CTE，子查询，临时表或表变量之间是否存在性能差异？ [英] Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?

问题描述

在这个出色的 SO问题中， CTE之间的差异和子查询进行了讨论。

In this excellent SO question, differences between CTE and sub-queries were discussed.

我想特别问一下：

以下哪种情况下效率更高/更快？

In what circumstance is each of the following more efficient/faster?

• CTE


• 子查询


• 临时表


• 表变量

• CTE
• Sub-Query
• Temporary Table
• Table Variable

传统上，我在开发存储过程中使用了很多 temp表 $ c>-因为它们似乎比许多相互交织的子查询更具可读性。

Traditionally, I've used lots of temp tables in developing stored procedures - as they seem more readable than lots of intertwined sub-queries.

非递归CTE 很好地封装数据集，并且可读性很强，但是在特定情况下有人可以说它们总是会更好吗？还是必须总是摆弄各种选项以找到最有效的解决方案？

Non-recursive CTEs encapsulate sets of data very well, and are very readable, but are there specific circumstances where one can say they will always perform better? or is it a case of having to always fiddle around with the different options to find the most efficient solution?

EDIT

最近有人告诉我，就效率而言，临时表是不错的首选，因为它们具有相关的直方图，即统计信息。 / p>

I've recently been told that in terms of efficiency, temporary tables are a good first choice as they have an associated histogram i.e. statistics.

推荐答案

SQL是声明性语言，而不是过程语言。也就是说，您构造一个SQL语句来描述所需的结果。您并没有告诉SQL引擎如何完成工作。

SQL is a declarative language, not a procedural language. That is, you construct a SQL statement to describe the results that you want. You are not telling the SQL engine how to do the work.

通常，让SQL引擎是一个好主意和SQL优化器找到最佳的查询计划。开发SQL引擎需要花费很多人年的精力，所以让工程师去做他们知道怎么做的事情。

As a general rule, it is a good idea to let the SQL engine and SQL optimizer find the best query plan. There are many person-years of effort that go into developing a SQL engine, so let the engineers do what they know how to do.

当然，在某些情况下，查询计划不是最佳的。然后，您想使用查询提示，重组查询，更新统计信息，使用临时表，添加索引等等，以获得更好的性能。

Of course, there are situations where the query plan is not optimal. Then you want to use query hints, restructure the query, update statistics, use temporary tables, add indexes, and so on to get better performance.

针对您的问题。从理论上讲，CTE和子查询的性能应该相同，因为它们都向查询优化器提供了相同的信息。一个区别是，多次使用的CTE可以轻松识别和计算一次。然后可以存储结果并读取多次。不幸的是，SQL Server似乎没有利用这种基本的优化方法（您可能将其称为常见的子查询消除）。

As for your question. The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. The results could then be stored and read multiple times. Unfortunately, SQL Server does not seem to take advantage of this basic optimization method (you might call this common subquery elimination).

临时表是另一回事，因为您提供有关如何运行查询的更多指导。一个主要区别是优化器可以使用临时表中的统计信息来建立其查询计划。这样可以提高性能。另外，如果您有一个不止一次使用的复杂CTE（子查询），那么将其存储在临时表中通常会提高性能。该查询仅执行一次。

Temporary tables are a different matter, because you are providing more guidance on how the query should be run. One major difference is that the optimizer can use statistics from the temporary table to establish its query plan. This can result in performance gains. Also, if you have a complicated CTE (subquery) that is used more than once, then storing it in a temporary table will often give a performance boost. The query is executed only once.

您的问题的答案是，您需要反复试验才能获得期望的性能，尤其是对于在数据库上运行的复杂查询而言定期的。在理想情况下，查询优化器将找到理想的执行路径。尽管它经常发生，但是您也许可以找到一种获得更好性能的方法。

The answer to your question is that you need to play around to get the performance you expect, particularly for complex queries that are run on a regular basis. In an ideal world, the query optimizer would find the perfect execution path. Although it often does, you may be able to find a way to get better performance.

这篇关于CTE，子查询，临时表或表变量之间是否存在性能差异？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！