CTE 和临时表哪个性能更好？

CTE和 Temporary Tables哪个性能更好？

215331 次浏览
小开

这是一个非常开放的问题，这完全取决于如何使用它以及临时表的类型(表变量或传统表)。

传统的临时表将数据存储在临时数据库中，这确实会降低临时表的速度; 但是表变量不会。

小开

临时表总是在磁盘上-所以只要您的 CTE 可以保存在内存中，它很可能会更快(就像一个表变量一样)。

但话又说回来，如果 CTE (或临时表变量)的数据负载过大，它也会存储在磁盘上，因此没有什么大的好处。

一般来说，我更喜欢 CTE 而不是临时表，因为在我使用它之后它就消失了。我不需要考虑明确地放弃它或者其他什么。

因此，最后没有明确的答案，但就我个人而言，我更喜欢 CTE 而不是临时表。

小开
最佳答案

我想说它们是不同的概念，但是说“粉笔和奶酪”并没有太大的不同。

  • 临时表适合于重用或对一组数据执行多个处理传递。

  • CTE 既可用于递归，也可用于简单地提高可读性。
    而且，与视图或内联表值函数一样，也可以将其视为要在主查询

    • 中展开的宏

  • 临时表是另一个包含一些作用域规则的表

我在同时使用这两个变量(以及表变量)的地方存储了 procs

小开

CTE 有它的用途-当 CTE 中的数据很小并且可读性有很大的提高时，就像递归表中的情况一样。但是，它的性能肯定不比表变量好，当处理非常大的表时，临时表的性能明显优于 CTE。这是因为您无法在 CTE 上定义索引，而且当您有大量数据需要与另一个表连接时(CTE 就像一个宏)。如果将多个表与每个表中的数百万行记录联接起来，则 CTE 的性能将显著低于临时表。

小开

派对迟到了，但是..。

我工作的环境受到高度限制，支持一些供应商产品，并提供诸如报告之类的“增值”服务。由于策略和契约的限制，我通常不能享受独立表/数据空间和/或创建永久代码的能力(根据应用程序的不同，这种能力会有所提高)。

IOW，I 不行通常开发一个存储过程或 UDF 或临时表等。我几乎必须通过 MY 应用程序接口完成所有事情(Crystal Reports ——添加/链接表，设置来自 w/in CR 的子句的位置，等等)。一个小的优点是 Crystal 允许我使用 COMMANDS (以及 SQL 表达式)。通过定义一个 SQL 命令可以完成一些通过常规的添加/链接表功能无法高效完成的事情。我通过它使用 CTE，并且“远程”获得了非常好的结果。CTE 还可以帮助进行 w/report 维护，不需要开发代码，将代码交给 DBA 进行编译、加密、传输、安装，然后需要进行多级测试。我可以通过本地接口执行 CTE。

使用 CTEs w/CR 的缺点是，每个报告是独立的。必须为每个报告维护每个 CTE。在我可以使用 SP 和 UDF 的地方，我可以开发一些可以由多个报告使用的东西，只需要链接到 SP 并传递参数，就好像您在处理一个常规表一样。CR 实际上并不擅长将参数处理到 SQL 命令中，因此可能缺少 CR/CTE 方面的内容。在这些情况下，我通常尝试定义 CTE 来返回足够的数据(但不是所有数据) ，然后使用 CR 中的记录选择功能来切片和分割这些数据。

所以... 我的投票是 CTE (直到我得到我的数据空间)。

小开

CTE 不占用任何物理空间，它只是一个结果集，我们可以使用 join。

临时工只是暂时的。我们可以像普通表一样创建索引和约束，为此我们需要定义所有的变量。

临时表的范围仅限于会话内。 例如: 打开两个 SQL 查询窗口

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


select * from #temp

在第一个窗口中运行此查询 然后在第二个窗口中运行下面的查询，您可以找到差异。

select * from #temp
小开

我发现 CTE 的卓越性能的一个用法是，我需要将一个相对复杂的 Query 连接到几个表上，每个表有几百万行。

