史丹利好熱

 

比方每頁10筆，要查第100頁的資料，資料序號為991-1000，總筆數為113,001。

我們繼續用AdventureWorks2014資料庫的PRODUCTION.TRANSACTIONHISTORY進行查詢，決定分頁結果是否一致的排序條件依照ProductID(非叢集索引)，分別查詢第1頁、10頁及第100頁，每頁50筆。

 

---

SQL2005-SQL2008寫法

第一次先查詢總筆數，第二次再用子查詢查出資料並且編號，再挑出符合分頁條件範圍的資料。

CREATE PROCEDURE USP_Pagination_2008_RowNumber
    @PageSize INT,   
    @PageNumber INT   
AS 
DECLARE @TotalCount INT = 
( SELECT
		COUNT(*)
	FROM PRODUCTION.TRANSACTIONHISTORY)

SELECT
	*
FROM (SELECT
		ROW_NUMBER() OVER (ORDER BY ProductID) AS ROW_NO
		,@TotalCount AS TOTAL_ROW_COUNT
		,*
	FROM PRODUCTION.TRANSACTIONHISTORY) DATA
WHERE DATA.ROW_NO
BETWEEN (@PageNumber - 1) * (@PageSize) + 1 
    AND (@PageNumber * @PageSize)
ORDER BY ProductID

 

---

SQL 2012寫法(CTE + Offset Fetch)

用上CTE + Offset Fetch，同時用ROW_NUMBER，但設定相同的值條件來不執行排序的小技巧(Order BY Nothing)

CREATE PROCEDURE USP_Pagination_2012_OffsetFetch
    @PageSize INT,   
    @PageNumber INT   
AS 

WITH CTE_DATA
AS
(SELECT
		ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ROW_NO
		,*
	FROM PRODUCTION.TRANSACTIONHISTORY),
CTE_COUNT
AS
(SELECT COUNT(*) AS TOTAL_ROW_COUNT FROM CTE_DATA)

SELECT
	A.ROW_NO,C.TOTAL_ROW_COUNT
	,A.*
FROM	CTE_DATA A ,CTE_COUNT C
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY

 

SQL 資料分頁(pagination)選取第1頁的比較(開啟包括實際執行計畫)

SET STATISTICS IO ON 
SET STATISTICS TIME ON 

EXEC USP_Pagination_2008_RowNumber @PageSize=50,@PageNumber=1
EXEC USP_Pagination_2012_OffsetFetch @PageSize=50,@PageNumber=1

分頁處理方式	CPU Time(ms)	經過時間(ms)	邏輯讀取	執行計畫成本占比
ROW_NUMBER()	0	169	367	57%
Offset Fetch	0	118	367	43%

 

SQL 資料分頁(pagination)選取第10頁的比較(開啟包括實際執行計畫)

EXEC USP_Pagination_2008_RowNumber @PageSize=50,@PageNumber=10
EXEC USP_Pagination_2012_OffsetFetch @PageSize=50,@PageNumber=10

分頁處理方式	CPU Time(ms)	經過時間(ms)	邏輯讀取	執行計畫成本占比
ROW_NUMBER()	0	198	1746	57%
Offset Fetch	16	132	1745	43%

 

SQL 資料分頁(pagination)選取第100頁的比較(開啟包括實際執行計畫)

EXEC USP_Pagination_2008_RowNumber @PageSize=50,@PageNumber=100
EXEC USP_Pagination_2012_OffsetFetch @PageSize=50,@PageNumber=100

分頁處理方式	CPU Time(ms)	經過時間(ms)	邏輯讀取	執行計畫成本佔比
ROW_NUMBER()	31	175	15,536	57%
Offset Fetch	15	137	15,535	43%

 

Offset Fetch都相較ROW_NUMBER有進步。

 

---

再測試一次

關閉<包括實際執行計畫>，執行查詢第1頁-3頁，紀錄伺服器平均的等候回應時間。

使用ROW_NUMBER(USP_Pagination_2008_RowNumber)

使用SQL 2012推出的新功能: Offset Fetch(USP_Pagination_2012_OffsetFetch)

 

 

---

小結

• 新的DB分頁處理語法Offset Fetch可以獲得30%的執行時間獲利。
• 關閉<包括實際執行計畫>後，平均伺服器回應時間從12ms到10ms，都很快。
• 要不要分頁
• 排序。

 

 

---

參考

OFFSET FETCH 子句 (SQL Server Compact)

https://technet.microsoft.com/zh-tw/library/gg699618(v=sql.110).aspx

 

Comparing performance for different SQL Server paging methods

https://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/