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!
Jeremy Massel 