I have to delete 16+ millions records from a 221+ million row table and it is going extremely slowly.
I appreciate if you share suggestions to make code below faster:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; DECLARE @BATCHSIZE INT, @ITERATION INT, @TOTALROWS INT, @MSG VARCHAR(500); SET DEADLOCK_PRIORITY LOW; SET @BATCHSIZE = 4500; SET @ITERATION = 0; SET @TOTALROWS = 0; BEGIN TRY BEGIN TRANSACTION; WHILE @BATCHSIZE > 0 BEGIN DELETE TOP (@BATCHSIZE) FROM MySourceTable OUTPUT DELETED.* INTO MyBackupTable WHERE NOT EXISTS ( SELECT NULL AS Empty FROM dbo.vendor AS v WHERE VendorId = v.Id ); SET @BATCHSIZE = @@ROWCOUNT; SET @ITERATION = @ITERATION + 1; SET @TOTALROWS = @TOTALROWS + @BATCHSIZE; SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR); PRINT @MSG; COMMIT TRANSACTION; CHECKPOINT; END; END TRY BEGIN CATCH IF @@ERROR <> 0 AND @@TRANCOUNT > 0 BEGIN PRINT 'There is an error occured. The database update failed.'; ROLLBACK TRANSACTION; END; END CATCH; GO
Execution Plan (limited for 2 iterations)
VendorId is PK and non-clustered, where clustered index is not in use by this script. There are 5 other non-unique, non-clustered indexes.
Task is "removing vendors which do not exist in another table" and back them up into another table. I have 3 tables,
vendors, SpecialVendors, SpecialVendorBackups. Trying to remove
SpecialVendors which do not exist in
Vendors table, and to have a backup of deleted records in case what I'm doing is wrong and I have to put them back in a week or two.