Deletes on large table

I had to help a collegue out today. He had to delete about 12 million rows on a 13 billion row table. And his statement (delete from [table name] where spec_id = 1) never got anywhere.

First thing i tried was to encapsulate the statement in a transaction. This transaction was put within a while loop. The script looked like this:

SET NOCOUNT ON

DECLARE @R INT;

SET @R = 1;

WHILE @R > 0
BEGIN

BEGIN TRANSACTION
DELETE FROM [table name]
WHERE SPEC_ID = 1
OPTION (MAXDOP 1)

SET @R = @@ROWCOUNT;

COMMIT TRANSACTION

END

Now this looked like a smart plan, but still no progress. The maxdop hint was introduced because the delete statement was locking itself.

Now, what i realized some time later was that the table was partitioned. This meant that the query without the partition key was running around like a wild dog, but effectively doing nothing at all. So, i adjusted the query with the partitioning key:

 
SET NOCOUNT ON

DECLARE @R INT;
DECLARE @DATEID INT;

SET @R = 1;
SET @DATEID = 20170101

WHILE @DATEID < 20190117
BEGIN

BEGIN TRANSACTION
DELETE FROM [table name]
WHERE SPEC_ID = 1
OPTION (MAXDOP 1)

SET @R = @@ROWCOUNT;
SET @DATEID = @DATEID + 1

COMMIT TRANSACTION

END

Now, this helped! The query finished in around 15 minutes clearing out the 10 miljoen records. Still very slow but at least it finished.

Now, i still don’t get why deletes are almost always very slow on SQL Server. Any thoughts on that? Let me know!

Thanks for reading 🙂

Advertenties

Geef een reactie

Vul je gegevens in of klik op een icoon om in te loggen.

WordPress.com logo

Je reageert onder je WordPress.com account. Log uit /  Bijwerken )

Google photo

Je reageert onder je Google account. Log uit /  Bijwerken )

Twitter-afbeelding

Je reageert onder je Twitter account. Log uit /  Bijwerken )

Facebook foto

Je reageert onder je Facebook account. Log uit /  Bijwerken )

Verbinden met %s