How to delete large records in batches
Tags: sql, mssqlserver, programming, tipsandtricks
Published: 2023-03-29 23:03:16

When performing long-running modifications, I'm sure many of you enjoy using batches to increase concurrency. But I want to talk about a pitfall to be aware of. If you're not careful, the method you use to implement batching can actually worsen concurrency.

<p>&nbsp;</p>
## Why Use Batches?

Even without an explicit transaction, all SQL statements are atomic – changes are all or nothing. So when you have long-running modifications to make, locks on data can be held for the duration of your query and that can be too long. Especially if your changes are intended for live databases. 

But you can make your modifications in several smaller chunks or batches. The hope is that each individual batch executes quickly and holds locks on resources for a short period of time. But care is needed. 


### Example

I'm going to give an example to show what I mean. 

The example uses the **FactOnlineSales** table in the **ContosoRetailDW** database ([available as a download here](http://www.microsoft.com/en-ca/download/details.aspx?id=18279)). 

The **FactOnlineSales** table has

*   one clustered index on **OnlineSalesKey** and no other indexes,
*   12 million rows,
*   and 46 thousand database pages

**Metrics to Use** 
In this example, I want to know how long each query takes because this should let me know roughly how long locks are held. But instead of duration, I'm going to measure logical reads. It's a little more consistent and in the examples below, it's nicely correlated with duration.


<p>&nbsp;</p>
## The Straight Query

Suppose we want to remove sales data from **FactOnlineSales** for the "Worcester Company" whose **CustomerKey = 19036**. That's a simple delete statement:

```sql
DELETE FactOnlineSales WHERE CustomerKey = 19036;
```

This delete statement runs an unacceptably long time. It scans the clustered index and performs 46,650 logical reads and I'm worried about concurrency issues.

<p>&nbsp;</p>
### Naive Batching

So I try to delete 1,000 rows at a time. This implementation seems reasonable on the surface:
```sql
DECLARE	
  @RC INT = 1;
 
WHILE (@RC > 0)
BEGIN
 
  DELETE TOP (1000) FactOnlineSales
  WHERE CustomerKey = 19036;
 
  SET @RC = @@ROWCOUNT
 
END
```

Unfortunately, this method does poorly. 
It scans the clustered index in order to find 1,000 rows to delete. The first few batches complete quickly, but later batches gradually get slower as it takes longer and longer to scan the index to find rows to delete. 

By the time the script gets to the last batch, SQL Server has to delete rows near the very end of the clustered index and to find them, SQL Server has to scan the entire table. In fact, this last batch performs 46,521 logical reads (just 100 fewer reads than the straight delete). 

And the entire script performed 1,486,285 logical reads in total. If concurrency is what I’m after, this script is actually worse than the simple DELETE statement.


<p>&nbsp;</p>
### Careful Batching

But I know something about the indexes on this table. I can make use of this knowledge by keeping track of my progress through the clustered index so that I can continue where I left off:

```sql
DECLARE
  @LargestKeyProcessed INT = -1,
  @NextBatchMax INT,
  @RC INT = 1;
 
WHILE (@RC > 0)
BEGIN
 
  SELECT TOP (1000) @NextBatchMax = OnlineSalesKey
  FROM FactOnlineSales
  WHERE OnlineSalesKey > @LargestKeyProcessed
    AND CustomerKey = 19036
  ORDER BY OnlineSalesKey ASC;
 
  DELETE FactOnlineSales
  WHERE CustomerKey = 19036
    AND OnlineSalesKey > @LargestKeyProcessed
    AND OnlineSalesKey <= @NextBatchMax;
 
  SET @RC = @@ROWCOUNT;
  SET @LargestKeyProcessed = @NextBatchMax;
 
END
```

The delete statements in this script performed 46,796 logical reads in total but no individual delete statement performed more than 6,363. Graphically that looks like: 

![Logical Reads Per Delete](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0ux9ijhjuy3s0anq35xi.png "Logical Reads Per Delete")_Logical Reads Per Delete_

The careful batching method runs in roughly the same time as the straight delete statement but ensures that locks are not held for long. 

The naive batching method runs with an order of n² complexity (compared to the expected complexity of n) and can hold locks just as long as the straight delete statement. This underlines the importance of testing for performance. 

Original Source: [http://michaeljswart.com/2014/09/take-care-when-scripting-batches/](http://michaeljswart.com/2014/09/take-care-when-scripting-batches/)