sql - 链式 CTE 与 Temp 表的性能影响

Question

我有以下链式 CTE 查询(简化):

;WITH CTE1
AS(    
    SELECT * FROM TableA
),
CTE2
AS(
    SELECT * FROM TableB b INNER JOIN CTE1 c ON b.id = c.id
)

SELECT * FROM CTE2

如果我打破 CTE 链并将 CTE1 的数据存储到临时表中，那么整体查询的性能会提高(从 1 分 20 秒到 8 秒)。

;WITH CTE1
AS(    
    SELECT * FROM TableA
)

SELECT * INTO #Temp FROM CTE1

;WITH CTE2
AS(
    SELECT * FROM TableB b INNER JOIN #Temp c ON b.id = c.id
)

SELECT * FROM CTE2
DROP TABLE #Temp

CTE1 和 CTE2 中有复杂的查询。我刚刚创建了一个简化版本来解释这里。

打破CTE椅子应该提高性能吗？

SQL Server 版本:2008 R2

Answer 1

显然，正如您自己所展示的那样，它可以。

为什么？最明显的原因是优化器知道临时表的大小。这为其提供了更多用于优化查询的信息。 CTE 只是一个估计值。因此，您所看到的改进归功于查询计划。

另一个原因是 CTE 在查询中被多次引用。 SQL Server 不会实现 CTE，因此定义代码将运行多次。

有时，您故意将 CTE 物化为临时表，以便您可以向它们添加索引。这也可以提高性能。

尽管如此，我更愿意避免使用临时表。优化器通常非常好。