Sql Server - OUTER APPLY与子查询

Question

Sql Server - OUTER APPLY与子查询

sqlsql-servercommon-table-expression

10

请考虑在Sql Server中的以下两个语句：

这个使用了嵌套子查询：

```sql SELECT * FROM table1 WHERE id IN (SELECT id FROM table2 WHERE condition) ```

This one is using Join:

这个使用了连接：

```sql SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE condition ```

    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表都可能有数百万行，哪一个潜在地表现更好？

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

还有几个问题：

我相当确定这一点，但仍想确认一下，在第一种解决方案中，CTE将被有效地执行两次（因为它被引用了两次，而且CTE像宏一样扩展）。
在使用OUTER APPLY运算符时，每行都会执行CTE吗？在第一个语句的嵌套查询中使用时，每行都会执行吗？

- r_honey

8

运行，检查查询计划。 - Mitch Wheat

1

“TOP 100 PERCENT ... ORDER BY” 这种写法是被优化掉了，没有实际作用。我同意第二种写法应该会更好一些。你也可以考虑使用 ROW_NUMBER 和 PARTITION BY 来获取每个组的 TOP 1。 - Martin Smith

2个回答

1

也许我会使用这个版本的Thomas的查询：

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

- r_honey

如果SegmentId是主键，那么每一行的num将为1。 - Thomas

@Thomas：是的，SegmentId是主键。我认为基于PARTITION BY或OVER子句的任何解决方案在这种情况下都不可行，包括你发布的那个。 - r_honey

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Thomas · Accepted Answer

首先，从CTE中去掉Top 100 Percent。在这里你没有使用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