測試環境
- Windows Server 2003 / Intel Core2 6300 / 4 GB RAM
- SQL Server 2005 Enterprise (9.00.3054)
- 測試表
tb_group：300 萬列，PK 為 ID (CLUSTERED)，非索引欄位 country
三種分頁實作
1. TOP N 雙重巢狀 (傳統相容)
-- 第 1 頁
SELECT TOP @PageSize * FROM tb_group ORDER BY Country ASC;
-- 第 N 頁 (N>1)
SELECT TOP @PageSize * FROM (
SELECT TOP @PageSize*@PageIndex * FROM tb_group ORDER BY Country ASC
) t ORDER BY Country DESC;
2. 暫存表 + IDENTITY 序號
CREATE TABLE #Pager (ID INT, PagerID INT IDENTITY(1,1));
INSERT INTO #Pager (ID)
SELECT TOP @PageSize*@PageIndex ID FROM tb_group ORDER BY Country ASC;
SELECT g.* FROM tb_group g
JOIN #Pager p ON g.ID = p.ID
WHERE p.PagerID BETWEEN @PageSize*(@PageIndex-1)+1 AND @PageSize*@PageIndex;
DROP TABLE #Pager;
3. ROW_NUMBER() 視窗函數 (SQL 2005+)
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS RowNo
FROM tb_group
) t
WHERE RowNo BETWEEN @PageSize*(@PageIndex-1)+1 AND @PageSize*@PageIndex;
測試結果 (毫秒)
| 測試案例 | TOP N (無索引) | TOP N (有索引) | 暫存表 (無索引) | 暫存表 (有索引) | ROW_NUMBER (無索引) | ROW_NUMBER (有索引) |
|---|
| 第 10 頁 (30/頁) | 65,220 | 280 | 27,407 | 2,627 | 9,107 | 250 |
| 第 1,000 頁 | 67,000 | 640 | 28,170 | 2,873 | 9,873 | 263 |
| 第 10,000 頁 | 67,000 | 517 | 31,500 | 5,783 | 12,230 | 517 |
| 第 100,000 頁 (20/頁) | 75,000 | 2,640 | 45,937 | 15,750 | 19,640 | 1,983 |
深度分析
| 方法 | 優點 | 缺點 | 適用場景 |
|---|
| TOP N | 無版本限制、索引排序極快 | 無索引排序極慢；末頁邏輯複雜；重複排序值需二次排序 | SQL 2000、有索引排序、中小資料量 |
| 暫存表 | 邏輯直觀、支援複雜排序 | 寫入 tempdb 產生 I/O 壓力；大資料量嚴重拖慢 | 無索引排序、一次性報表、可接受秒級 |
| ROW_NUMBER | 語法簡潔、單次掃描、有無索引表現穩定、支援 PARTITION | 需 SQL 2005+；極大資料量無索引仍較慢 | 首選方案 (SQL 2005+) |
關鍵發現
- 索引是分頁效能的決定性因素：有索引排序時，三種方法差距縮小至毫秒級；無索引時差距達數十倍。
- ROW_NUMBER 穩定性最佳：隨頁碼增長，時間增長呈線性，無劇烈跳動。
- 深分頁 (10萬頁) 仍有效能挑戰：即使 ROW_NUMBER，無索引需 19 秒。解決方案：
• 強制建立排序欄位索引
• 使用「游標式分頁」：WHERE ID > @LastMaxID ORDER BY ID OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
• 應用層限制最大頁碼或改用無限捲動
遺留問題：ROW_NUMBER 大小寫 Bug？
測試發現小寫 row_number() 報錯，大寫 ROW_NUMBER() 正常。經驗證為查詢編輯器字型/編碼顯示問題，實際執行不分大小寫。建議統一大寫關鍵字。
最佳實務建議
-- 通用分頁存儲過程範本 (SQL 2012+ OFFSET/FETCH 更簡潔)
CREATE PROC usp_Pager
@TableName SYSNAME,
@OrderBy SYSNAME,
@PageSize INT = 20,
@PageIndex INT = 1,
@Where NVARCHAR(MAX) = ''
AS
BEGIN
DECLARE @SQL NVARCHAR(MAX) = N'
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderBy) + ') AS RN
FROM ' + QUOTENAME(@TableName) +
CASE WHEN @Where <> '' THEN ' WHERE ' + @Where ELSE '' END + '
) t WHERE RN BETWEEN ' + CAST((@PageIndex-1)*@PageSize+1 AS VARCHAR) +
' AND ' + CAST(@PageIndex*@PageSize AS VARCHAR);
EXEC sp_executesql @SQL;
END;
