9

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)

enter image description here

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.

migrated from stackoverflow.com Oct 29 '17 at 12:26

This question came from our site for professional and enthusiast programmers.

  • I would work on optimizing that query and try a left join where null – paparazzo Oct 29 '17 at 12:56
8

The execution plan shows that it is reading rows from a nonclustered index in some order then performing seeks for each outer row read to evaluate the NOT EXISTS

enter image description here

You are deleting 7.2% of the table. 16,000,000 rows in 3,556 batches of 4,500

Assuming that the rows that qualify are evently distributed throughout the index then this means it will delete approx 1 row every 13.8 rows.

So iteration 1 will read 62,156 rows and perform that many index seeks before it finds 4,500 to delete.

iteration 2 will read 57,656 (62,156 - 4,500) rows that definitely won't qualify ignoring any concurrent updates (as they have already been processed) and then another 62,156 rows to get 4,500 to delete.

iteration 3 will read (2 * 57,656) + 62,156 rows and so on until finally iteration 3,556 will read (3,555 * 57,656) + 62,156 rows and perform that many seeks.

So the number of index seeks performed across all batches is SUM(1, 2, ..., 3554, 3555) * 57,656 + (3556 * 62156)

Which is ((3555 * 3556 / 2) * 57656) + (3556 * 62156) - or 364,652,494,976

I would suggest that you materialise the rows to delete into a temp table first

INSERT INTO #MyTempTable
SELECT MySourceTable.PK,
       1 + ( ROW_NUMBER() OVER (ORDER BY MySourceTable.PK) / 4500 ) AS BatchNumber
FROM   MySourceTable
WHERE  NOT EXISTS (SELECT *
                   FROM   dbo.vendor AS v
                   WHERE  VendorId = v.Id) 

