Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are implementing a new feature in our system that will cause about a million records (each record is tiny, basically a GUID, a date, and four smallint fields) to be purged from a table every night. Basically it's a caching table, and once the data is 7 days old we do:

DELETE FROM scheduleCache WHERE schDateCreated < '2013-08-26

This will run every night at 1am, and will purge about a million records every time it runs.

Is there anything I should be noting or doing for a table like this? Any properties I should put on the table, or any routines I should run regularly to "clean up"? I've never dealt with a table like this before.

The table has a single clustered index (GUID + one of the smallint fields), and we have a weekly index rebuild that runs Sunday mornings.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

The problem of deleting large portions of a table is far from a trivial problem. The best approach, by far, is partitioning. A daily partition scheme with a sliding window is really a magic bullet for this problem, see How to Implement an Automatic Sliding Window in a Partitioned Table.

If you cannot afford partitioning (eg. non-enterprise license on site) then I would recommend clustering by schDateCreated. If you need primary key on the GUID+smallint then move it to non-clustered. Delete in batches (eg. TOP 10000), in a loop, to reduce pressure on the log. Consider updating stats after the operation.

share|improve this answer
    
Unfortunately we can't afford partitioning, nor can we request our customer-site installations to pay a that size of upgrade just to keep this function working smoothly. I will start it with the clustered index like you suggest and see how we go from there. –  Mark Henderson Aug 27 '13 at 3:01
    
Just as followup, I made a clustered index on the date field, did a DELETE TOP 10000 WHILE COUNT() > 0 and it's been working great for several months. –  Mark Henderson Jun 10 at 8:13

One solution would be to store the data in one table per day, dropping the tables as they age out of the cache. The tables would have names like cache_(julian date).

Another solution would be to have a set of tables cache_0 to cache_n, truncating each table before you use it. You would use table cache_((julian date) modulo (cycle period)) for each day.

You would need to handle year end carefully, as you'll either go from 365 or 366 to 0 for either solution.

share|improve this answer
    
Indeed, these two things should work, however I'd like to avoid having multiple tables for a) execution plan caching and b) I know some customers do not give the application create table permission –  Mark Henderson Aug 26 '13 at 4:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.