sql 标量子查询
Nowadays a lot of developers use Object-Relational Mapping (ORM) frameworks. ORM is a programming technique that maps data from an object-oriented to a relational format, i.e. it allows a developer to abstract from a relational database (SQL Server, for example), use object-oriented language (C#, for example) and let an ORM to do all the “talks” to a database engine by generating query texts automatically. ORMs are not perfect, especially if they are used in a wrong way. Sometimes they generate inefficient queries, e.g. a query with redundant expressions. SQL Server has a mechanism to struggle with that inefficiency called a query simplification.
如今，许多开发人员都使用对象关系映射（ORM）框架。 ORM是一种编程技术，可以将数据从面向对象的格式映射到关系格式，即，它允许开发人员从关系数据库（例如SQL Server）中进行抽象，使用面向对象的语言（例如C＃）并让ORM通过自动生成查询文本来与数据库引擎进行所有“对话”。 ORM并不是完美的，尤其是如果错误地使用它们。 有时它们会生成效率低下的查询，例如带有冗余表达式的查询。 SQL Server具有一种可以解决这种低效率的机制，称为查询简化。
Query simplification is a pre-optimization phase that is run during the query compilation, but before the actual optimization search is started. During that phase the optimizer applies simplification rules against a query tree. The simplification rule represents an algorithm that transforms some portion of a query tree or the whole tree into a simpler form. In this post, we will talk about the new optimizer rule in SQL Server 2017 – CollapseIdenticalScalarSubquery.
查询简化是在查询编译过程中但在开始实际优化搜索之前运行的预优化阶段。 在该阶段，优化器对查询树应用简化规则。 简化规则表示一种将查询树或整个树的某些部分转换为更简单形式的算法。 在本文中，我们将讨论SQL Server 2017中的新优化器规则– CollapseIdenticalScalarSubquery。
折叠子查询 (Collapsing Subqueries)
I have tried different variants of scalar subqueries to make this rule work, but fortunately, the optimizer team was kind enough to guide me – a query pattern for this rule is:
我尝试了标量子查询的不同变体来使此规则起作用，但是幸运的是，优化器团队很乐于指导我–该规则的查询模式为：
SELECT CASE WHEN EXISTS (subquery) THEN … END, CASE WHEN EXISTS (the same subquery)
I asked myself, who may write a query like this, and I think the answer might be an ORM.
我问自己，谁可以编写这样的查询，我认为答案可能是ORM。
Let’s look at the example. We run three queries, the first query is under compatibility level of 2016 (130), the second one under 2017 (140), the third also under 2017, but with the rule CollapseIdenticalScalarSubquery turned off.
让我们来看一个例子。 我们运行三个查询，第一个查询的兼容性级别为2016（130），第二个查询的兼容性级别为2017（140），第三个查询的兼容性级别也为2017，但关闭了CollapseIdenticalScalarSubquery规则。
use [Adventureworks2016CTP3];
go
-- Query under Compatibility Level 2016
alter database [Adventureworks2016CTP3] set compatibility_level = 130;
go
set showplan_xml on;
go
select
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID= d.SalesOrderID) then 1 else 0 end,
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID = d.SalesOrderID) then 2 else 3 end
from
Sales.SalesOrderHeader d;
go
set showplan_xml off;
go
-- Query under Compatibility Level 2017
alter database [Adventureworks2016CTP3] set compatibility_level = 140;
go
set showplan_xml on;
go
select
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID= d.SalesOrderID) then 1 else 0 end,
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID = d.SalesOrderID) then 2 else 3 end
from
Sales.SalesOrderHeader d;
go
-- Query under Compatibility Level 2017 with turned off Rule CollapseIdenticalScalarSubquery
select
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID= d.SalesOrderID) then 1 else 0 end,
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID = d.SalesOrderID) then 2 else 3 end
from
Sales.SalesOrderHeader d
option(queryruleoff CollapseIdenticalScalarSubquery);
go
set showplan_xml off;
go
The plans are accordingly:
相应的计划是：
You may see that in the first plan, there are two clustered index scans of the table SalesOrderDetail, however the subquery is exactly the same “exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID = d.SalesOrderID)” but referenced twice.
您可能会看到，在第一个计划中，对表SalesOrderDetail进行了两次聚集索引扫描，但是子查询是完全相同的“存在（从Sales.SalesOrderDetail f中选择*，其中f.SalesOrderID = d.SalesOrderID）”但被引用了两次。
In the second case, compiled under next compatibility level, the double reference of the subquery is collapsed and we see only one reference to the SalesOrderDetails table and more efficient plan, despite the query still has two subqueries with SalesOrderDetails.
在第二种情况下，在下一个兼容性级别下进行编译，该子查询的双重引用被折叠，尽管该查询仍然具有两个带有SalesOrderDetails的子查询，但我们仅看到对SalesOrderDetails表的引用和更有效的计划。
In the third case, also compiled under 2017 level, we see the second branch with the SalesOrderDetail again, but that is because we turned off the rule CollapseIdenticalScalarSubquery with an undocumented hint queryruleoff (which I originally described in my blog post).
在第三种情况下（也是在2017年级别下编译的），我们再次看到第二个分支带有SalesOrderDetail，但这是因为我们关闭了规则CollapseIdenticalScalarSubquery和未记录的提示queryruleoff（我最初在我的博客文章中对此进行了描述）。
If you look into the DMV sys.dm_exec_query_transformation stats, you will see the succeed counter has increased after the rule was used during the query compilation:
如果查看DMV sys.dm_exec_query_transformation统计信息，您将看到在查询编译期间使用该规则后，成功计数器增加了：
select name, succeeded from sys.dm_exec_query_transformation_stats where name = 'CollapseIdenticalScalarSubquery'
go
use [Adventureworks2016CTP3];
go
-- Query under Compatibility Level 2016
alter database [Adventureworks2016CTP3] set compatibility_level = 140;
go
set statistics xml on;
select
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID= d.SalesOrderID) then 1 else 0 end,
case when exists (select * from Sales.SalesOrderDetail f where f.SalesOrderID = d.SalesOrderID) then 2 else 3 end
from
Sales.SalesOrderHeader d
option(recompile);
set statistics xml off;
go
select name, succeeded from sys.dm_exec_query_transformation_stats where name = 'CollapseIdenticalScalarSubquery'
go
The result is:
结果是：
use [AdventureworksDW2016CTP3]
go
alter database [AdventureworksDW2016CTP3] set compatibility_level = 140;
go
set showplan_xml on;
go
select
case when exists (select * from dbo.FactResellerSales f where f.DueDate = d.DateKey) then 1 else 0 end,
case when exists (select * from dbo.FactResellerSales f where f.DueDate = d.DateKey) then 2 else 3 end
from
dbo.DimDate d
go
set showplan_xml off;
go
The plan is:
该计划是：
The plan uses FactResellerSales table twice and do not benefit from the new simplification rule. Probably, this functionality will be evolved in the future, but nowadays it works in this way.
该计划使用FactResellerSales表两次，并且不会从新的简化规则中受益。 此功能可能会在将来得到发展，但如今它以这种方式起作用。
结论 (Conclusion)
Unfortunately, this rule also does not solve a quite popular problem, that was described by Erland Sommarskog on the Connect site: Unnecessarily bad performance for coalesce(subquery), however, I hope that, as far as Microsoft invests some time to this kind of problems, the problem from the Connect site might be addressed in future.
不幸的是，该规则也不能解决一个非常普遍的问题，该问题在Connect网站上由Erland Sommarskog进行了描述： 合并（subquery）的性能不必要地很差 ，但是，我希望，只要Microsoft在此方面花了一些时间。问题，将来可能会解决Connect网站上的问题。
That’s all for today, thank you for reading and stay tuned!
今天就这些，谢谢您的阅读和关注！
目录 (Table of contents)
翻译自: https://www.sqlshack.com/sql-server-2017-scalar-subquery-simplification/
sql 标量子查询
1293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
