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

问题描述

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

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

我的问题是，为什么创建CTE和填充CTE的查询计划会因随后不使用temp表而如何使用它而改变。同样在什么情况下CTE会比临时表产生更好的性能？

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

谢谢

解决方案

您在问一个复杂的问题，所以您会得到一个复杂的答案：这取决于。 （我讨厌这样的回答。）

但是，严重的是，它与优化器如何选择数据计划（您已经知道）有关。临时表或变量类似于永久性结构，因为执行计划将执行与首先填充该结构相关的操作，然后在后续操作中使用该结构。 CTE不是临时表；它是临时表。在随后的操作中使用CTE之前，不会计算出CTE的使用情况，因此使用情况会影响计划的优化方式。 ，不一定是性能；但是，在许多情况下（例如递归），它们的性能将比传统编码方法更好。

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.

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.

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.

Thanks

解决方案

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

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'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屋！