SQL Server 大量數據分頁存儲過程效能測試：五種算法實測對比

針對 200 萬筆測試數據，實測五種分頁存儲過程寫法在不同頁碼（第 2、1000、10000、100000、199999 頁，每頁 10 筆）的執行耗時，揭示最佳實踐。

測試環境建立

CREATE DATABASE Data_Test; GO
USE Data_Test; GO
CREATE TABLE Tb_TestTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    UserName NVARCHAR(20) NOT NULL,
    UserPWD NVARCHAR(20) NOT NULL,
    UserEmail NVARCHAR(40) NULL
); GO

-- 插入 200 萬筆測試數據
SET IDENTITY_INSERT Tb_TestTable ON;
DECLARE @Cnt INT = 1;
WHILE @Cnt <= 2000000
BEGIN
    INSERT INTO Tb_TestTable (ID, UserName, UserPWD, UserEmail)
    VALUES (@Cnt, 'admin', 'admin888', 'lli0077@yahoo.com.cn');
    SET @Cnt += 1;
END
SET IDENTITY_INSERT Tb_TestTable OFF;

五種分頁算法實作

1. NOT IN 子查詢法（傳統寫法，深分頁極慢）

CREATE PROC Proc_Paged_NotIn @PageIndex INT, @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @StartTime DATETIME = GETDATE();
    DECLARE @SQL NVARCHAR(500) = 
        'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM Tb_TestTable ' +
        'WHERE ID NOT IN (SELECT TOP ' + CAST(@PageSize * @PageIndex AS VARCHAR) + ' ID FROM Tb_TestTable ORDER BY ID) ' +
        'ORDER BY ID';
    EXEC(@SQL);
    SELECT DATEDIFF(MS, @StartTime, GETDATE()) AS ElapsedMs;
    SET NOCOUNT OFF;
END

2. MAX(ID) 子查詢法（改善 NOT IN，但深分頁仍慢）

CREATE PROC Proc_Paged_MaxId @PageIndex INT, @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @StartTime DATETIME = GETDATE();
    DECLARE @SQL NVARCHAR(500) = 
        'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM Tb_TestTable ' +
        'WHERE ID > (SELECT MAX(ID) FROM (SELECT TOP ' + CAST(@PageSize * @PageIndex AS VARCHAR) + ' ID FROM Tb_TestTable ORDER BY ID) AS T) ' +
        'ORDER BY ID';
    EXEC(@SQL);
    SELECT DATEDIFF(MS, @StartTime, GETDATE()) AS ElapsedMs;
    SET NOCOUNT OFF;
END

3. 變數游標法（單掃描，中分頁尚可，深分頁仍需全掃）

CREATE PROC Proc_Paged_VarCursor @PageIndex INT, @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Cnt INT = 0, @MaxId INT = 0, @StartTime DATETIME = GETDATE();
    SELECT @Cnt = @Cnt + 1, @MaxId = CASE WHEN @Cnt <= @PageSize * @PageIndex THEN ID ELSE @MaxId END
    FROM Tb_TestTable ORDER BY ID;
    DECLARE @SQL NVARCHAR(500) = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM Tb_TestTable WHERE ID > ' + CAST(@MaxId AS VARCHAR);
    EXEC(@SQL);
    SELECT DATEDIFF(MS, @StartTime, GETDATE()) AS ElapsedMs;
    SET NOCOUNT OFF;
END

4. ROW_NUMBER() 窗口函數法（SQL 2005+ 標準寫法，性能穩定）

CREATE PROC Proc_Paged_RowNumber @PageIndex INT, @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @StartTime DATETIME = GETDATE();
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM Tb_TestTable
    ) AS T
    WHERE RowNum > @PageSize * @PageIndex AND RowNum <= @PageSize * (@PageIndex + 1);
    SELECT DATEDIFF(MS, @StartTime, GETDATE()) AS ElapsedMs;
    SET NOCOUNT OFF;
END

5. CTE + ROW_NUMBER 分頁號法（邏輯清晰，性能同方案 4）

CREATE PROC Proc_Paged_CTE @PageIndex INT, @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @StartTime DATETIME = GETDATE();
    DECLARE @SQL NVARCHAR(400) = 
        'WITH CTE AS (SELECT CEILING(ROW_NUMBER() OVER (ORDER BY ID) / ' + CAST(@PageSize AS VARCHAR) + ') AS PageNum, * FROM Tb_TestTable) ' +
        'SELECT * FROM CTE WHERE PageNum = ' + CAST(@PageIndex AS VARCHAR);
    EXEC sp_executesql @SQL;
    SELECT DATEDIFF(MS, @StartTime, GETDATE()) AS ElapsedMs;
    SET NOCOUNT OFF;
END

測試結果摘要 (單位：毫秒，每頁測 5 次取平均)

結論與建議

• ROW_NUMBER / CTE 為首選： SQL Server 2005 以上版本，性能隨頁碼增長極其平緩，穩定在 50-100ms 內。
• 避免 NOT IN / MAX(ID)： 深分頁性能隨資料量線性惡化，百萬級數據不可用。
• SQL 2012+ 新語法： OFFSET @PageSize * @PageIndex ROWS FETCH NEXT @PageSize ROWS ONLY，語法簡潔、優化器支援最佳，強烈推薦。
• 索引關鍵： 分頁欄位（如 ID）必須有聚集索引或覆蓋索引，否則所有方法皆會全表掃描。