CODEWITHSUNDEEP
sql-serverazureloopst-sqlsql-delete
Etienne
Etienne

Reputation: 7201

Batch Delete data - Azure SQL Server

For some reason when I try to do a batch delete on a Azure SQL Server it takes FOREVER. Below is the code I am trying to do. The select is fast. Both tables do have an index on the PlateNumber column.

WHILE(EXISTS(SELECT TOP 1 1 FROM TableName AS A WITH(NOLOCK)
             LEFT JOIN  #PlateNumbers2Keep AS B ON B.PlateNumber = A.PlateNumber
             WHERE      B.PlateNumber IS NULL) )
BEGIN
       BEGIN TRANSACTION    
       DELETE       TOP (1)             A 
       FROM         TableName AS A
       LEFT JOIN    #PlateNumbers2Keep   AS B ON B.PlateNumber = A.PlateNumber
       WHERE        B.PlateNumber IS NULL  
       COMMIT;
END

Upvotes: 1

Views: 561

Answers (1)

MarcinJ
MarcinJ

Reputation: 3639

You're not testing with 1 record, your loop will run for as long as there are any records not existing in that temp table, and will delete one row at a time, which is quite terrible.

Try this:

DECLARE @rowsAffected BIGINT = 1

WHILE @rowsAffected <> 0
BEGIN
    BEGIN TRANSACTION

    DELETE TOP (100000) A
      FROM TableName A
     -- anti-semi join is better for performance than regular left
     WHERE NOT EXISTS (SELECT 1 
                         FROM #PlateNumbers2Keep B 
                        WHERE B.PlateNumber = A.PlateNumber)

    SET @rowsAffected = @@ROWCOUNT

    COMMIT;
END;

Upvotes: 1

Related Questions