我使用 CTE 首先选择基于索引列的子集，首先将这些表分割为每个相关的几千行，然后将 CTE 加入到我的主查询中。这极大地减少了查询的运行时。

虽然 CTE 的结果没有被缓存，而表变量可能是一个更好的选择，但我真的只是想尝试一下，并找到适合上述场景的结果。

小开

我刚刚测试了这个—— CTE 和非 CTE (其中每个联合实例都键入了查询)都花了大约31秒。CTE 使得代码更具可读性-从241行减少到130行，这是非常好的。另一方面，临时表削减到132行，并花了5秒运行。没开玩笑。所有这些测试都被缓存了——这些查询以前都运行过多次。

小开

我两种方法都用过，但在大量复杂的过程中，我总是发现使用临时表更好，也更有条不紊。CTE 有它们的用途，但是通常使用较小的数据。

例如，我已经创建了 sprocs，它在15秒内返回大型计算的结果，然后将这段代码转换为在 CTE 中运行，并且看到它运行超过8分钟以获得相同的结果。

小开

看情况。

首先

什么是公共表表达式？

(非递归) CTE 的处理方式与 SQLServer 中也可用作内联表表达式的其他构造非常相似。派生表、视图和内联表值函数。请注意，虽然 BOL 说 CTE“可以被认为是临时结果集”，但这是一个纯粹的逻辑描述。通常情况下，它本身并不具体化。

什么是临时表？

这是 temdb 中存储在数据页上的行的集合。数据页可以部分或全部驻留在内存中。此外，临时表可能被编入索引，并具有列统计信息。

测试数据

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;

例子一

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

Plan 1

注意，在上面的计划中没有提到 CTE1。它只是直接访问基表，并将其视为

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

通过将 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次昂贵的表扫描才能找到这些值。

enter image description here

这将是实现中间结果的一个很好的候选者。

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

With Plan

将查询的一部分中间物化为临时表有时是有用的，即使它只计算一次——当它允许利用物化结果的统计信息重新编译查询的其余部分时。这种方法的一个例子是 SQLCat 文章 何时分解复杂查询。

在某些情况下，SQLServer 将使用后台缓存中间结果(例如 CTE) ，从而避免重新计算子树。这在(迁移的) Connect 项 提供关于强制 CTE 或派生表的中间实体化的提示中进行了讨论。然而，没有创建关于这一点的统计数据，即使假脱机行的数量与估计的数量有很大差异，正在执行的执行计划也不可能动态地适应响应(至少在当前版本中是如此)。自适应查询计划将来可能成为可能)。

小开

根据我在 SQLServer 中的经验，我发现了 CTE 优于 Temp 表的一种情况

我需要在存储过程中仅使用一次来自复杂查询的 DataSet (~ 100000)。

  • 临时表在 SQL 上导致了开销，而我的过程在 SQL 上 执行缓慢(因为临时表是真正的物化表 存在于 tempdb 中，并且在我当前程序的生命周期中持续存在)

  • 另一方面，对于 CTE，CTE 只持续到以下情况 因此，CTE 是一种方便的内存结构，具有有限的 默认情况下，CTE 不使用 temdb

在这种情况下，CTE 可以真正帮助简化代码并优化临时表。 我用过两次 CTE

WITH CTE1(ID, Name, Display)
AS (SELECT ID,Name,Display from Table1 where <Some Condition>),
CTE2(ID,Name,<col3>) AS (SELECT ID, Name,<> FROM CTE1 INNER JOIN Table2 <Some Condition>)
SELECT CTE2.ID,CTE2.<col3>
FROM CTE2
GO
小开

因此，分配给我的优化查询是用 SQL 服务器中的两个 CTE 编写的。只花了28秒。

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

我添加了一个索引到临时表的字段，它正在加入，并得到它下降到2秒

三分钟的工作，现在它的运行速度12倍，所有删除 CTE。我个人不会使用 CTE，因为它们更难调试。

疯狂的是 CTE 都只使用了一次，但仍然在它们上面添加了一个索引，结果证明它们的速度快了50% 。


提交答案