Deleting millions of rows from a MSSQL server table

Question

This SQL query took 38 minutes to delete just 10K of rows. How can I optimize it?

Index already exists for CREATEDATE from table2.

declare @tmpcount int
declare @counter int
SET @counter = 0
SET @tmpcount = 1
WHILE @counter <> @tmpcount
BEGIN
 SET ROWCOUNT 10000
 SET @counter = @counter + 1
 DELETE table1
 FROM table1
 JOIN table2
 ON table2.DOC_NUMBER = table1.DOC_NUMBER
 AND table2.DOC_YEAR = table1.DOC_YEAR
 WHERE YEAR(CREATEDDATE) BETWEEN 2007 and 2009
 IF @@ROWCOUNT = 0 
 BREAK
END

Answer 1

The performance problem is due to the non-sargable expression YEAR(CREATEDATE). Applying a function to a column in a WHERE clause prevents efficient use of the index on the column. Below is an alternative technique, which uses an inclusive start and exclusive end for the datetime range.

Note the use of SET ROWCOUNT is deprecated for DELETE, INSERT and UPDATE statements. Use TOP instead.

DELETE TOP(10000) table1
FROM dbo.table1
JOIN dbo.table2
 ON table2.DOC_NUMBER = table1.DOC_NUMBER
 AND table2.DOC_YEAR = table1.DOC_YEAR
WHERE 
 CREATEDDATE >= '20070101' 
 AND CREATEDATE < '20100101';

I'm not sure the purpose of @tmpcount in your script other than perhaps controlling the number of iterations for testing.

Answer 2

Besides great performance improvement due to Dan's suggestion, the code can also be improved. I have put comments to indicate changes:

-- contract declaration and assignment
declare @tmpcount int = 1
declare @counter int = 1
-- this can be declared before the cycle, as it is not changes within the cycle
SET ROWCOUNT 10000
-- @@ROWCOUNT will be 1 due to last assignment
WHILE (@@ROWCOUNT > 0 AND @counter <> @tmpcount)
BEGIN
 SET @counter = @counter + 1
 DELETE table1
 FROM table1
 JOIN table2
 ON table2.DOC_NUMBER = table1.DOC_NUMBER
 AND table2.DOC_YEAR = table1.DOC_YEAR
 WHERE YEAR(CREATEDDATE) BETWEEN 2007 and 2009
 -- break condition was moved in while
 -- it works as long as DELETE remains the last instruction in the while
END