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

Question

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

考虑以下两个查询，除了 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 会对颜色子查询使用外连接，因为我对颜色子查询的结果有非空约束，但是没关系。）

大多数行都被颜色过滤器删除。结果是查询 2 比查询 1 便宜得多，因为第二个连接涉及的行数较少。抛开构建这样一个声明的所有原因，这是预期的行为吗？ SQL Server 是否应该选择在查询计划中尽早移动过滤器，而不管子查询的写入顺序如何？

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

Answer 1

随着 TOP 运算符在这里发挥作用，查询优化器对统计数据非常盲目，因此它将寻找有关如何最好地工作的其他线索，例如首先实例化 CTE 的相关部分。

它是一个外连接，因为如果没有返回任何内容，子查询将被用作 NULL，并且系统首先实例化它。如果您使用聚合而不是 TOP，您可能会得到一个略有不同但更一致的计划。

Answer 2

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

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'