[SQL] 各群組前 10 筆資料

在社群內討論到使用 Row_Number() 取出群組前 10 筆資料很耗時，原以為是 master、detail 架構下，可以用 [SQL] APPLY 來改善，EX：連結文章內的客戶前 2 筆交易紀錄，沒想到是真的只有單一 Table 要找出各群組的前十筆資料，模擬該情境，重點放在建立符合 POC 原則的 Index 給 Row_Number() 使用

原寫法

SELECT *
FROM
  (
    SELECT * , 
      ROW_NUMBER() OVER (PARTITION BY 群組 ORDER BY 日期 DESC) AS RowNO
    FROM RowPerformance
  ) AS T
WHERE T.RowNO = 10

改善寫法

建立 IX_Row Index，讓 Row_Number() 可以透過它跑 Index Scan 來完成資料編號，找出各群組前 10 筆的 PK 後，再和自身 join 一次來找出所需欄位資訊

-- 配合 Row_Number 建立 Index
CREATE INDEX IX_Row ON RowPerformance (群組 , 日期 DESC)

SELECT T1.*
FROM RowPerformance AS T1
  JOIN
    (
      SELECT PKID ,
        ROW_NUMBER() OVER (PARTITION BY 群組 ORDER BY 日期 DESC) AS RowNO
      FROM RowPerformance
    ) AS T2 ON T1.PKID = T2.PKID
WHERE T2.RowNO = 10

語法效能差異

因為 Row_Number() 會掃整個 Table，所以在 Logical Read 上基本上是沒有差異，在 CPU Time 上比較明顯

	原語法	改善語法
CPU Time	5,233 ms	829 ms
經過時間	3,536 ms	1,160 ms

執行計畫上少掉 sort 成本，且也不需要平行處理