In this excellent SO question, differences between
sub-queries were discussed.
In what circumstance is each of the following more efficient/faster?
临时表 – 因为它们看起来比许多相互交织的子查询更具可读性.
Traditionally, I’ve used lots of
temp tables in developing
stored procedures – as they seem more readable than lots of intertwined sub-queries.
Non-recursive CTEs 很好地封装了数据集，并且非常具有可读性，但是在特定情况下可以说它们总是表现得更好吗?还是必须总是摆弄不同的选项才能找到最有效的解决方案?
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?
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).
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.