ASP.NET 資料分頁效能大比拼：DataReader 游標法 vs 三種傳統存儲程序分頁

測試環境建立

建立資料表 R_Student 並插入 100 萬筆測試資料：

CREATE TABLE R_Student (
    Id NVARCHAR(64) PRIMARY KEY,
    Class NVARCHAR(64) NOT NULL,
    Age TINYINT NOT NULL,
    Sex TINYINT NOT NULL
);
-- 插入 1,000,000 筆
DECLARE @i INT = 0;
WHILE @i < 1000000 BEGIN
    INSERT INTO R_Student VALUES ('Name'+STR(@i), 'Class'+STR(@i), @i%100, @i%2);
    SET @i += 1;
END

四種分頁方案實作

1. DataReader 游標前移法 (本文主推)

利用 SqlDataReader 僅前移游標特性，先 Read() 跳過前 N 筆（不取資料），再讀取目標頁筆數。優點：不載入不需資料，記憶體佔用極低。

public DataSet RangeQuery(string sql, long first, long last) {
    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand(sql, conn)) {
        conn.Open();
        using (var reader = cmd.ExecuteReader()) {
            // 跳過前 first 筆
            for (long i = 0; i < first && reader.Read(); i++) { }
            // 讀取目標頁
            var ds = new DataSet();
            var dt = new DataTable();
            ds.Tables.Add(dt);
            dt.Load(reader); // Load 會讀取剩餘所有，需配合 last 限制
            return ds;
        }
    }
}

關鍵優化：搭配 SELECT TOP (pageSize) ... WHERE ... ORDER BY Id 並在應用層控制 first、last，或直接在 SQL 加 OFFSET/FETCH (SQL Server 2012+)。

2. 二次 TOP 法 (效能最差)

-- 第 1 頁
SELECT TOP @pageSize * FROM R_Student ORDER BY Id;
-- 第 N 頁 (N>1)
SELECT TOP @pageSize * FROM R_Student 
WHERE Id NOT IN (SELECT TOP @pageSize*(@page-1) Id FROM R_Student ORDER BY Id)
ORDER BY Id;

缺點：NOT IN 子查詢效能隨頁碼遞增急劇惡化，且需主鍵唯一。

3. ROW_NUMBER() 窗口函數法 (通用、效能佳)

WITH Numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNum FROM R_Student
)
SELECT * FROM Numbered 
WHERE RowNum BETWEEN (@page-1)*@pageSize+1 AND @page*@pageSize;

優點：不受排序欄位、重複鍵限制，SQL Server 2005+ 支援，執行計畫穩定。

4. 通用分頁存儲程序 (MAX 主鍵法)

利用主鍵最大值定位分頁起點：

SELECT TOP @pageSize @select FROM @from 
WHERE @key > (SELECT MAX(@key) FROM (SELECT TOP @pageSize*(@page-1) @key FROM @from ORDER BY @key) T)
ORDER BY @key;

缺點：需單一主鍵、主鍵需可排序、不支援複雜排序。

效能測試結果 (頁面大小=10, 100萬筆, 單位:毫秒)

結論與建議

• 深分頁 (大頁碼)：四種方法耗時均顯著上升，根本原因在於資料庫必須掃描大量前置資料列。根治方案：禁止深分頁（UI 限制最大頁碼）、改用 Keyset Pagination (Seek 法)：WHERE Id > @lastSeenId ORDER BY Id LIMIT @pageSize，效能與頁碼無關。
• 淺分頁 (前幾頁)：ROW_NUMBER、通用分頁、DataReader 表現接近，DataReader 略勝一籌且不佔用資料庫 TempDB。
• 現代最佳實務：SQL Server 2012+ 直接使用 OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY，語法標準、最佳化器支援良好。
• DataReader 適用場景：舊版 SQL Server、需串流處理超大結果集、記憶體受限環境。