Incremental delete - How it is beneficial ?

Question

If I have a table with huge amount of data...and If I do incremental delete instead "one time delte"..what's the benefit ?

Onetime delete

 DELETE table_1
     WHERE BID = @BID
    AND CN = @CN        
     AND PD = @PD;  

Incremental Delete

While (1=1)
Begin
 DELETE TOP (100000) FROM table_1
     WHERE BID = @BID
    AND CN = @CN        
     AND PD = @PD;  

If @@rowcount = 0   -- No row affected.
  BREAK
 ELSE
  Continue
End

I got help from http://stackoverflow.com/questions/3883420/deleting-a-sql-server-table-takes-much-time

Answer 1

Depends on the configuration, I have seen large deletes blow up the transaction log and cause a failure in not enough disk space.

You could also avoid locking escalation by using a smaller batch.

Answer 2

Alternatively, you can export the data you want to keep, truncate the table, and load your data back. This may be faster. Even if you want to kepp 50% of your data, it can still be faster - truncate is only minimally logged. Run your own benchmarks

Answer 3

The difference is the size of the rollback information.

SQL Server is transactional, before the delete is committed it should be possible to rollback the transaction.

Take the following example:

• Free space on harddisk 10 GB
• Information to be deleted 20 GB

When you start the delete the transaction log will grow until the disk is full then it will crash.

Even with enough disk space there are other problems, database locking or performance hits. This can be a serious problem if deleting data from a live system.

Answer 4

I am a rookie in case of DB tuning ....:(

Me too...
but I do not need to understand technical intricacies (like lock escalations, etc.) to feel/guess the benefits of eating/biting watermelon by by small pieces against putting it by a whole one piece into my mouth