投影中相关子查询排序的影响 [英] Impact of ordering of correlated subqueries within a projection

问题描述

我注意到 SQL Server(在本例中为 SQL Server 2008)处理 select 语句中的相关子查询的方式有点出乎意料.我的假设是，查询计划不应仅受在 select 语句的投影子句中写入子查询(或列，就此而言)的顺序的影响.然而，情况似乎并非如此.

I'm noticing something a bit unexpected with how SQL Server (SQL Server 2008 in this case) treats correlated subqueries within a select statement. My assumption was that a query plan should not be affected by the mere order in which subqueries (or columns, for that matter) are written within the projection clause of the select statement. However, this does not appear to be the case.

考虑以下两个查询，除了 CTE 中子查询的顺序不同之外，它们是相同的:

Consider the following two queries, which are identical except for the ordering of the subqueries within the CTE:

--query 1: subquery for Color is second
WITH vw AS
(
 SELECT p.[ID],
  (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName],
  (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color]
 FROM Person p
)
SELECT ID, Color, FirstName
FROM vw
WHERE Color = 'Gray';


--query 2: subquery for Color is first
WITH vw AS
(
 SELECT p.[ID],
  (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color],
  (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName]
 FROM Person p
)
SELECT ID, Color, FirstName
FROM vw
WHERE Color = 'Gray';

如果您查看两个查询计划，您会发现每个子查询都使用了外连接，并且连接的顺序与编写子查询的顺序相同.有一个过滤器应用于颜色的外部连接的结果，以过滤掉颜色不是灰色"的行.(对我来说，SQL 对颜色子查询使用外连接很奇怪，因为我对颜色子查询的结果有一个非空约束，但可以.)

If you look at the two query plans, you'll see that an outer join is used for each subquery and that the order of the joins is the same as the order the subqueries are written. There is a filter applied to the result of the outer join for color, to filter out rows where the color is not 'Gray'. (It's odd to me that SQL would use an outer join for the color subquery since I have a non-null constraint on the result of the color subquery, but OK.)

大部分行被滤色器移除.结果是查询 2 比查询 1 便宜得多，因为第二个连接涉及的行更少.撇开构建这样一个语句的所有原因，这是预期的行为吗?无论子查询的编写顺序如何，SQL Server 是否应该选择在查询计划中尽早移动过滤器?

Most of the rows are removed by the color filter. The result is that query 2 is significantly cheaper than query 1 because fewer rows are involved with the second join. All reasons for constructing such a statement aside, is this an expected behavior? Shouldn't SQL server opt to move the filter as early as possible in the query plan, regardless of the order the subqueries are written?

澄清一下，我正在探索这种情况是有正当理由的.我可能需要创建一个包含类似构造子查询的视图，现在很明显，基于从视图投影的这些列的任何过滤都会因为列的排序而在性能上有所不同！

Just to clarify, there is a valid reason I'm exploring this scenario. I may need to create a view that involves similarly constructed subqueries, and it is now apparent that any filtering based on these columns projected from the view will vary in performance just because of the ordering of the columns!

推荐答案

这里有一个可能表现更好的替代版本:

Here is an alternate version that might perform better:

With Colors As
    (
    Select Id, [Color]
        , ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY [LastModified] DESC ) As Num
    From Preference
    Where [Color] Is Not Null
    )
    , Names As
    (
    Select Id, [FirstName]
        , ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY [LastModified] DESC ) As Num
    From Preference
    Where [FirstName] Is Not Null
    )
Select
From Person As P
    Join Colors As C
        On C.Id = P.Id
            And C.Num = 1
    Left Join Names As N
        On N.Id = P.Id
            And N.Num = 1
Where C.[Color]= 'Grey'

另一种更简洁但性能可能好也可能差的解决方案:

Another solution which is more concise but may or may not perform as well:

With RankedItems
    (
    Select Id, [Color], [FirstName]
        , ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Case When [Color] Is Not Null 1 Else 0 End DESC, [LastModified] DESC ) As ColorRank
        , ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY Case When [FirstName] Is Not Null 1 Else 0 End DESC, [LastModified] DESC ) As NameRank
    From Preference
    )
Select
From Person As P
    Join RankedItems As RI
        On RI.Id = P.Id
            And RI.ColorRank = 1
    Left Join RankedItems As RI2
        On RI2.Id = P.Id
            And RI2.NameRank = 1
Where RI.[Color]= 'Grey'

这篇关于投影中相关子查询排序的影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！