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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a delete statement that get run repetitive until all the data are removed.

Delete TOP (1000) From Log Where CreationDate < '2011-Dec-31' AND pKey NOT IN (...)

I am considering of truncating the where clause so that the pseudo code is like:

while(@parameter < '2011-Dec-31' AND rowCount > 0)
 Delete TOP (1000) From Log Where CreationDate < @parameter AND pKey NOT IN (...)
 SET @parameter = DATEADD(day, 1, @parameter)

Would this help speed up the delete because it doesn't have to do cache as much data, or it doesn't matter that much since I have an index on CreationDate?


share|improve this question
It'll speed things up to do a bigger set. With smaller ranges (1k rows is pretty small) you are paying a lot of overhead to keep applying your filter every time this query runs. Have you tried different batch sizes? – JNK Jan 30 '12 at 19:06
Actually I suppose it could help by avoiding having to rescan the rows that meet previously processed dates but weren't deleted as they didn't match the NOT IN predicate. Do you have a guaranteed maximum of 1,000 rows per day? Execution Plan? – Martin Smith Jan 30 '12 at 20:24
Since you're deleting all data out of that table, is there any reason you're not using a TRUNCATE statement instead of a DELETE statement? Is something foreign keyed onto that table so that you can't use truncate? – Brandon Jan 30 '12 at 21:16
@Brandon: he is obviously not deleting all data. He has a complex condition: CreationDate < '2011-Dec-31' AND pKey NOT IN (...) – ypercubeᵀᴹ Jan 30 '12 at 22:09
Larger sizes will always be faster. BUT! you need to limit the size so that your log file will not need to expand, and also see how long the deletes take. I personally try to keep it the batch sizes to around 30 seconds, and 50% log file max, whichever is smaller. – Simon Hughes Jan 31 '12 at 13:19
up vote 0 down vote accepted

You trade off

  • speed
  • smaller transaction log
  • concurrency

So running this in batches with a good index satisfies the last 2, but is slower. However, you don't need absolute speed if it is less intrusive to other operations

Otherwise, I'm not sure what you want...

share|improve this answer
In my case, and the delete seems to faster after I reduce the data selection range. The question boil down would the where clause matter if I always deleting TOP 1000. The answer is that it does. The reason may be because there are simultaneous insert during the delete, and the insert must be blocking the delete due to page lock. By having a small date range of data to be delete, it doesn't lock the entire page, allowing both delete and insert operates better. – dsum Feb 21 '12 at 0:03

Your Answer


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.