Sql Server - OUTER APPLY vs. Subqueries

Question

Please consider the following 2 statements on the Sql server:

This method uses subqueries:

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 

And this uses the OUTER APPLY statement:

  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 

Which of these 2 could potentially improve, given that both Segment tables can have millions of rows?

My intuition is that OUTER APPLY will work better.

A couple more questions:

• I am almost sure of this, but still want to confirm that in the first solution, CTE will be executed twice efficiently (because its link is twice and CTE is expanded as a built-in macro).
• Will CTE run once for each line when used in an OUTER APPLY statement? It will also be executed for each row when used in a subquery in the first expression

Answer 1

First get rid of the Top 100 Percent in CTE. You are not using TOP here, and if you want the results to be sorted, you must add Order By at the end of the whole statement. Secondly, to answer the question about performance, and if you made me guess, my bid will be in the second form only because it has one subquery instead of two. Thirdly, another form you could try:

 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 

Another opportunity

 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

Maybe I will use this variation of Thomas' query:

 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