SQL Server 2005 利用暫存表與 @@ROWCOUNT 優化分頁查詢存儲過程

原始效能問題

原框架通用分頁存儲過程採用 NOT IN (SELECT TOP ...) 子查詢方式，導致重複掃描基礎表、無法有效使用索引，資料量增長時效能急劇下降。

優化策略：暫存表 + @@ROWCOUNT

1. 先將符合條件的主鍵 INSERT INTO #TempIds，僅掃描一次基礎表。
2. 對暫存表建立叢集索引 (主鍵)，後續分頁查詢走索引 Seek。
3. 使用 @@ROWCOUNT 取得總筆數計算總頁數，避免第二次 COUNT(*) 掃描。
4. 分頁查詢透過 JOIN #TempIds 定位目標頁資料，再關聯主表取得完整欄位。

優化後存儲過程範本

CREATE PROCEDURE [dbo].[AreaSelect_Optimized]
    @PageSize INT = 20,
    @CurrentPage INT = 1,
    @AreaName NVARCHAR(50) = NULL,
    -- 其他參數...
AS
BEGIN
    SET NOCOUNT ON;
    IF @AreaName IS NOT NULL SET @AreaName = '%' + @AreaName + '%';

    -- 1. 篩選符合條件的 ID 存入暫存表
    CREATE TABLE #TempIds (Identifier INT PRIMARY KEY);
    INSERT INTO #TempIds (Identifier)
    SELECT Identifier FROM Area
    WHERE (@AreaName IS NULL OR AreaName LIKE @AreaName)
      -- 其他條件...
    ORDER BY AreaName; -- 符合索引順序

    DECLARE @TotalRows INT = @@ROWCOUNT;
    DECLARE @TotalPages INT = CASE WHEN @TotalRows % @PageSize = 0 
        THEN @TotalRows / @PageSize ELSE @TotalRows / @PageSize + 1 END;

    -- 2. 計算分頁範圍
    DECLARE @StartRow INT = (@CurrentPage - 1) * @PageSize + 1;
    DECLARE @EndRow INT = @CurrentPage * @PageSize;

    -- 3. 使用 ROW_NUMBER 配合暫存表分頁 (SQL 2005+)
    ;WITH Numbered AS (
        SELECT t.Identifier, ROW_NUMBER() OVER (ORDER BY t.Identifier) AS RowNum
        FROM #TempIds t
    )
    SELECT a.Identifier, a.ParentId, a.AreaLevel, a.Children, a.AreaName, a.Path, a.Status, a.Alt,
           @TotalPages AS TotalPage
    FROM Area a
    INNER JOIN Numbered n ON a.Identifier = n.Identifier
    WHERE n.RowNum BETWEEN @StartRow AND @EndRow
    ORDER BY a.AreaName;

    DROP TABLE #TempIds;
END

效能提升分析

補充建議

• SQL Server 2012+ 可直接使用 OFFSET ... FETCH 簡化分頁語法。
• 若分頁欄位固定，考慮在主表建立覆蓋索引避免 Key Lookup。
• 暫存表建立後可加 CREATE CLUSTERED INDEX 明確指定索引順序。