And change the DELETE to delete WHERE PK IN (SELECT PK FROM #MyTempTable WHERE BatchNumber = @BatchNumber) You may still need to include a NOT EXISTS in the DELETE query itself to cater for updates since the temp table was populated but this should be much more efficient as it will only need to perform 4,500 seeks per batch.

  • When you say "materialise the rows to delete into a temp table first" are you suggesting to place all those records with their all columns into temp table? or only the PK column? (I believe you are suggesting me to move those to temp table completely but wanted to double check) – cilerler Oct 30 '17 at 17:56
  • @cilerler - Just the key column(s) – Martin Smith Oct 30 '17 at 18:00
  • can you quickly review this if I get what you said correctly or not, please? – cilerler Oct 30 '17 at 19:23
  • @cilerler -DELETE TOP (@BATCHSIZE) FROM MySourceTable should just be DELETE FROM MySourceTable also index the temp table CREATE TABLE #MyTempTable ( Id BIGINT, BatchNumber BIGINT, PRIMARY KEY(BatchNumber, Id) ); and is VendorId definitely the PK on its own? You have > 221 million different vendors? – Martin Smith Oct 30 '17 at 19:39
  • Thanks Martin, Will test it after 6pm. And your answer is, It is definitely the only PK exist in that table – cilerler Oct 30 '17 at 19:59
4

The execution plan suggests that each successive loop will do more work than the previous loop. Assuming that the rows to delete are evenly distributed throughout the table the first loop will need to scan about 4500*221000000/16000000 = 62156 rows to find 4500 rows to delete. It will also do the same number of clustered index seeks against the vendor table. However, the second loop will need to read past the same 62156 - 4500 = 57656 rows that you didn't delete the first time. We might expect the second loop to scan 120000 rows from MySourceTable and to do 120000 seeks against the vendor table. The amount of work needed per loop increases at a linear rate. As an approximation we can say that the average loop will need to read 102516868 rows from from MySourceTable and to do 102516868 seeks against the vendor table. To delete 16 million rows with a batch size of 4500 your code needs to do 16000000/4500 = 3556 loops, so the total amount of work for your code to complete is around 364.5 billion rows read from MySourceTable and 364.5 billion index seeks.

A smaller issue is that you use a local variable @BATCHSIZE in a TOP expression without a RECOMPILE or some other hint. The query optimizer will not know the value of that local variable when creating a plan. It will assume that it equals 100. In reality you're deleting 4500 rows instead of 100, and you could possibly end up with a less efficient plan due to that discrepancy. The low cardinality estimate when inserting into a table can cause a performance hit as well. SQL Server might pick a different internal API to do inserts if it thinks that it needs to insert 100 rows as opposed to 4500 rows.

One alternative is to simply insert the primary keys/clustered keys of the rows that you want to delete into a temporary table. Depending on the size of your key columns this could easily fit into tempdb. You can get minimal logging in that case which means that the transaction log won't blow up. You can also get minimal logging against any database with a recovery model of SIMPLE. See the link for more information on the requirements.

If that's not an option then you should change your code so that you can take advantage of the clustered index on MySourceTable. The key thing is to write your code so that you do approximately the same amount of work per loop. You can do that by taking advantage of the index instead of just scanning the table from the beginning each time. I wrote a blog post that goes over some different methods of looping. The examples in that post do inserts into a table instead of deletes but you should be able to adapt the code.

In the example code below I assume that the primary key and clustered key of your MySourceTable. I wrote this code pretty quickly and am not able to test it:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE @BATCHSIZE INT,
        @ITERATION INT,
        @TOTALROWS INT,
        @MSG VARCHAR(500)
        @STARTID BIGINT,
        @NEXTID BIGINT;
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;

SELECT @STARTID = ID
FROM MySourceTable
ORDER BY ID
OFFSET 0 ROWS
FETCH FIRST 1 ROW ONLY;

SELECT @NEXTID = ID
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
OFFSET (60000) ROWS
FETCH FIRST 1 ROW ONLY;

BEGIN TRY
    BEGIN TRANSACTION;

    WHILE @STARTID IS NOT NULL
        BEGIN
            WITH MySourceTable_DELCTE AS (
                SELECT TOP (60000) *
                FROM MySourceTable
                WHERE ID >= @STARTID
                ORDER BY ID
            )           
            DELETE FROM MySourceTable_DELCTE
            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;

            SET @STARTID = @NEXTID;
            SET @NEXTID = NULL;

            SELECT @NEXTID = ID
            FROM MySourceTable
            WHERE ID >= @STARTID
            ORDER BY ID
            OFFSET (60000) ROWS
            FETCH FIRST 1 ROW ONLY;

        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

The key part is here:

WITH MySourceTable_DELCTE AS (
    SELECT TOP (60000) *
    FROM MySourceTable
    WHERE ID >= @STARTID
    ORDER BY ID
)   

Each loop will only read 60000 rows from MySourceTable. That should result in an average delete size of 4500 rows per transaction and a maximum delete size of 60000 rows per transaction. If you want to be more conservative with a smaller batch size that's fine too. The @STARTID variable advances after each loop so you can avoid reading the same row more than once from the source table.

  • Thank you for detailed info. I set that 4500 limit to not lock table. If I'm not mistaken SQL has a hard limit that locks entire table if the delete count goes above 5000. And since this will be a long process I can not effort to lock that table for a long period of time. If I set that 60000 to 4500, do you think I will get the same performance? – cilerler Oct 30 '17 at 13:54
  • @cilerler If you're worried about lock escalation you can disable it at the table level. There's nothing wrong with using a batch size of 4500. The key is that each loop will do roughly the same amount of work. – Joe Obbish Oct 30 '17 at 15:23
  • I have to accept other answer due to the speed differences. I tested your solution and @Martin-Smith 's solution and his version is getting more data ~2% in for a 10 minutes test. Your solutions are much better than mine and I really do appreciate for your time... – – cilerler Oct 31 '17 at 2:33
2

Two thoughts spring to mind:

The delay is probably due to indexing with that volume of data. Try dropping the indexes, deleting, and re-building the indexes.

Or..

It may be faster to copy the rows you want to keep into a temporary table, drop the table with the 16 million rows, and rename the temporary table (or copy to new instance of the source table).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.