Java + SQL Server 高效分頁預存程序實作 (游標法，250 萬筆 7 秒)

前言

海量資料分頁是常見效能挑戰。SQL Server 2000/2005 缺乏 OFFSET/FETCH (SQL 2012+)，傳統 TOP N + NOT IN 或 ROW_NUMBER() OVER (SQL 2005+) 在大偏移量時效能急劇下降。本文介紹利用 **伺服器端游標 (sp_cursoropen/fetch/close)** 實作高效分頁預存程序，實測 250 萬筆資料任意頁查詢約 7 秒，並提供 Java CallableStatement 完整呼叫範例。

一、分頁預存程序 (游標法)

CREATE PROCEDURE dbo.Proc_FenYe
    @SqlStr     NVARCHAR(4000),  -- 查詢語句 (不含 ORDER BY 亦可)
    @CurrentPage INT,            -- 第 N 頁 (1-based)
    @PageSize   INT              -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CursorId INT,
            @RowCount INT,
            @TotalPage INT;

    -- 1. 開啟快速唯讀游標 (FORWARD_ONLY, READ_ONLY)
    -- @scrollopt=1 (FORWARD_ONLY), @ccopt=1 (READ_ONLY)
    EXEC sp_cursoropen @CursorId OUTPUT, @SqlStr, @scrollopt = 1, @ccopt = 1, @rowcount = @RowCount OUTPUT;

    -- 2. 計算總頁數
    SET @TotalPage = CEILING(1.0 * @RowCount / @PageSize);

    -- 3. 回傳分頁資訊 (第 1 個結果集)
    SELECT @CurrentPage AS CurrPage, @TotalPage AS TotalPage, @RowCount AS TotalRow;

    -- 4. 修正頁碼越界
    IF @CurrentPage > @TotalPage SET @CurrentPage = @TotalPage;
    IF @CurrentPage < 1 SET @CurrentPage = 1;

    -- 5. 計算絕對擷取位置 (1-based)
    DECLARE @FetchPos INT = (@CurrentPage - 1) * @PageSize + 1;

    -- 6. 擷取指定範圍資料列 (第 2 個結果集)
    -- @fetchtype=16 (FETCH_ABSOLUTE), @rownumber=@FetchPos, @nrows=@PageSize
    EXEC sp_cursorfetch @CursorId, 16, @FetchPos, @PageSize;

    -- 7. 關閉游標釋放資源
    EXEC sp_cursorclose @CursorId;

    SET NOCOUNT OFF;
END;

二、關鍵參數說明

三、Java 呼叫範例

public void testPaging() {
    String url = "jdbc:sqlserver://localhost:1433;databaseName=pubs;encrypt=true;trustServerCertificate=true";
    String user = "sa", pwd = "";

    String sql = "SELECT * FROM t_tmp";  -- 250 萬筆測試表

    try (Connection con = DriverManager.getConnection(url, user, pwd);
         CallableStatement cs = con.prepareCall("{call dbo.Proc_FenYe(?, ?, ?)}")) {

        cs.setString(1, sql);
        cs.setInt(2, 2);     // 第 2 頁
        cs.setInt(3, 10);    // 每頁 10 筆

        boolean hasResults = cs.execute();

        // 第 1 個結果集：分頁資訊
        if (hasResults) {
            try (ResultSet rsInfo = cs.getResultSet()) {
                if (rsInfo.next()) {
                    System.out.printf("頁碼: %d, 總頁數: %d, 總筆數: %d%n",
                            rsInfo.getInt("CurrPage"),
                            rsInfo.getInt("TotalPage"),
                            rsInfo.getInt("TotalRow"));
                }
            }
        }

        // 第 2 個結果集：實際資料
        if (cs.getMoreResults()) {
            try (ResultSet rsData = cs.getResultSet()) {
                while (rsData.next()) {
                    System.out.println("欄位1: " + rsData.getString(1) +
                                       ", 欄位2: " + rsData.getString(2));
                }
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

四、效能優勢分析

游標法利用資料庫引擎內部定位機制，避免應用層傳輸大量中間資料，且不需額外排序運算，是舊版本 SQL Server 大資料量分頁的最佳選擇。

五、注意事項與限制

1. @SqlStr 長度限制 4000 字元：複雜查詢超過長度需改用動態 SQL + sp_executesql 並輸出至暫存表，再對暫存表開游標。
2. 游標資源：佔用 tempdb 與記憶體，高併發時注意連線池大小與游標關閉釋放 (預存程序結束自動關閉，但顯式 sp_cursorclose 更保險)。
3. 唯讀/快速唯讀：@ccopt=1 確保不加鎖，適合報表/查詢場景；不可用於需更新的分頁。
4. 順序穩定性：@SqlStr **必須包含確定性 ORDER BY**，否則分頁結果不穩定。

六、現代替代方案 (SQL 2012+)

-- 推薦：ANSI 標準，可讀性佳，優化器支援良好
SELECT * FROM LargeTable
ORDER BY Id
OFFSET (@PageNo - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

若環境已升級至 SQL Server 2012 或更新版本，請優先使用 OFFSET/FETCH，僅在相容性受限環境使用游標法。

結語

伺服器端游標分頁法是 SQL Server 2000/2005/2008/R2 時代大資料量分頁的「殺手鐧」。掌握 sp_cursoropen/fetch/close 參數組合、Java 多結果集處理 (getMoreResults())、資源管理與順序穩定性要求，即能在舊版本平台上實現高效、穩定的分頁查詢。