SQL 2005 CTE与TEMP表在其他表的联接中使用时的性能

Question

我有一个复杂的查询，需要在后续查询中使用（实际上是update语句）。我已经尝试过使用CTE和临时表。与临时表方法相比，使用CTE的性能令人恐惧。大概是15秒vs毫秒。为了简化测试，而不是在后续查询中加入CTE
/ Temp表，我仅从中选择*。在这种情况下，它们执行相同的操作。

我已经在后续查询中查看了这两种方法的执行计划，然后只需选择*。使用简单选择时，查询计划大致相同，但是使用后续选择中的联接时，查询计划则不同。具体来说，用于创建和填充临时表的查询计划部分保持不变，而用于创建和填充CTE的查询计划部分随后在带有联接的查询中使用时会发生巨大变化。

我的问题是，为什么在创建CTE和填充CTE的查询计划中，如何在不使用temp表的情况下更改其使用方式呢？而且在什么情况下CTE会比临时表产生更好的性能？

*注意，我也使用了表变量，它与临时表方法相当。

谢谢

Answer 1

您在问一个复杂的问题，所以您会得到一个复杂的答案：这要视情况而定。（我讨厌这样的回应）。

但是，严重的是，它与优化器如何选择数据计划（您已经知道）有关。临时表或变量类似于永久性结构，因为执行计划将执行与首先填充该结构相关的操作，然后在后续操作中使用该结构。CTE不是临时表；它是临时表。在后续操作中使用CTE之前，不会计算CTE的使用情况，因此使用情况会影响计划的优化方式。

CTE是针对可重用性和维护性问题（不一定是性能）而实施的；但是，在许多情况下（例如递归），它们的性能将比传统编码方法更好。