Delete a load of rows from a slow SQL table
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.