哪些性能更​​高,CTE或临时表？

哪个更高效,CTE或者Temporary Tables？

1> Martin Smith..：

---

这取决于.

首先

什么是通用表格？

(非递归)CTE与其他构造非常相似,这些构造也可以用作SQL Server中的内联表表达式.派生表,视图和内联表值函数.请注意,虽然BOL说CTE"可以被认为是临时结果集",但这是纯粹的逻辑描述.通常情况下,它本身并没有进行物质化.

什么是临时表？

这是存储在tempdb中的数据页上的行的集合.数据页可以部分或全部驻留在存储器中.此外,临时表可以被索引并具有列统计信息.

测试数据

CREATE TABLE T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);

INSERT INTO T(B)
SELECT TOP (1000000)  0 + CAST(NEWID() AS BINARY(4))
FROM master..spt_values v1,
     master..spt_values v2;

例1

WITH CTE1 AS
(
SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T
)
SELECT *
FROM CTE1
WHERE A = 780

在上面的计划中注意,没有提到CTE1.它只是直接访问基表,并被视为相同

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM   T
WHERE  A = 780 

通过将CTE具体化为中间临时表来进行重写将大大适得其反.

实现CTE定义

SELECT A,
       ABS(B) AS Abs_B,
       F
FROM T

将涉及将大约8GB的数据复制到临时表中,然后仍然存在从中进行选择的开销.

例2

WITH CTE2
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY A) AS RN
         FROM   T
         WHERE  B % 100000 = 0)
SELECT *
FROM   CTE2 T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   CTE2 T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

上面的例子在我的机器上大约需要4分钟.

1,000,000个随机生成的值中只有15行与谓词匹配,但昂贵的表扫描发生了16次以找到这些值.

这将是实现中间结果的良好候选者.等效的临时表重写耗时25秒.

INSERT INTO #T
SELECT *,
       ROW_NUMBER() OVER (ORDER BY A) AS RN
FROM   T
WHERE  B % 100000 = 0

SELECT *
FROM   #T T1
       CROSS APPLY (SELECT TOP (1) *
                    FROM   #T T2
                    WHERE  T2.A > T1.A
                    ORDER  BY T2.A) CA 

将查询的一部分中间实现到临时表有时可能是有用的,即使它只被评估一次 - 当它允许重新编译查询的其余部分时利用对具体化结果的统计信息.此方法的一个示例是SQL Cat文章何时分解复杂查询.

在某些情况下,SQL Server将使用假脱机来缓存中间结果,例如CTE,并避免必须重新评估该子树.这在(迁移的)Connect项中讨论提供强制以强制CTE或派生表的中间实现.但是,没有为此创建统计数据,即使假脱机行的数量与估计的数量大不相同,也不可能使进行中的执行计划动态适应响应(至少在当前版本中).自适应查询计划可能成为可能未来).

---

这是回答实际问题的唯一答案(问哪个具有更好的性能而不是差异或哪个是你最喜欢的),并且它正确地回答了这个问题:"它取决于"是正确的答案.它也是支持数据解释的唯一答案,其他几个(票数高)明确声称一个比另一个好,没有参考或证明......要明确,所有这些答案也是**错误**.因为"这取决于"

2> gbn..：

---

我会说他们是不同的概念,但不能说"粉笔和奶酪".

临时表适用于重用或对一组数据执行多个处理过程.

CTE可用于递归或简单地提高可读性.
并且,像视图或内联表值函数也可以像处理宏一样在主查询中进行扩展

临时表是另一个表,其中包含一些围绕范围的规则

我存储了procs,我使用它们(和表变量)

---

临时表还允许有时需要的索引甚至统计,而CTE则不允许.

---

我认为这个答案并不足以突出CTE可能导致糟糕表现的事实.我通常在dba.stackexchange上引用这个[answer](http://dba.stackexchange.com/a/13117/65699).如果我正在查找`cte vs临时表',那么你的问题在我的搜索引擎中排在第二位,所以恕我直言这个答案需要强调CTE更好的弊端.TL;链接答案的DR:*CTE绝不能用于表现.*.我同意这句话,因为我经历过CTE的缺点.

---

在这个答案中没有一个提到性能。

---

@TT。有趣。我发现CTE的表现要好得多

3> 小智..：

---

CTE有其用途 - 当CTE中的数据很小并且与递归表中的情况一样具有强大的可读性改进.但是,它的性能肯定不比表变量好,当一个人处理非常大的表时,临时表明显优于CTE.这是因为您无法在CTE上定义索引,并且当您有大量需要与另一个表连接的数据时(CTE就像一个宏).如果要连接多个表,每个表中包含数百万行记录,则CTE的执行速度会比临时表差得多.

---

我从自己的经历中看到了这一点.CTE的表现明显变慢.

---

CTE的执行速度也较慢,因为结果没有缓存.因此,每次使用CTE时,它都会重新运行查询,计划和所有操作.

4> marc_s..：

---

临时表始终在磁盘上 - 因此只要您的CTE可以保存在内存中,它很可能会更快(就像表变量一样).

但话又说回来,如果你的CTE(或临时表变量)的数据负载太大,它也会存储在磁盘上,所以没有什么大的好处.

一般来说,我比临时表更喜欢CTE,因为它在我使用它之后就消失了.我不需要考虑明确地删除它或任何东西.

所以,最后没有明确的答案,但就个人而言,我更喜欢CTE而不是临时表.

---

对于SQLite和PostgreSQL,临时表*会自动删除(通常在会话结束时).我不知道其他DBMS.

5> selvaraj..：

---

CTE不会占用任何物理空间.它只是我们可以使用join的结果集.

临时表是临时的.我们可以创建索引,约束就像普通表一样,我们需要定义所有变量.

临时表的范围仅在会话中.EX:打开两个SQL查询窗口

create table #temp(empid int,empname varchar)
insert into #temp 
select 101,'xxx'

select * from #temp

在第一个窗口中运行此查询,然后在第二个窗口中运行以下查询,您可以找到差异.

select * from #temp

---

>>"它只是一个我们可以使用连接的结果集." - >这不准确.CTE不是"结果集",而是内联代码.SQL Server查询引擎将CTE代码解析为查询文本的一部分,并根据构建执行计划.CTE内联的想法是使用CTE的一大优势,因为它允许服务器创建"组合执行计划"

6> 小智..：

---

因此,我分配给优化的查询是用SQL Server中的两个CTE编写的.花了28秒.

我花了两分钟将它们转换为临时表,查询耗时3秒

我在正在连接的字段上为临时表添加了一个索引,并将其降低到2秒

三分钟的工作,现在通过移除CTE,运行速度提高了12倍.我个人不会使用CTE,因为它们也更难以调试.

疯狂的事情是,CTE都只使用过一次,而且它们的指数仍被证明要快50%.