CTE vs Temp Table for BULK DELETE Operations in SQL Server

We have a table in that daily around we are getting 1 M records. In this table we are Parsing 7 days old data. Almost we are deleting 1 M records as well. One of my junior team members has written the code and testing it in the small dataset and it was working fine. When we had deployed in upper environment our query blocks everything… log file grows… tempdb cries…. QA team reach out to use saying everything got hanged. Could you please have a look.

When we saw the code, he has used CTE to delete the records. I asked some other junior team member the said that sir I will use “Delete from table where column condition”. I asked him to try this. He tried after some time he came back to me and saying this this is also not working. When we are running the delete command our other query which used this table getting slow and, in the application, getting failed due to time out issue.

Here we will see the solution how we will come over from this issue.

Ø  Instead of CTE we will use Temp table

Ø  Instead of Deleting the All records at a time we will delete these records into a batch

Here we will see when we use CTE and Temp table. Let’s break it with real examples, performance impact, and best practices.

Demo…

We have a table

--creating a table and inserting records create table Sales_order( SO_id bigint identity(1,1), So_name varchar(50), Qntity decimal(18,10), create_user varchar(50) default current_user, create_dt datetime default getdate())

First we insert 1 M record having 8 day old date as created date form this demo

--Inserting record 1 M DECLARE @c bigint, @create_dt datetime =getdate()-8 set @c=0 while @c<=1000000 BEGIN insert into Sales_order(So_name,Qntity,create_dt)  select 'So_name'+cast(@c as varchar(10)) as So_name, @c* Rand(),@create_dt set @c=@c+1 END

 Now we are inserting 2M more data.

--Inserting record 1 M DECLARE @c bigint, @create_dt datetime =getdate()-2 set @c=0 while @c<=1000000 BEGIN insert into Sales_order(So_name,Qntity,create_dt)  select 'So_name'+cast(@c as varchar(10)) as So_name, @c* Rand(),@create_dt set @c=@c+1 END  --Inserting record 1 M DECLARE @c bigint, @create_dt datetime =getdate() set @c=0 while @c<=1000000 BEGIN insert into Sales_order(So_name,Qntity,create_dt)  select 'So_name'+cast(@c as varchar(10)) as So_name, @c* Rand(),@create_dt set @c=@c+1 END

 Now we have around 3M records in this table.

“First, let’s see how DELETE works with a CTE. A CTE is just a logical result set – it does not store data physically.”

DELETE Using CTE

WITH cte_Sales_order AS (     SELECT *     FROM Sales_order     WHERE create_dt < (GETDATE()-7) ) select count(*) FROM cte_Sales_order;

Seeing the result

Now we are deleting these records

Before deleting these records.

“What’s happening here?

Ø  CTE is evaluated

Ø  Rows are identified

Ø  DELETE executes immediately

BUT remember…”

CTE is re-evaluated every time

Ø  No indexing

Ø  No statistics

Ø  Limited control over execution

Ø  Poor choice for very large deletes

If the query plan changes, performance changes!”

WITH cte_Sales_order AS (     SELECT *     FROM Sales_order     WHERE create_dt < (GETDATE()-7) ) delete FROM cte_Sales_order;

 Record deleted.

For the next demo we are inserting 1 M records

--Inserting record 1 M DECLARE @c bigint, @create_dt datetime =getdate()-8 set @c=0 while @c<=1000000 BEGIN insert into Sales_order(So_name,Qntity,create_dt)  select 'So_name'+cast(@c as varchar(10)) as So_name, @c* Rand(),@create_dt set @c=@c+1 END

 Using simple delete

DELETE     FROM Sales_order     WHERE create_dt < (GETDATE()-7)

This also delete the records.

Problem of this as below

Ø  Long blocking

Ø  Transaction log explosion

Ø  Possible lock escalation (table lock)

Ø  Rollback risk if it fails

Ø  Can bring production to a halt 

Now the Best we to use Batch Delete using TOP or using Temp table and the delete it in a batch

For this demo inserting again 1 M records.

Read here : Deleting Records from Large tables (Batch Delete)

https://bageshkumarbagi-msbi.blogspot.com/2017/07/deleting-records-from-large-tables.html

here we are using temp table to delete the records.

First of all we will create a temp table and store the ID which records we are going to delete. See below.

--creating temp table CREATE TABLE #DelRecords (id bigint primary key not null)  --inserting id which are are going to delete insert into #DelRecords(id) SELECT SO_id  FROM Sales_order     WHERE create_dt < (GETDATE()-7);  -- now we are deleting the records DECLARE @batchSize INT ; set @batchSize = 10000;  WHILE 1 = 1 BEGIN     DELETE TOP (@batchSize) so     FROM Sales_order so               join #DelRecords dr               on so.SO_id=dr.id       IF @@ROWCOUNT = 0               BREAK;      -- Optional pause to reduce pressure     WAITFOR DELAY '00:00:01'; END

 Note: Batch size depends on your table load.

Benefits:

Ø  Small transactions

Ø  Minimal blocking

Ø  Log growth under control

Ø  Safe for production

While we are deleting large volume of data, we need to keep in mind

What NOT to do

Ø  Single large DELETE

Ø   Delete without WHERE clause

Ø   Delete without index

Ø  Run during peak business hours

“To delete millions of records safely in SQL Server, I always use batch deletes using TOP or key ranges to avoid blocking, log growth, and performance issues.”