2

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.

0

2 Answers 2

4

weird

The performance issue in both queries has nothing to do with CTE vs. temp table. I see there is a timing difference, but hear me out a little bit.

just delete

In the straight delete, you spend the most time deleting from tables, not selecting from them.

NUTS

The waits in this query are all related to reading from and writing to disk:

NUTS

#temp table

The query plan for the delete from the #temp table show the most time spent in the same two places (just more time):

NUTS

The waits for this query have the same bottlenecks as before, just more of them:

NUTS

differences

Given the hardware difficulties both queries face, I would half expect the timing differences to not be repeatable or reliable, but more a matter of happenstance.

Since they are repeatable, you should take care to apply the same hints to both queries. Your first query has (updlock, readpast) specified. Skipping row locks via the readpast hint could be the reason for the pronounced difference, here.

From the docs:

When READPAST is specified, row-level locks are skipped but page-level locks are not skipped.

Since that locking hint doesn't make much sense here, try using a paglock or tablock hint with your query to skip row-level locking.

Note the wait stats again:

NUTS

In the slower query, the wait count is ~2x of the faster query.

Regardless, the hardware you're on is incompatible with a production database workload.

2
  1. What is better depends on the use. You can see this article for more information. https://www.brentozar.com/archive/2019/06/whats-better-ctes-or-temp-tables/
  2. This is because you have a mistake in your EXISTS query, it will now remove everything from that table as long as there is data in the temporary table. You should replace that line with:

where exists(select 1 from #Table_AToDelete AS a WHERE a.queue_id = A.queue_id)

Assuming that in the real table there is also a column named queue_id.

1
  • Thank you peter, i will review that post. the real table does have a column named Queue_id. its the PK, which is why i created the PK on the temp table on the same column.
    – DBA Greg14
    Nov 30, 2022 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.