史丹利好熱

第三篇，取資料序號

• 取總筆數的效能(COUNT(1) OVER() VS CTE)
• 排序條件選擇
• 取資料序號(ROW_NUMBER() OVER(ORDER BY NOTHING))
• Offset Fetch Vs Row_number(總筆數及資料序號)

 

這次的實驗中，我們還是用AdventureWorks2014資料庫中的PRODUCTION.TRANSACTIONHISTORY資料表作DB分頁測試，假設需求想要多個ROW_NO欄位。

 

實驗

測試時順便要比較加上row_number函式會增加多少資源:

• 第1次語法是沒有加上row_numer的查詢
• 第2次則是加上row_number，但order by nothing(放null放1都行)
• 第3次的語法則是加上row_number，但order by 的條件和原本分頁的條件相同。

查詢1: 

SET STATISTICS IO ON 
SET STATISTICS TIME ON 
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 10;

SELECT *
FROM	PRODUCTION.TRANSACTIONHISTORY
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY

查詢2:

DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 10;

SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ROW_NO
FROM	PRODUCTION.TRANSACTIONHISTORY
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY

查詢3:

DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 10;

SELECT *,ROW_NUMBER() OVER(ORDER BY ProductID) AS ROW_NO
FROM	PRODUCTION.TRANSACTIONHISTORY
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY

 

執行結果:  查第10頁，每頁10筆，因此資料序號是91-100。

 

CPU、執行時間及邏輯讀取:

執行方式	CPU Time	經過時間	邏輯讀取
沒有Row_Number()	0ms	49ms	317
Row_Number(order by Nothing)	0ms	49ms	317
Row_Number(order by ProductID)	0ms	50ms	317

 

執行計畫:

第1次查詢沒有Row_number的查詢

 

第2次與第3次查詢的執行計畫相同，雖然多了row_number()的查詢，只多了一點點的成本

 

成本分析:

執行方式	子樹成本
沒有Row_Number()	0.316297
Row_Number(order by Nothing)	0.316307
Row_Number(order by ProductID)	0.316307

 

 

---

小結

• I/O相同，多了ROW_NUMBER()後，執行計畫也多2個步驟，成本只多了一些些，經過時間也相同。
• WEB程式端也可以使用每頁筆數和頁數推算。
• 下一篇測試直接用ROW_NUMBER()作分頁處理與OFFSET FETCH作分頁處理的比較。

 

 

---

參考

Stackoverflow ROW_NUMBER Without ORDER BY

[SQL Server][Pagination]DB端的分頁處理(一)取總筆數的效能(COUNT(1) OVER() VS CTE)

[SQL Server][Pagination]DB端的分頁處理(二)排序條件選擇