SQL Server 分頁查詢性能深度對比：TOP N、臨時表、CTE 三大方案實測分析

分頁查詢三大核心算法

1. TOP NOT IN 方案：

   SELECT TOP (@PageSize) * FROM TableName
   WHERE ID NOT IN (
       SELECT TOP ((@PageIndex-1)*@PageSize) ID FROM TableName ORDER BY ...
   )
   ORDER BY ...

2. 臨時表 + IDENTITY 方案：

   DECLARE @Temp TABLE (RowID INT IDENTITY(1,1), ID INT, ...);
   INSERT INTO @Temp (ID, ...) SELECT ID, ... FROM TableName ORDER BY ...;
   SELECT * FROM TableName t JOIN @Temp tmp ON t.ID = tmp.ID
   WHERE tmp.RowID BETWEEN @Lower AND @Upper;

3. CTE + ROW_NUMBER() 方案 (SQL 2005+)：

   WITH CTE AS (
       SELECT ROW_NUMBER() OVER (ORDER BY ...) AS RowNum, *
       FROM TableName
   )
   SELECT * FROM CTE WHERE RowNum BETWEEN @Lower AND @Upper;

實測環境與數據規模

• Windows Server 2003 + SQL Server 2005
• 測試表：2,567,245 行，無 WHERE 條件，純分頁
• 頁大小：50 行，頁碼作為變量傳入
• 測試頁碼：1, 3, 10, 32, 100, 316, 1000, 3162, 10000, 31623, 100000（對數級遞增）
• 指標：執行時間（毫秒），含緩存/無緩存對比

關鍵性能數據摘錄（單位：毫秒）

深度結論與選型建議

• TOP N 方案：前 10-32 頁極快，但隨頁碼增長性能指數級惡化（NOT IN 子查詢掃描開銷），深分頁不可用。
• 臨時表方案：性能最穩定，冷/熱緩存差異大（首次寫入 tempdb 開銷），但會導致 tempdb 日誌膨脹、I/O 壓力大，高併發下系統表爭用風險高。
• CTE 方案：性能穩定介於兩者間，無 tempdb 寫入開銷，但 ROW_NUMBER() 全量排序隨數據量增長開銷固定。
• CTE 優化版：CTE 僅選取主鍵+排序列，再回表 JOIN，顯著降低排序內存壓力，性能接近臨時表熱緩存，且無 tempdb 負擔，綜合推薦。
• 生產現狀：現用存儲過程（類似 CTE 優化）前期表現良好，深分頁性能下降明顯，建議引入「延遲關聯」或「Keyset 分頁」（基於游標/上一頁最大 ID）突破深分頁瓶頸。

圖表分析

圖表直觀展示：TOP N 前期領先後崩潰；臨時表冷啟動慢穩定；CTE 優化版曲線最平滑，適合未知分頁深度的通用場景。