有CTE，子查询，临时表或表变量之间的性能差异？有CTE，子查询，临时表或表变量之间的性能差异？(I

在这种优良SO问题，之间的差异CTE和sub-queries进行了讨论。

我想特别要求：

在什么情况下每个以下更高效/更快？

CTE
子查询
临时表
表变量

传统上，我用大量的temp tables在开发stored procedures -因为他们似乎比许多交织子查询更具可读性。

Non-recursive CTE小号封装的数据集的非常好，而且非常可读的，但在那里的具体情况，人们可以说，他们将永远有更好的表现？或者是它不必总是反复折腾的不同选择，找到最有效的解决方案的情况下？

编辑

我最近被告知，在效率方面，临时表是一个很好的第一选择，因为他们有一个相关的直方图，即统计数据。

Answer 1:

SQL是一个说明性语言，而不是程序语言。也就是说，你构造SQL语句来描述你想要的结果。你讲的不是SQL引擎如何做的工作。

作为一般规则，这是一个好主意，让SQL引擎和SQL优化器找到最好的查询计划。有迹象表明，进入开发的SQL引擎许多人，多年的努力，所以让工程师做什么，他们知道该怎么做。

当然，在有些情况下查询计划不是最优的。然后，你要使用查询提示，重组查询，更新统计，使用临时表，添加索引，等等，以获得更好的性能。

至于你的问题。热膨胀系数和子查询的性能应该在理论上是相同的，因为两者都提供相同的信息查询优化。一个区别是，一个CTE使用超过一次，可以很容易地识别和计算一次。然后结果可以存储和读取多次。不幸的是，SQL服务器似乎并没有充分利用这个基本的优化方法（可能会调用这个共同的子查询消除）。

临时表是一个不同的问题，因为你提供关于如何查询应运行更多的指导。一个主要的区别是，优化器可以使用统计数据从临时表中建立查询计划。这可能会导致性能提升。另外，如果你有一个复杂的CTE（子查询），它使用一次以上，然后将其存储在临时表往往会给出一个性能提升。该查询只执行一次。

在回答你的问题是，你需要玩，让你期望的，尤其是对于那些定期运行复杂的查询性能。在一个理想的世界，查询优化器会找到最完美的执行路径。虽然它经常做，你可以找到一种方式来获得更好的性能。

Answer 2:

没有规则。采用了不同的方法，我觉得热膨胀系数更具可读性，并使用它们，除非他们表现出一定的性能问题，在这种情况下，我调查的实际问题，而不是猜测，热膨胀系数的问题，并尝试重新写。通常有更多比我选择了声明阐明我的意图与查询方式的问题。

当然，还有的时候你可以解开的CTE或删除子查询，并用#temp表替换它们，并减少持续时间的情况。这可能是由于各种东西，如陈旧的统计数据，无法连得准确的统计数据（例如加入到表值函数），并行性，甚至无法生成因为查询的复杂性最佳计划（在这种情况下，打破它可以使优化一个战斗的机会）。但也有在I / O参与创建#temp表可以胜过其他性能方面，可能使一个使用CTE的吸引力特定的平面形状的情况。

坦率地说，有太多的变量，提供一个“正确”的回答你的问题。有没有知道当一个查询可能赞成一种方法或另一种的提示预见的方式-只知道，在理论上，对于CTE或单个子查询相同的语义应该执行完全一样的。我认为，如果你介绍一些情况下，这是不正确的问题将是更有价值 - 这可能是因为您在优化发现了一个限制（或发现已知的），或者它可能是您的查询是语义不等同或者一个包含阻挠优化的元素。

所以我建议写在这似乎是最自然的你的方式查询，只有当你发现优化器具有实际性能问题偏离。我个人对他们进行排名CTE，那么子查询，与#temp表是不得已而为之。

Answer 3:

#TEMP是materalized和CTE不是。

CTE只是语法，因此从理论上讲，这只是一个子查询。它被执行。 #TEMP物化。所以昂贵的CTE的加入是执行多次可以在#TEMP更好。在另一边，如果它是不执行一个简单的评估，但几次那么不值得#TEMP的开销。

在一些人，这样不喜欢表变量，但我喜欢他们作为被物化，更快速地创造出比#TEMP。有些时候相比，表变量的查询优化器具有更好的#TEMP。

创建一个#TEMP或表变量PK的能力，为查询优化器比CTE的详细信息（因为你不能申报上一CTE PK）。

Answer 4:

只是两件事我想，使其始终最好使用＃临时表而不是一个CTE是：

你不能把一个CTE的主键，以便通过CTE被访问的数据将不得不穿越的CTE的表而不是仅仅访问的临时表的PK或指数的指标中的每一个。
因为你无法约束，索引和主键添加到CTE他们更容易臭虫侵入，坏数据。

-onedaywhen昨天

下面是一个例子，其中#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;

文章来源: Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?