CTE，子查询，临时表或表变量之间有性能差异吗?

在这个优秀的所以问题中，讨论了CTE和sub-queries之间的区别。

我特别想问:

在什么情况下，下列每一项都更有效/更快?

  • CTE
  • 子查询
  • 临时表
  • 表变量

传统上，我在开发stored procedures时使用了大量的temp tables——因为它们似乎比大量交织在一起的子查询更具可读性。

Non-recursive CTEs封装数据集非常好，并且非常可读，但是否有特定的情况下，人们可以说他们总是表现得更好?还是说，为了找到最有效的解决方案，总是要在不同的选项中折腾?

编辑

最近有人告诉我，在效率方面，临时表是一个很好的首选，因为它们有一个相关的直方图，即统计数据。

189087 次浏览
小开
最佳答案

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)。

小开

我认为使用# Temp表比使用CTE更可取的两件事是:

  1. 您不能在CTE上放置主键，因此CTE访问的数据必须遍历CTE表中的每个索引，而不是仅仅访问临时表上的PK或Index。

  2. 因为不能向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;

提交答案