萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

毫秒級百萬資料分頁預存程序:SQL Server 2005+ ROW_NUMBER() 兩種演算法實測

建立百萬筆測試表 (33 秒),編寫通用分頁預存程序 dbo.im531_Page,支援動態資料表、欄位、條件、排序,兩種演算法可切換:BETWEEN / TOP。

預存程序核心碼

CREATE PROCEDURE dbo.im531_Page
    @TabeName NVARCHAR(50),
    @Fields NVARCHAR(1024),
    @SearchWhere NVARCHAR(1024),
    @OrderFields NVARCHAR(1024),
    @pageNumber INT,   -- 每頁筆數
    @page INT          -- 頁碼 (1-based)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sqlType TINYINT = 0; -- 0=BETWEEN, 1=TOP
    DECLARE @sql NVARCHAR(MAX);
    IF @SearchWhere <> '' SET @SearchWhere = ' WHERE ' + @SearchWhere;
    IF @page < 2 SET @page = 1;
    IF @page = 1
        SET @sql = 'SELECT TOP ' + CAST(@pageNumber AS NVARCHAR(20)) + ' ' + @Fields
                 + ' FROM [' + @TabeName + '] [a] WITH(NOLOCK) ' + @SearchWhere
                 + ' ORDER BY ' + @OrderFields;
    ELSE
    BEGIN
        -- CTE 產生行號
        SET @sql = ';WITH [Page_____Table] AS ('
                 + 'SELECT ROW_NUMBER() OVER(ORDER BY ' + @OrderFields + ') AS [RowNow], ' + @Fields
                 + ' FROM [' + @TabeName + '] [a] WITH(NOLOCK) ' + @SearchWhere + ')';
        IF @sqlType = 0 -- BETWEEN
            SET @sql = @sql + ' SELECT * FROM [Page_____Table] WITH(NOLOCK)'
                          + ' WHERE [RowNow] BETWEEN ' + CAST((@page-1)*@pageNumber+1 AS NVARCHAR(20))
                          + ' AND ' + CAST(@page*@pageNumber AS NVARCHAR(20))
                          + ' ORDER BY [RowNow]';
        ELSE -- TOP
            SET @sql = @sql + ' SELECT TOP ' + CAST(@pageNumber AS NVARCHAR(20)) + ' *'
                          + ' FROM [Page_____Table] WITH(NOLOCK)'
                          + ' WHERE [RowNow] > ' + CAST((@page-1)*@pageNumber AS NVARCHAR(20))
                          + ' ORDER BY [RowNow]';
    END
    EXEC (@sql);
END

實測結果 (百萬筆、每頁 20 筆、隨機頁碼)

兩種演算法皆穩定在 **毫秒級** (1–10 ms),WITH(NOLOCK) 避免鎖定、ROW_NUMBER() 単次掃描、CTE 物化行號後直接區間查找。SQL Server 2000 不支援,2008/2012/2014/2016/2019 同樣適用。

建立百萬測試表
建立百萬測試表
測試結果 1
測試結果 1
測試結果 2
測試結果 2
測試結果 3
測試結果 3
測試結果 4
測試結果 4

關鍵字:分頁、預存程序、ROW_NUMBER、CTE、百萬資料、毫秒級、NOLOCK

copyright © 萬盛學電腦網 all rights reserved