按 2 行結果排序導致 800% 的性能下降

以下查詢針對大約 6 百萬行的表執行需要 25 秒。當我刪除最終訂單時，它會在 3 秒內執行。表上沒有索引（它是一個中間 SSIS ETL 目標，後來被拉入 DW。）

它不是記憶體。我以任何順序多次執行它，結果一致。

查詢本身會檢查序列號中的間隙。SSIS 將使用它來查看是否需要重新獲取任何內容。

;with edges as
(
   select 
       ROW_NUMBER() over (partition by s1.SiteIDNumber order by s1.SequenceNumber) as rn,
       s1.SiteIDNumber, 
       s1.SequenceNumber
   from TestPlay s1
   left join TestPlay s2 on s2.SiteIDNumber = s1.SiteIDNumber and s2.SequenceNumber = s1.SequenceNumber + 1
   left join TestPlay s3 on s3.SiteIDNumber = s1.SiteIDNumber and s3.SequenceNumber = s1.SequenceNumber - 1
   where s2.SiteIDNumber is null 
   or s3.SiteIDNumber is null
),
gaps as
(
   select 
       e.rn,
       e.SiteIDNumber,
       e.SequenceNumber  + 1 as StartSeq,
       e2.SequenceNumber - 1 as EndSeq
   from edges e
   join edges e2 on e2.SiteIDNumber = e.SiteIDNumber and e2.rn = e.rn+1
   where e.rn = (e.rn / 2) * 2
)
select * from gaps order by rn

這是執行計劃中唯一似乎不同的部分：

使用 ORDER BY（25 秒）：

無順序（3 秒）：

由於您使用的是 2012 版本，因此這裡有另一種使用新LAG()功能來解決此間隙和孤島問題的方法：

; WITH seq AS
 ( SELECT 
       SiteIDNumber,
       SequenceNumber,
       LagSequenceNumber = LAG(SequenceNumber) OVER (PARTITION BY SiteIDNumber 
                                                     ORDER BY SequenceNumber),
   FROM 
       TestPlay 
 )
SELECT 
   SiteIDNumber,
   StartSeq = LagSequenceNumber + 1,
   EndSeq = SequenceNumber - 1
FROM
   seq
WHERE
   LagSequenceNumber + 1 < SequenceNumber 
ORDER BY 
   SiteIDNumber,
   SequenceNumber ;

合併連接就像拉鍊一樣工作——如果您不關心順序，SQL Server 知道它可以按照它想要的任何方式對輸入進行排序，而不必擔心重新排序任何內容。當您添加 order by 時，在這種情況下合併連接不再是最佳選擇，因為按照表達式定義的順序對第一個 CTE進行兩次物化和排序顯然比看起來更昂貴。ROW_NUMBER()

一種解決方法，在周五啤酒點即興思考：

;WITH ...
(
)
SELECT * INTO #x FROM gaps;

SELECT * FROM #x ORDER BY rn;

但是，我敢打賭，如果您四處尋找一個名叫 Itzik Ben-Gan 的人，您會發現更有效的方法來解決涉及間隙和孤島的問題（或者建議不要嘗試按照您的排序方式進行排序）。

引用自：https://dba.stackexchange.com/questions/73547