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!


9 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

  • http://gph-entertainments.co.uk Says:

    Hey I know this is off topic but I was wondering if you knew
    of any widgets I could add to my blog that automatically tweet my newest twitter updates.
    I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something
    like this. Please let me know if you run into anything.

    I truly enjoy reading your blog and I look forward to your new updates.

  • castle Says:

    I really like what you guys are up too. Such clever work and reporting!
    Keep up the wonderful works guys I’ve added you guys to my blogroll.

  • bouncy castle Says:

    Simply want to say your article is as astonishing.
    The clearness in your post is simply cool and
    i can assume you are an expert on this subject. Fine with your permission allow me to grab your RSS
    feed to keep up to date with forthcoming post. Thanks
    a million and please keep up the enjoyable work.

  • bouncy Says:

    No matter if some one searches for his vital thing, thus he/she desires to be available that in detail,
    thus that thing is maintained over here.

  • car scratch repair Newton Aycliffe Says:

    I don’t even know how I ended up here, but I thought this post was good.
    I don’t know who you are but certainly you’re going to a famous blogger if you are not already ;
    ) Cheers!

  • inflatablehire.edublogs.org Says:

    I feel this is among the so much important information for me.
    And i am glad studying your article. However should commentary on some basic things, The website taste
    is ideal, the articles is really nice : D. Excellent process,
    cheers

Leave a Reply