CODEWITHSUNDEEP
sql-server
pkr
pkr

Reputation: 1761

Why is deleting records in batches better than deleting in bulk?

What are the reasons something like this

DECLARE @i int = 0
WHILE @i < 100 BEGIN
  BEGIN TRAN
    SET @i = @i + 1

    DELETE TOP (20) st
    FROM SomeTable st
    JOIN #ToDelete ON #ToDelete.Id = st.Id
  COMMIT
END

should be used instead of something like this?

DELETE st
FROM SomeTable st
JOIN #ToDelete ON #ToDelete.Id = st.Id

Upvotes: 4

Views: 5178

Answers (1)

HLGEM
HLGEM

Reputation: 96600

When you delete a large number of records several things might happen.

First you may have cascade delete turned on in which case deleting 10000 records from the main table could result in deleting millions of records when all the child tables are taken into account. Even without cascaded delte, if there are a lot of child tables, they all have to be checked to make sure there are no records before the delete can take place.

Further a delete like this might escalate the locking on the table so that no one else can use the table while the delete is taking place. This is even more of a problem when using cascade delete as the process will take longer and many more tables might get locked.

Another thing that might cause a large delete to be especially slow is if there are triggers on the table, especially triggers that have some sort of looping in them.

In general this technique is used when the delete for a large number of records would take too long and cause blocking in the meantime. You go by batches because that is generally faster than one row at a time.

This is not a techinque you need to use every time you delete a batch of records, if the delete would take a few seconds in a table with no child records and/or no triggers, there is no need. If the table is not one that others would normally be accessing simultaneously with the delete (such as a job that does the deletes during off hours), you would not need to use the technique.

Upvotes: 13

Related Questions