SQL Server如何执行transaction脚本?(同样的问题花在高管身上的时间大不相同)

Question

我在一家拥有大量SQL脚本的大公司工作。这个用户抱怨他的脚本太慢，我开始看它。为了找出慢的地方，我把问题分解开来，选择插入Temp表，然后在他的实际问题中使用Temp表。

但是，最终的结果是，如果我保留临时表，问题的执行速度会快得多。

All in one question: Just shy of 20 minutes.
My broken up question: A bit more than 2 minutes.

两者之间存在显著差异。

好的，整个脚本有940行长，我在Temp表中提出的每个问题大约有70-80行。

我的Temp表工作的结果是一个看起来像这样的问题：

with cte_A
as (
select *
from #Temp1
union all
select *
from #Temp2
union all
select *
from #Temp3)
, cte_B
as (select *
from #Temp4)
, cte_C
as (select *
from #Temp5)
, OB_BL_NotOverdue
as (select *
from #Temp6)
, cte_D
as (select *
from #Temp7)
, cte_E
as (select *
from #Temp8
union all
select *
from #Temp9)
SQL question based on the above.

现在，原来的问题有一个完整的70-80行问题来代替select * from #TempX，所以唯一的区别是，代替70-80行SQL，我在问题之前执行该SQL并执行select *。总运行时间(创建所有Temp表并运行问题(为2分钟。所以我在两次处决中都做了完全相同的工作。

我们运行的服务器是SQL server 2016(所以不是最新版本(。

有人能解释一下原因吗？

Answer 1

首先，CTE没有什么比编写代码和进行分离更好的了，但对于引擎来说，它就像子查询一样工作。

因此，如果将所有查询都放在CTE中，并且它们正在访问相同的表，则可能会混淆SQL引擎，从而增加I/O读取次数。

因此，一旦将这些内容分别存储在Temporary table中，数据就会存储在tempdb中的SQL Server内存中。因此，对于SQL引擎来说，从内存和tempdb读取数据几乎总是比选择一些特定的计划来有效地从磁盘读取数据更容易。