[SQL Server][Pagination]DB端的分頁處理(四)Offset Fetch(2012) VS Row_number(2008)

同事專案的SQL版本是SQL 2016,可以使用SQL 2012 在ORDER BY的延伸功能Offset Fetch,因為某些因素,暫時還是用舊的方式分頁,來幫同事試試新的做法,同時也用兩個小技巧,回傳總筆數及資料序號再來比較。

 

 

比方每頁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/