Delete a load of rows from a slow SQL table

15 November 2023

I was trying to delete ~180,000 rows from a table and it was taking 10+ minutes so I gave up. I knew that deleting around 8000 records worked in 10-12 seconds.

Here is a query that made it work in less time:

SET NOCOUNT ON;
DECLARE @r INT = 1;
WHILE @r > 0
BEGIN
  BEGIN TRANSACTION;

  -- Do your deletes, with a 'top' constraint to chunk it:
	delete top (8000) from MyLogs where [Timestamp] < '2023-01-01T00:00:00'

  SET @r = @@ROWCOUNT;
  COMMIT TRANSACTION;
END

I don’t claim to understand this! I’m not a SQL 🐿! I’m not a database 🛢!

I borrowed and butchered this from https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes.

🧣 I hope you’re having a nice month.