CTE、子查询、临时表或表变量之间是否存在性能差异

在这个优秀的SO问题中，讨论了CTE和sub-queries之间的差异。

我想特别问：

在什么情况下，以下每个方法都更有效/更快？

CTE
子查询
临时表
表变量

传统上，我在开发stored procedures中使用了很多temp tables - 因为它们似乎比许多相互交织的子查询更具可读性。

Non-recursive CTE很好地封装了数据集，并且非常可读，但是是否有特定情况可以说它们总是表现得更好？还是必须始终摆弄不同的选项以找到最有效的解决方案？

编辑

我最近被告知，就效率而言，临时表是一个很好的首选，因为它们具有相关的直方图，即统计数据。

SQL

是一种声明性语言，而不是过程语言。也就是说，您可以构造一个 SQL 语句来描述所需的结果。你不是在告诉SQL引擎如何做这项工作。

作为一般规则，最好让 SQL 引擎和 SQL 优化器找到最佳查询计划。开发SQL引擎需要花费许多人年的努力，所以让工程师做他们知道该怎么做的事情。

当然，在某些情况下，查询计划不是最佳的。然后，您希望使用查询提示、重构查询、更新统计信息、使用临时表、添加索引等以获得更好的性能。

至于你的问题。从理论上讲，CTE 和子查询的性能应该是相同的，因为两者都向查询优化器提供相同的信息。一个区别是，多次使用的CTE可以很容易地识别和计算一次。然后可以多次存储和读取结果。遗憾的是，SQL Server 似乎没有利用这种基本的优化方法（您可以称之为常见的子查询消除）。

临时表是另一回事，因为您将提供有关如何运行查询的更多指导。一个主要区别是优化程序可以使用临时表中的统计信息来建立其查询计划。这可以提高性能。此外，如果您有多次使用的复杂 CTE（子查询），则将其存储在临时表中通常会提高性能。查询仅执行一次。

问题的答案是，您需要尝试以获得预期的性能，特别是对于定期运行的复杂查询。在理想情况下，查询优化器将找到完美的执行路径。尽管经常这样做，但您可以找到一种获得更好性能的方法。

没有规则。我发现 CTE 更具可读性，除非它们表现出一些性能问题，否则会使用它们，在这种情况下，我会调查实际问题，而不是猜测 CTE 是问题所在并尝试使用不同的方法重写它。这个问题通常比我选择以声明方式陈述查询意图的方式更多。

当然，在某些情况下

，您可以解开 CTE 或删除子查询并将其替换为 #temp 表并缩短持续时间。这可能是由于各种原因造成的，例如过时的统计信息，甚至无法获得准确的统计信息（例如，连接到表值函数），并行性，甚至由于查询的复杂性而无法生成最佳计划（在这种情况下，将其分解可能会给优化器一个战斗的机会）。但也在某些情况下，创建 #temp 表所涉及的 I/O 可能超过其他性能方面，这些方面可能会降低使用 CTE 的特定计划形状的吸引力。

老实说，有太多的变量无法为您的问题提供"正确"的答案。没有可预测的方法可以知道查询何时可能倾向于一种或另一种方法 - 只要知道，从理论上讲，CTE 或单个子查询的相同语义应该执行完全相同的语义。我认为如果您提出一些不正确的情况，您的问题会更有价值 - 可能是您在优化器中发现了限制（或发现了已知限制），或者可能是您的查询在语义上不等效，或者一个包含阻止优化的元素。

因此，我建议以您认为最自然的方式编写查询，并且仅在发现优化器遇到的实际性能问题时才会偏离查询。就个人而言，我对它们进行 CTE 排序，然后是子查询，#temp 表是最后的手段。

#temp 是物质化的，而CTE不是。

CTE 只是语法，所以理论上它只是一个子查询。它被执行。 #temp 物化了。因此，在多次执行的联接中，昂贵的 CTE 在 #temp 中可能更好。另一方面，如果这是一个没有执行但几次的简单评估，那么不值得 #temp 开销。

SO 上的一些人不喜欢表变量，但我喜欢它们，因为它们是具体化的，创建速度比 #temp 快。有时，与表变量相比，查询优化器使用 #temp 做得更好。

在 #temp 或表变量上创建 PK 的功能为查询优化器提供了比 CTE 更多的信息（因为您无法在 CTE 上声明 PK）。

我认为只有

两件事使使用 # 临时表而不是 CTE 总是更可取

：

您不能将主键放在 CTE 上，因此 CTE 访问的数据必须遍历 CTE 表中的每个索引，而不仅仅是访问临时表上的 PK 或索引。
由于您无法向 CTE 添加约束、索引和主键，因此它们更容易出现错误和错误数据。

-一天当昨天

下面是一个示例，其中 #table 约束可以防止不良数据，而 CTE 的情况并非如此

DECLARE @BadData TABLE ( 
                       ThisID int
                     , ThatID int );
INSERT INTO @BadData
       ( ThisID
       , ThatID
       ) 
VALUES
       ( 1, 1 ),
       ( 1, 2 ),
       ( 2, 2 ),
       ( 1, 1 );
IF OBJECT_ID('tempdb..#This') IS NOT NULL
    DROP TABLE #This;
CREATE TABLE #This ( 
             ThisID int NOT NULL
           , ThatID int NOT NULL
                        UNIQUE(ThisID, ThatID) );
INSERT INTO #This
SELECT * FROM @BadData;
WITH This_CTE
     AS (SELECT *
           FROM @BadData)
     SELECT *
       FROM This_CTE;

相关内容

最新更新

热门标签：