CTE vs. temp table for batch deletes

2 votes
2 answers
1624 views
sql-server delete cte temporary-tables batch-processing
                          Two-part question here.  

We have a large table (between 1-2 million rows) with very frequent DML operations on it.  During low volume periods, we have an agent job to remove older rows to keep the tables size in check.  it uses the CTE below, which is causing lots of blocking when it runs:

    ;with agent_cte (queue_id) as 
    (
       select top (3000) queue_id
       from Table_A with (updlock, readpast)
       where state in ('success','error','reject','failed','deleted')
       and modify_timestamp <= DATEADD(dd,- 5,getdate())
       order by modify_timestamp asc
    )
       delete from agent_cte 
    ;
CTE Query Plan 

I re-wrote this to use a temp table to speed the query and reduce blocking, but I'm finding a large performance difference between using IN vs. EXISTS to determine which rows to delete.

IN version:

    -- Create temp Table
	create table #Table_AToDelete
		(Queue_id uniqueidentifier,
		PRIMARY KEY(Queue_id)
		);

    -- grab top 3k queue_id's older than 5 days, insert into temp table
    	Insert into #Table_AToDelete
    		select top (3000) queue_id
    		from Table_A with (nolock)
    		where state in ('success','error','reject','failed','deleted')
    		and modify_timestamp <= DATEADD(dd,- 5,getdate())
	
    -- delete the rows from table_A based on rows in temp table
    	delete 
    		from Table_A
    		where queue_id in (select queue_id from #Table_AToDelete)

Temp Table Plan 

This version runs in 40-50 seconds, however when I replace the last line from the delete statement with the below:

    where exists(select queue_id from #Table_AToDelete)

It is still running after 2 minutes, so I cancelled it.

So, the questions:

1. I've seen temp tables used to help with blocking and performance before, but don't fully understand WHY it would perform better than using a CTE?  We do have an index on Queue_id.
2. Why is there a large performance difference between IN and EXISTS in the delete?
 
Any feedback on how to tune this to perform better or reduce blocking further?

Some more notes:

- Both the CTE and temp table are available using paste the plan (links above). 
- There are FKs to two other tables with cascading update and delete, and the CTE plan spends more time on the delete there, while the temp table version spends more of its time on the main table. 
- Generally speaking, are there performance benefits from using a temp table like this vs. a CTE? 
- I'm not allowed to post table schemas, so I apologize if the plans are not enough info.

I'm also going to test using a view like in this article . 


                        
Asked by DBA Greg14 (265 rep)
Nov 30, 2022, 02:42 PM
Last activity: Dec 1, 2022, 01:35 PM
CTE vs. temp table for batch deletes

Related Questions
