DataReader 分頁與傳統分頁存儲程序效能比較 (3/3)

通用分頁存儲程序

來源：Tracy-Chuang 部落格，簡化版：

CREATE PROCEDURE [dbo].[spCommonPageData]
    @Select NVARCHAR(500),   -- 查詢欄位
    @From NVARCHAR(200),     -- FROM 子句
    @Where NVARCHAR(500) = NULL,
    @OrderBy NVARCHAR(100) = NULL,
    @Key NVARCHAR(50),       -- 分頁鍵值 (單一主鍵)
    @Page INT,               -- 目前頁碼 (1-based)
    @PageSize INT            -- 頁面大小
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(1000);

    -- 使用 MAX(Key) 子查詢定位分頁起始點
    SET @Sql = 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + ' ' + @Select +
               ' FROM ' + @From + ' WHERE ' +
               CASE WHEN @Where IS NULL OR @Where = '' THEN '' ELSE @Where + ' AND ' END +
               @Key + ' > (SELECT ISNULL(MAX(' + @Key + '), 0) AS MaxID FROM ' +
               '(SELECT TOP ' + CAST(@PageSize * (@Page - 1) AS VARCHAR(10)) + ' ' + @Key +
               ' FROM ' + @From +
               CASE WHEN @Where IS NULL OR @Where = '' THEN '' ELSE ' WHERE ' + @Where END +
               ' ORDER BY ' + @Key + ') AS T) ' +
               ' ORDER BY ' + @Key +
               CASE WHEN @OrderBy IS NULL OR @OrderBy = '' THEN '' ELSE ',' + @OrderBy END;

    EXEC(@Sql);
END

已知限制

• 不支援複合主鍵。
• 重複鍵值處理可能有誤。
• 必須指定 ORDER BY @Key，否則結果不定。

效能測試結果 (100 萬筆、PageSize=10、單位：毫秒)

結論

DataReader 逐筆略過 (Seek) 方式在深分頁 (第 10 萬頁) 仍最快，因為它只移動資料指標不傳輸資料。但缺點是需維持連線開啟，不適合 Web 無狀態架構。實務上結合 ROW_NUMBER (SQL 2005+) 或 OFFSET/FETCH (SQL 2012+) 兼顧效能與架構靈活性。