Optimization: Delete with Top and minimize the where clause date range I have a delete statement that get run repetitive until all the data are removed.

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


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

while(@parameter &lt; '2011-Dec-31' AND rowCount &gt; 0)
{
 Delete TOP (1000) From Log Where CreationDate &lt; @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?

Thanks
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...