使用OFFSET/FETCH为何引发T-SQL查询性能问题？

问题场景 #

使用OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY分页时，查询耗时超5分钟仅返回20条记录，但以下两种场景执行极快：

• 移除FETCH NEXT 20 ROWS ONLY，仅保留OFFSET 20 ROWS，返回2000条记录耗时0秒；
• 先将查询结果插入临时表，再对临时表执行分页，耗时也为0秒。

原查询代码 #

SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME
    , ITEM.CODE_CATEGORY_ID CATEGORY_ID, ITEM_CAT.CODE CATEGORY_CODE
    , ITEM_CAT.NAME CATEGORY_NAME
FROM B1.SP_ITEM_MF ITEM
INNER JOIN B1.SP_ADMIN_STRUCTURE ITEM_CAT ON ITEM.CODE_CATEGORY_ID = ITEM_CAT.ID
    AND ITEM_CAT.RECORD_TYPE ='CD'
    AND ITEM_CAT.ACTIVE='Y'
INNER JOIN [A1].SP_REQ_CATEGORY_MAPPING MAP ON MAP.ITEM_CATEGORY_ID = ITEM.CODE_CATEGORY_ID
INNER JOIN B1.SP_ADMIN_STRUCTURE  REQ_CAT ON REQ_CAT.ID = MAP.REQ_CATEGORY_ID
    AND REQ_CAT.RECORD_TYPE = 'RQ'
    AND REQ_CAT.ACTIVE = 'Y'
    AND REQ_CAT.CODE IN ('CRW','SCHF')
ORDER BY ITEM.CODE
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY

临时表改写后的查询代码 #

CREATE TABLE #TempPagination (
    RowNum INT,
    ITEM_ID BIGINT,
    ITEM_CODE VARCHAR(64),
    ITEM_NAME VARCHAR(256),
    CATEGORY_ID INT,
    CATEGORY_CODE VARCHAR(8),
    CATEGORY_NAME VARCHAR(64)
);

INSERT INTO #TempPagination (ITEM_ID, ITEM_CODE, ITEM_NAME, CATEGORY_ID, CATEGORY_CODE, CATEGORY_NAME)
SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME
    , ITEM.CODE_CATEGORY_ID CATEGORY_ID,ITEM_CAT.CODE CATEGORY_CODE,ITEM_CAT.NAME CATEGORY_NAME
FROM B1.SP_ITEM_MF ITEM
INNER JOIN B1.SP_ADMIN_STRUCTURE ITEM_CAT ON ITEM.CODE_CATEGORY_ID = ITEM_CAT.ID
    AND ITEM_CAT.RECORD_TYPE = 'CD'
    AND ITEM_CAT.ACTIVE = 'Y'
INNER JOIN A1.SP_REQ_CATEGORY_MAPPING MAP ON MAP.ITEM_CATEGORY_ID = ITEM.CODE_CATEGORY_ID
INNER JOIN B1.SP_ADMIN_STRUCTURE REQ_CAT ON REQ_CAT.ID = MAP.REQ_CATEGORY_ID
    AND REQ_CAT.RECORD_TYPE = 'RQ'
    AND REQ_CAT.ACTIVE = 'Y'
    AND REQ_CAT.CODE IN ('CRW','SCHF');

SELECT 
    ITEM_ID,
    ITEM_CODE,
    ITEM_NAME,
    CATEGORY_ID,
    CATEGORY_CODE,
    CATEGORY_NAME
FROM #TempPagination
ORDER BY ITEM_CODE
OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

DROP TABLE #TempPagination;

环境：SQL Server 2019，相关字段已建索引。

问题解答 #

1. 为何不使用临时表时查询耗时久，使用临时表则快速？ #

从执行计划的差异来看：

• 原查询中，SQL Server优化器选择了先排序后过滤关联的执行路径：因为要先按ITEM.CODE排序再分页，优化器错误预估了中间结果集的大小，尝试在关联过程中提前应用分页逻辑，触发了大量嵌套循环关联和重复排序操作，实际扫描的数据量远超预期，导致性能暴跌。
• 临时表方案强制优化器分两步执行：第一步先完成所有表的关联过滤，生成完整的结果集写入临时表——这一步优化器能正确识别结果集规模，选择哈希关联或合并关联等高效策略；第二步对仅2000条左右的临时表数据做排序分页，成本几乎可以忽略，因此整体耗时极短。

简单来说，临时表避免了优化器在分页逻辑上做出错误的执行计划选择，把“分页+关联排序”拆成了“关联过滤+分页排序”两个低耗步骤。

2. FETCH NEXT 20 ROWS ONLY为何会导致性能大幅下降？ #

FETCH NEXT 20 ROWS ONLY会让优化器产生“只需要返回少量数据”的判断，进而倾向于选择**“先排序后取数”的执行计划**，但这个选择在你的场景中完全错误：

• 排序字段ITEM.CODE来自大表B1.SP_ITEM_MF，且关联涉及跨库表和多条件过滤，优化器错误预估了排序前需要处理的数据量，以为可以先按ITEM.CODE排序，再逐步关联其他表并取数。实际执行时却需要扫描整个大表索引，反复执行关联操作，导致大量I/O和CPU消耗。
• 当移除FETCH NEXT 20 ROWS ONLY后，优化器明确需要返回全量结果，会选择更高效的关联策略先完成过滤关联，再对仅2000条数据排序，成本极低，因此耗时0秒。

本质是优化器的基数预估错误：它误以为少量数据就能满足分页需求，实际却需要处理大量数据才能完成排序和关联，最终导致执行计划效率极低。

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