Housekeeping blobs in a DB.

Hi,

VS2005

SQl workgroup 2005

I have a DB where I store alot of Blobs, the blobs are tagged up with alot of id. My concern is deleting them automatically on a regular basis, too keep the DB size down. I will be deleting them based on based on age(date/time) and what category the blob is classed as.

- My concern is how to delete blobs sort of equally dispersed among the categories so no category ends up empty although it may have old pictures in it.

Any tips, links or code is much welcome.

(my app will be acessing the DB though ADO .NET 3.0 c++/cli)

Cheers,

Andreas

WHat about using DELETE TOP(50 PERCENT) From SomeTable Where DateisOld ?

That would not delete every record which is old and will leave some of them still on the disk/Database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
--|||

Jens,

Hi, that was a good suggestion, I wasn′t aware of the TOP command. It sort of is useful maybe if its used with

a > 1000 to ensure there is atleast 1000 blobs in each category?

now I need to solve such a query

Any help is appriciated!

Cheers,

/Andreas

|||A quick sample for your asked query would be:

DELETE
Production.ProductProductPhoto
FROM Production.ProductPhoto P
INNER JOIN
(
SELECT ProductPhotoId,ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter
From Production.ProductPhoto
) SubQuery
ON P.ProductPhotoId = SubQuery.ProductPhotoId
WHERE SubQuery.Counter > 1000

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Thanks for your swift reply, will try what youre suggesting.

so far i′ve come up with a CTE solution that′s close but not totally there

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY Category

ORDER BY Age DESC) AS rownum, ID FROM Imageblob)

DELETE FROM tempdata WHERE rownum < 1000;

hmm can you use DELETE TOP within a CTE? i didn′t get it to work.

Cheers,

/Andreas

|||

Whats your error message ?

THis works for me:

WITH tempdata AS (SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter, ProductPhotoId FROM Production.ProductPhoto)

DELETE FROM tempdata WHERE counter > 1000;

Remember that you want to do a >1000 instead of a <1000

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Jens,

Hi,

ah, the <1000 was a typo.

Error message? I don′t really get the hang of where to put the TOP cmd.

Cheers,

/Andreas.

|||YOu don′t need the TOP if you use the TVF.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

Ah,

I was toying with the idea to use TOP and Percentage rather than a fixed number to delete.

Cheers,

/Andreas

|||

Ok, is the problem solved then ? You can either delete a percentage or leave a threshold of rows in the table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Jens,

I think so, ofc there are some more things I′ll try to add if I fail i′ll be back.

But thanks for your time and advice Jens!

Cheers

/Andreas

|||

Jens,

Ah, the phrase equal deletion is perhaps not accurate, I want to delete in the categories with most

rows. When i′ve created the CTE how can I from that deduce which Category in tempdata holds most

rows? Where do I fit a Count/Count_big?

And then delete from the category which has most rows and or delete more from the first, less from the second... etc

Code Snippet

count(age)

from Imageblob

Group by Category;

gives me rows per category, but with the CTE it gives me other number probably correct

based on the the tempdata is.

Cheers,

/Andreas

|||

The statement mentioned in your last post will elimenate the NULL values, therefore you probably have another count than the expected one of the CTE. For only deleting in the "biggest" category, you will have to query

Code Snippet

WITH tempdata AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ProductPhotoId Order by ModifiedDate DESC) As Counter,
ProductPhotoId
FROM Production.ProductPhoto
)
DELETE tempdata
FROM tempdata F
INNER JOIN
(
SELECT TOP (1) COUNT(*),category FROM tempdata
GROUP BY category
ORDER BY COUNT(*)
) SubQueryForCount
ON SubQueryForCount.category = F.tempdata
WHERE counter > 1000;

Jens K. Suessmeyer

http://www.sqlserver2005.de