投影内相关子查询的排序的影响

小编典典

投影内相关子查询的排序的影响

sql

我注意到SQL Server（在本例中为SQL Server
2008）如何处理select语句中的相关子查询有些意外。我的假设是，查询计划不应仅受子查询（或相应的列）在select语句的projection子句中写入的顺序的影响。但是，事实并非如此。

请考虑以下两个查询，除了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对颜色子查询使用外部联接是很奇怪的，因为我对颜色子查询的结果具有非null约束，但是可以。）

大部分行被滤色器删除。结果是查询2比查询1便宜得多，因为第二个联接涉及的行较少。除了构造这样的语句的所有原因之外，这是预期的行为吗？无论子查询的写入顺序如何，SQL
Server都不应该选择在查询计划中尽早移动过滤器吗？

编辑：
只是为了澄清，有一个有效的原因，我正在探索这种情况。我可能需要创建一个包含类似构造的子查询的视图，现在很明显，基于这些视图从这些列投影的任何过滤都会由于列的顺序而导致性能变化！

阅读 211

2021-05-30

共1个答案

小编典典

这是一个可能表现更好的替代版本：

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'

另一个更简洁但效果可能不佳的解决方案：

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'

2021-05-30