5
\$\begingroup\$

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
\$\endgroup\$
10
\$\begingroup\$

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.

\$\endgroup\$
1
\$\begingroup\$

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
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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