Batch Delete Performance SQL Server

Deleting old records from a table with > 3 000 00 rows. What’s the best way to do this?

It seems the fastest way to do this is simply to:

delete from [table] where creation_timestamp < dateadd (mm, -6, getdate())

(deleting anything older than 6 months)

It took 3 hours (10916 seconds to be exact) to delete 1.6 million (1,619,433) records this way. (148.35 / second).

We needed to do a second batch the next day, but wanted to split it into batches to try to get better performance.

Running:


delete from [table] where pk_id in(
select pk_id from (
SELECT ROW_NUMBER() OVER (ORDER BY creation_timestamp desc) AS RowNumber, pk_id
FROM [table]
where creation_timestamp < '2009-12-16 13:52:08.673') _objectsToDelete
WHERE RowNumber between 1 and 100000)

takes 12 minutes. (732 seconds) (136.61 / second).

Strangely, using the TOP command with a subquery takes the longest:


delete from [table] where pk_id in (select TOP 100000 pk_id from [table] where creation_timestamp < '2009-12-16 13:52:08.673')

15 minutes (904 seconds) (110.61 / second)

Have a better way? Let me know in the comments!


3 Responses to “Batch Delete Performance SQL Server”

  • Colorado chat line Says:

    yahey ! I just would like to hold a vast thumbs up for the nice facts you have here on this unique post. I will probably be return to your web page for more soon . Great post, i certainly really like this web site, carry on it. If you are free, nice, and wants to have a good companion . call now @ 712-432-2207 or check the website. Lets have a blast!

  • laptop sh Says:

    laptop sh…

    [...]Batch Delete Performance SQL Server | Jeremy Massel's Blog[...]…

  • hire bouncy castles Says:

    I think this is among the most vital info for me. And i’m glad reading your article.

    But should remark on few general things, The web site style is great, the articles is really great :
    D. Good job, cheers

Leave a Reply