SQL 2005 CTE 与 TEMP 表在其他表的连接中使用时的性能 [英] SQL 2005 CTE vs TEMP table Performance when used in joins of other tables

问题描述

我有一个复杂的查询，需要在后续查询中使用(实际上是更新语句).我尝试过使用 CTE 和临时表.与临时表方法相比，使用 CTE 的性能非常糟糕.它类似于 15 秒 vs 毫秒.为了简化测试而不是在后续查询中加入 CTE/Temp 表，我只是从中选择了 *.在这种情况下，它们的表现相同.

I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. Its something like 15 seconds vs milliseconds. To simplify the test instead of joining the CTE/Temp table in the subsequent query I simply selected * from it. In that case they perform the same.

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

I Have Looked at The Execution Plan for both approaches both with the joins in the subsequent query and then simply select *. With the simple select the query plans are about the same, but with the joins in the subsequent select the query plans are not. Specifically the portion of the query plan for creating and populating the temp table stays the same, while the query plan portion for creating and populating the CTE changes dramatically when it is subsequently used in a query with a join.

我的问题是，为什么创建和填充 CTE 的查询计划会随着它随后的使用方式而改变，而临时表没有.另外在什么情况下，CTE 会产生比临时表更好的性能?

My question is why does the query plan for the creation and population of the CTE change by how it is subsequently used while the temp table is not. Also in what scenarios then would a CTE yield better performance than a temp table?

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

*Note I have used a table variable as well and it is comparable to the temp table approach.

谢谢

推荐答案

你问的是一个复杂的问题，所以你得到了一个复杂的答案:这要看情况.(我讨厌那个回应).

You're asking a complicated question, so you're getting a complicated answer: it depends. (I hate that response).

但是，说真的，这与优化器如何选择数据计划有关(您已经知道了)；临时表或变量就像一个永久结构，因为执行计划将首先执行与填充该结构相关的操作，然后在后续操作中使用该结构.CTE 不是临时表；CTE 的使用在后续操作使用之前不会计算，因此使用会影响计划的优化方式.

Seriously, however, it has to do with how the optimizer chooses a data plan (which you knew already); a temp table or variable is like a permanent structure in that an execution plan will perform the operation associated with filling that structure first, and then use that structure in subsequent operations. A CTE is NOT a temp table; use of the CTE is not calculated until it is being used by subsequent operations, and so that usage impacts how the plan is optimized.

CTE 的实施是为了可重用性和维护问题，不一定是性能；然而，在许多情况下(如递归)，它们的性能会优于传统编码方法.

CTE's were implemented for reusability and maintenance issues, not necessarily performance; however, in many cases (like recursion), they will perform better than traditional coding methods.

这篇关于SQL 2005 CTE 与 TEMP 表在其他表的连接中使用时的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！