如何在分页查询时获取总记录数并保持性能？

核心思路：拆分计数与数据查询，复用过滤逻辑 #

既然分页查询（OFFSET/FETCH）已经解决了性能问题，我们可以把总记录数计算和分页数据获取拆分为两个低开销的独立操作，避免重复执行全量关联和函数计算。

方案1：用临时表存储筛选后的核心主键 #

先把符合WHERE条件的核心表主键（比如tbl1.caseID、tbl1.orderID）筛选出来，基于这个极小的数据集分别做计数和分页关联：

-- 1. 先筛选符合条件的核心主键，仅关联WHERE用到的表
SELECT t1.caseID, t1.orderID
INTO #FilteredKeys
FROM tbl1 t1
LEFT JOIN tbl10 t10 ON t1.关联字段 = t10.关联字段
WHERE 
   (t10.ArrivalDate between @foo1 and @foo2)
   AND (@prmProcessingID = 0 OR t10.ProcessingID = @prmProcessingID)
   -- 其他WHERE条件

-- 2. 快速计算总记录数（无需全量JOIN）
SELECT COUNT(*) AS TotalRecords FROM #FilteredKeys

-- 3. 基于筛选后的主键获取分页数据，仅关联需要的表
SELECT 
    fn_DoWork1(fk.caseID, fk.orderID) as cln1,
    fn_DoWork2(fk.caseID, fk.orderID) as cln2,
    -- 其他函数列
    BalanceDue
FROM #FilteredKeys fk
LEFT JOIN tbl1 t1 ON fk.caseID = t1.caseID AND fk.orderID = t1.orderID
LEFT JOIN tbl2 t2 ON ...
-- 其他必要的LEFT JOIN
ORDER BY t1.排序字段 -- 必须加稳定排序，避免分页结果混乱
OFFSET @PageOffset ROWS
FETCH NEXT 20 ROWS ONLY

DROP TABLE #FilteredKeys

这个方法的优势是计数操作仅处理筛选主键的逻辑，性能开销极低；分页查询也只针对筛选后的数据集做关联，保持原有的性能优势。

方案2：用COUNT(*) OVER()在分页查询中同时返回总条数 #

如果不想拆分查询，可以在分页语句中加入窗口函数获取总记录数，前端只需取第一条数据的TotalRecords值即可：

WITH FilteredData AS (
    -- 先过滤出符合条件的基础数据，仅关联必要的表和字段
    SELECT 
        t1.caseID, t1.orderID,
        t10.ArrivalDate, t10.ProcessingID,
        BalanceDue
    FROM tbl1 t1
    LEFT JOIN tbl10 t10 ON ...
    WHERE 
       (t10.ArrivalDate between @foo1 and @foo2)
       AND (@prmProcessingID = 0 OR t10.ProcessingID = @prmProcessingID)
)
SELECT 
    fn_DoWork1(fd.caseID, fd.orderID) as cln1,
    fn_DoWork2(fd.caseID, fd.orderID) as cln2,
    -- 其他函数列
    fd.BalanceDue,
    COUNT(*) OVER() AS TotalRecords -- 返回全量符合条件的总条数
FROM FilteredData fd
LEFT JOIN tbl2 t2 ON ...
-- 其他必要的LEFT JOIN
ORDER BY fd.排序字段
OFFSET @PageOffset ROWS
FETCH NEXT 20 ROWS ONLY

该方法只需一次查询，但要注意：如果过滤后的数据集极大，COUNT(*) OVER()会有一定额外开销，但远低于全量执行原查询。

额外优化点 #

• 替换标量函数：把fn_DoWork1这类标量函数改成内联表值函数（ITVF），或直接将函数逻辑嵌入查询，避免逐行调用的性能损耗。
• 优化索引：给WHERE条件中的字段（如tbl10.ArrivalDate、tbl10.ProcessingID）建立复合索引，加速核心主键的筛选。
• 清理冗余JOIN：检查30个LEFT JOIN是否都是必要的，部分表可能仅在函数中用到，可改为子查询或按需关联。

内容的提问来源于stack exchange，提问作者AngryHacker