用于批量删除的 CTE 与临时表

这里有两部分问题。

我们有一个大表（在 1-2 百万行之间），上面有非常频繁的 DML 操作。在低流量期间，我们有一个代理作业来删除旧行以控制表的大小。它使用下面的 CTE，它在运行时会导致大量阻塞：

;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 查询计划

我重新编写了它以使用临时表来加速查询并减少阻塞，但我发现使用IN与EXISTS确定要删除的行之间存在很大的性能差异。

IN版本：

-- 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)

临时表计划

这个版本在 40-50 秒内运行，但是当我用下面的 delete 语句替换最后一行时：

where exists(select queue_id from #Table_AToDelete)

2分钟后它还在运行，所以我取消了它。

所以，问题：

1. 我以前见过用于帮助阻塞和性能的临时表，但不完全理解为什么它会比使用 CTE 执行得更好？我们确实有一个索引Queue_id。
2. IN为什么delete和delete 中的性能差异很大EXISTS？

关于如何调整它以更好地执行或进一步减少阻塞的任何反馈？

更多注意事项：

• 使用粘贴计划（上面的链接）可以使用 CTE 和临时表。
• 其他两个具有级联更新和删除的表有 FK，CTE 计划将更多时间花在删除那里，而临时表版本将更多时间花在主表上。
• 一般来说，与 CTE 相比，使用这样的临时表是否有性能优势？
• 我不允许发布表模式，所以如果计划没有足够的信息，我深表歉意。

我还将使用本文中的视图进行测试。