sql bulk delete is much slower?

Question

I have a DB that is about 3.5GB. 2 tables use most spaces. each table has about 2.8M rows. now I need to delete about 1.9M rows from each table. using the batch delete

• DELETE TOP (n) syntax, then loop until now more needs to be deleted

it seems we controlled the log size problem. however, the performance downgraded a lot. for example, if I delete the data from the first table A in one batch (delete from ...), it takes about 9 minutes. Now, if I delete in a batch of 2000, after the whole night, it does not finish yet. If I get the # of rows that will be deleted, then batch delete 10% of it in each loop (so, about 200,000 for each batch), it takes about 40 minutes.

Is this normal? one thing I want to point out is: there is no primary key in these 2 tables, although they have non-cluster index. it was designed by previous developer who never thought we would have so much data to process. Now, we have to live with this, change database structure is not an option. Could it because of this that makes the delete slow?

Any suggest of what can cause the slow?

Answer 1

Considering the symptomp of gradual degradation after each batch I would guess is caused by DELETE leaving behind ghosted records, see SQL Server “empty table” is slow after deleting all (12 million) records? Subsequent deletes have to skip over all those ghosts, until they are reclaimed. For a one time operation it would make more sense to copy the data to be kept into a new table, then use partition switch tricks to switch it in instead of the old data, then truncate the old data. If you can't do ALTER TABLE .. SWITCH you can use the old sp_rename trick, but bear in mind that sp_rename does not preserve security permissions etc on the table (it is a different table with the same name).

If this deletion is to be done regularly, then you may look into a sliding window scenario.

Answer 2

each table has about 2.8M rows. now I need to delete about 1.9M rows from each table

1. Create a Stored Proc.
2. Insert .9M rows in a Local Temporary Table.
3. Truncate User Defined Table.
4. Insert all Local Temporary Table records in the User Defined Table.

Note - In case you have any Delete Trigger, Please attach the SQL Profiler for any Performance hinderance in you query.

Do the same process for another table. Hope this will help you.

Sample Stored Proc

Begin  Try
    SET NOCOUNT ON
    SET XACT_ABORT ON
    Begin Tran
        --Your queries
    Commit Tran
End Try

Begin Catch
    Rollback Tran
End Catch