史丹利好熱

設計分頁處理時，除了是否要用DB來處理分頁的考慮，也發現了幾個自己要再考慮的點，分成幾篇筆記。

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

 

SQL Server 2012推出ORDER BY子句的延伸，一種新的處理分頁功能(OFFSET FETCH)，從DB源頭端就只回傳特定的分頁資料，而且語法上也變得直覺許多。

分頁時，如果想要取總筆數，也許我們會在欄位清單中加上COUNT(1) OVER()來產生總筆數欄位，測試時，意外發現用COUNT(1) OVER()會造成效能損傷，來筆記並找出解決方式。

 

這次我們用AdventureWorks2014資料庫中的PRODUCTION.TRANSACTIONHISTORY資料表作DB分頁測試，排序條件是ProductID，並額外多取回總筆數。

ProductID是非叢集索引欄位(若是叢集索引更佳)

 

每頁10筆，取第10頁的10筆，然後回傳總筆數。就像下圖，總筆數TOTAL_COUNT = 113,443  

 

---

對照組(COUNT(1) OVER())

直接加一個欄位count(1) over() as TOTAL_COUNT

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

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

資料表 'Worktable'。掃描計數 3，邏輯讀取 286662，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
資料表 'TransactionHistory'。掃描計數 1，邏輯讀取 797，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。
資料表 'Worktable'。掃描計數 0，邏輯讀取 0，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

CPU 時間 = 390 ms，經過時間 = 333 ms。

 

---

實驗組(CTE)

透過兩個CTE的組合，第一個CTE是結果集，第二個CTE再COUNT第一個CTE的筆數，最後再用兩個CTE Join，同時取出結果集及總筆數。

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

WITH CTE_DATA
AS
(
SELECT   *
	FROM PRODUCTION.TRANSACTIONHISTORY
),CTE_COUNT AS (SELECT COUNT(*) AS TOTAL_COUNT FROM CTE_DATA)
SELECT TOTAL_COUNT,CTE_DATA.*
FROM CTE_DATA ,CTE_COUNT
ORDER BY ProductID
OFFSET (@PageNumber - 1) * (@PageSize) ROWS
FETCH NEXT @PageSize ROWS ONLY

 

 

資料表 'TransactionHistory'。掃描計數 2，邏輯讀取 520，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

CPU 時間 = 0 ms，經過時間 = 72 ms。

 

---

整理測試結果

取總筆數的方式	CPU Time	經過時間	邏輯讀取
COUNT(1) OVER()	390ms	333ms	287459
CTE	0ms	72ms	520

呼~驚訝，11萬筆就能差這麼多。

 

執行計畫:

 

 

---

小結

• 避免在回傳結果較大的結果集中使用COUNT(1) OVER
• 排序條件選擇的欄位很重要，珍貴的叢集索引設計很兩難()。
• 下一篇來比較排序條件(叢集索引 vs 非叢集索引 vs 非索引欄位)

 

---

參考

OFFSET FETCH 子句 (SQL Server Compact)

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