Sql Server - 外部应用与子查询

Question

请考虑 Sql Server 中的以下 2 条语句：

这一条使用嵌套子查询：

    WITH cte AS
(
    SELECT TOP 100 PERCENT *
    FROM Segments
    ORDER BY InvoiceDetailID, SegmentID
)
SELECT *, ReturnDate =
                (SELECT TOP 1 cte.DepartureInfo
                    FROM cte
                    WHERE seg.InvoiceDetailID = cte.InvoiceDetailID
                        AND cte.SegmentID > seg.SegmentID), 
            DepartureCityCode =
                (SELECT TOP 1 cte.DepartureCityCode
                    FROM cte
                    WHERE seg.InvoiceDetailID = cte.InvoiceDetailID
                        AND cte.SegmentID > seg.SegmentID)
FROM Segments seg

这使用 OUTER APPLY 运算符：

    WITH cte AS
(
    SELECT TOP 100 PERCENT *
    FROM Segments
    ORDER BY InvoiceDetailID, SegmentID
)
SELECT seg.*, t.DepartureInfo AS ReturnDate, t.DepartureCityCode
FROM Segments seg OUTER APPLY (
                SELECT TOP 1 cte.DepartureInfo, cte.DepartureCityCode
                FROM cte
                WHERE seg.InvoiceDetailID = cte.InvoiceDetailID
                        AND cte.SegmentID > seg.SegmentID
            ) t

考虑到两个 Segments 表可能有数百万行，这 2 条语句中哪一条可能会执行得更好？

我的直觉是 OUTER APPLY 会表现得更好。

还有几个问题：

1. 几乎我对此非常确定，但仍然想确认在第一个解决方案中，CTE 将有效地执行两次（因为它被引用两次并且 CTE 像宏一样内联扩展）。
2. 在 OUTER APPLY 运算符中使用时，CTE 会为每行执行一次吗？当在第一个语句的嵌套查询中使用时，它还会对每一行执行吗？

Answer 1

首先，去掉 CTE 中的前 100%。此处未使用 TOP，如果您希望对结果进行排序，则应在整个语句的末尾添加 Order By。其次，为了解决您关于性能的问题，如果被迫进行猜测，我的赌注将是第二种形式，因为它有一个子查询而不是两个。第三，您可以尝试的另一种形式是：

With RankedSegments As
    (
    Select S1.SegmentId, ...
        , Row_Number() Over( Partition By S1.SegmentId Order By S2.SegmentId ) As Num
    From Segments As S1
        Left Join Segments As S2
            On S2.InvoiceDetailId = S1.InvoiceDetailId
                And S2.SegmentId > S1.SegmentID
    )
Select ...
From RankedSegments
Where Num = 1

另一种可能性

With MinSegments As
    (
    Select S1.SegmentId, Min(S2.SegmentId) As MinSegmentId
    From Segments As S1
        Join Segments As S2
            On S2.InvoiceDetailId = S1.InvoiceDetailId
                And S2.SegmentId > S1.SegmentID
    Group By S1.SegmentId
    )
Select ...
From Segments As S1
    Left Join (MinSegments As MS1
        Join Segments As S2
            On S2.SegmentId = MS1.MinSegmentId)
        On MS1.SegmentId = S1.SegmentId

Answer 2

也许我会使用托马斯查询的这种变体：

WITH cte AS
(
SELECT *, Row_Number() Over( Partition By SegmentId Order By InvoiceDetailID, SegmentId ) As Num
FROM Segments)
SELECT seg.*, t.DepartureInfo AS ReturnDate, t.DepartureCityCode
FROM Segments seg LEFT JOIN cte t ON seg.InvoiceDetailID = t.InvoiceDetailID AND t.SegmentID > seg.SegmentID AND t.Num = 1