SQL Server 分頁查詢三大方案效能實測：從游標到 MAX/TOP 最佳化

分頁查詢挑戰

Web 應用分頁瀏覽是常見需求。傳統 ADO Recordset 分頁依賴游標，佔用記憶體大、鎖定資源久，不適合大量資料。現代做法為「只取當頁資料塊」，避免全量載入。

方案一：資料表變數 + ROWCOUNT (俄羅斯式改良版)

CREATE PROCEDURE pagination1
    @pagesize INT, @pageindex INT
AS
SET NOCOUNT ON
DECLARE @indextable TABLE (id INT IDENTITY(1,1), nid INT)
DECLARE @PageLowerBound INT, @PageUpperBound INT
SET @PageLowerBound = (@pageindex - 1) * @pagesize
SET @PageUpperBound = @PageLowerBound + @pagesize
SET ROWCOUNT @PageUpperBound
INSERT INTO @indextable (nid)
SELECT gid FROM TGongwen
WHERE fariqi > DATEADD(day, -365, GETDATE())
ORDER BY fariqi DESC
SELECT O.gid, O.mid, O.title, O.fadanwei, O.fariqi
FROM TGongwen O, @indextable t
WHERE O.gid = t.nid AND t.id > @PageLowerBound AND t.id <= @PageUpperBound
ORDER BY t.id
SET NOCOUNT OFF

使用表變數優於暫存表，但隨頁碼增大效能下降明顯。

方案二：NOT IN + TOP 子查詢

CREATE PROCEDURE pagination2
    @SQL NVARCHAR(4000), @Page INT, @RecsPerPage INT,
    @ID VARCHAR(255), @Sort VARCHAR(255)
AS
DECLARE @Str NVARCHAR(4000)
SET @Str = 'SELECT TOP ' + CAST(@RecsPerPage AS VARCHAR) + ' * FROM (' + @SQL + ') T WHERE T.' + @ID +
    ' NOT IN (SELECT TOP ' + CAST((@RecsPerPage * (@Page - 1)) AS VARCHAR) + ' ' + @ID +
    ' FROM (' + @SQL + ') T9 ORDER BY ' + @Sort + ') ORDER BY ' + @Sort
EXEC sp_ExecuteSql @Str

核心邏輯：SELECT TOP 頁大小 * FROM Table WHERE ID NOT IN (SELECT TOP 頁大小*頁數 ID FROM Table ORDER BY ID) ORDER BY ID。缺點是 NOT IN 效能差，改用 NOT EXISTS 也無顯著改善。

方案三：MAX/MIN 邊界值 + TOP (最佳解)

利用不重複排序鍵 (如主鍵) 的最大/最小值作為分頁分水嶺，配合 > 或 < 運算子，符合 SARG 形式，可用索引求值。

SELECT TOP @PageSize *
FROM Table1
WHERE ID > (
    SELECT MAX(ID) FROM (
        SELECT TOP ((@PageIndex - 1) * @PageSize) ID
        FROM Table1 ORDER BY ID
    ) AS T
)
ORDER BY ID

完整儲存程序 pagination3 (支援總筆數、雙向排序、動態條件)

CREATE PROCEDURE pagination3
    @tblName VARCHAR(255), @strGetFields VARCHAR(1000) = '*',
    @fldName VARCHAR(255) = '', @PageSize INT = 10, @PageIndex INT = 1,
    @doCount BIT = 0, @OrderType BIT = 0, @strWhere VARCHAR(1500) = ''
AS
DECLARE @strSQL VARCHAR(5000), @strTmp VARCHAR(110), @strOrder VARCHAR(400)

IF @doCount != 0 BEGIN
    SET @strSQL = 'SELECT COUNT(*) AS Total FROM [' + @tblName + ']' +
        CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END
END ELSE BEGIN
    IF @OrderType != 0 BEGIN
        SET @strTmp = '<(SELECT MIN'
        SET @strOrder = ' ORDER BY [' + @fldName + '] DESC'
    END ELSE BEGIN
        SET @strTmp = '>(SELECT MAX'
        SET @strOrder = ' ORDER BY [' + @fldName + '] ASC'
    END

    IF @PageIndex = 1 BEGIN
        SET @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @strGetFields +
            ' FROM [' + @tblName + ']' +
            CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END + @strOrder
    END ELSE BEGIN
        SET @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @strGetFields +
            ' FROM [' + @tblName + '] WHERE [' + @fldName + '] ' + @strTmp + '(' +
            'SELECT TOP ' + STR((@PageIndex - 1) * @PageSize) + ' [' + @fldName + '] FROM [' + @tblName + ']' +
            CASE WHEN @strWhere != '' THEN ' WHERE ' + @strWhere ELSE '' END + @strOrder + ') AS T)' + @strOrder
    END
END
EXEC (@strSQL)

實測效能比較 (1000 萬筆資料，單位：毫秒)

• 方案一：100 頁後明顯變慢
• 方案二：1 萬頁後效能下降
• 方案三：全範圍穩定，後勁十足

結論：大量資料分頁首選方案三，配合主鍵索引與 SARG 條件，效能最穩健。方案二次之，方案一僅適合小資料量。