您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 分頁查詢優化：臨時表 + @@ROWCOUNT 提升效能實戰

編輯：SQL Server 2005

問題背景

既有通用分頁存儲過程每次查詢皆對大表 Area 重複掃描多次（統計總筆數、取分頁資料、多次 WHERE 子句），導致效能隨資料量增長急劇下降。

原始寫法缺點

同一複雜 WHERE 條件重複出現 3 次（COUNT、NOT IN 子查詢、主查詢）。
COUNT(Identifier) 需全表掃描，耗資源。
NOT IN (SELECT TOP N ...) 分頁方式在大偏移量時效能極差。

優化版本一：引入臨時表 #temp_Area


-- 1. 將符合條件的資料一次性寫入臨時表
SELECT Identifier, ParentId, AreaLevel, Children, AreaName, Path, Status, Alt
INTO #temp_Area
FROM Area
WHERE (@Identifier IS NULL OR Identifier = @Identifier)
  AND (@ParentId IS NULL OR ParentId = @ParentId)
  ... -- 其餘條件
ORDER BY AreaName ASC;

-- 2. 統計總筆數
DECLARE @SumCount INT;
SELECT @SumCount = COUNT(Identifier) FROM #temp_Area;

-- 3. 計算總頁數
DECLARE @TotalPage INT;
IF (@SumCount % @PageSize = 0) SET @TotalPage = @SumCount / @PageSize;
ELSE SET @TotalPage = ROUND(@SumCount / @PageSize, 0) + 1;

-- 4. 取分頁資料
SELECT TOP (@PageSize) Identifier, ParentId, AreaLevel, Children, AreaName, Path, Status, Alt,
       @TotalPage AS TotalPage, @SumCount AS SumCount
FROM #temp_Area
WHERE Identifier NOT IN (
    SELECT TOP (@PageSize * (@CurrentPage - 1)) Identifier FROM #temp_Area
)
ORDER BY AreaName ASC;

效果： 主表僅掃描一次，後續操作皆在記憶體臨時表上，IO 大幅降低。

優化版本二：使用 @@ROWCOUNT 替代 COUNT(*)

發現 COUNT(Identifier) 仍有開銷，利用 SELECT INTO 完成後 @@ROWCOUNT 即可取得影響行數，省去額外掃描。


SELECT Identifier, ParentId, AreaLevel, Children, AreaName, Path, Status, Alt
INTO #temp_Area
FROM Area
WHERE ... -- 條件同前
ORDER BY AreaName ASC;

DECLARE @SumCount INT = @@ROWCOUNT;  -- 直接取得寫入臨時表的行數
-- 後續分頁邏輯不變

關鍵點： @@ROWCOUNT 緊跟在 SELECT INTO 之後，捕捉該陳述式影響的行數，避免第二次掃描臨時表。

進階建議：現代分頁寫法（SQL Server 2012+）


SELECT Identifier, ParentId, AreaLevel, Children, AreaName, Path, Status, Alt
FROM Area
WHERE ... -- 條件
ORDER BY AreaName ASC
OFFSET (@CurrentPage - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

搭配 COUNT(*) OVER() 窗口函數可單查詢同時取得總筆數與分頁資料，為最佳解。若受限於 SQL Server 2005/2008 R2，臨時表 + @@ROWCOUNT 是相容性最好的高效方案。