SQL Server是否有行数相关的优化器提示？批量ID查询性能优化求助

针对你遇到的STRING_SPLIT被优化器固定估算为50行导致的性能问题，以下是无需临时表的解决方案：

方法1：使用RECOMPILE选项强制编译时获取实际行数 #

添加OPTION (RECOMPILE)后，SQL Server会在查询编译时解析@ids的实际值，从而准确计算STRING_SPLIT的返回行数，生成更优的执行计划：

SELECT * 
FROM (complex query) AS foo 
JOIN STRING_SPLIT(@ids, ';') AS ids 
  ON foo.id = ids.value
OPTION (RECOMPILE)

注意：该选项会增加每次查询的编译开销，适合单次执行或ID集合规模波动大的场景。若查询高频执行，需权衡编译成本与执行效率的提升。

方法2：改用表值参数(TVP)传递ID集合 #

如果应用代码允许修改，推荐使用表值参数替代字符串拼接的方式传递ID集合。SQL Server会自动识别TVP的实际行数，优化器能直接基于准确统计生成执行计划：

1. 先创建自定义表类型：

CREATE TYPE IdCollection AS TABLE (Id INT PRIMARY KEY);

2. 在查询中使用该类型：

DECLARE @ids IdCollection;
-- 由应用将ID数据填充到@ids中
SELECT * 
FROM (complex query) AS foo 
JOIN @ids AS ids 
  ON foo.id = ids.Id;

这种方法避免了字符串拆分的额外开销，同时彻底解决基数估算不准的问题，是长期最优方案。

方法3：手动构造带统计提示的子查询 #

如果上述两种方法都无法使用，可以通过在STRING_SPLIT结果中关联一个已知行数的虚拟表，结合查询提示修正估算值。例如已知返回1000行时：

SELECT * 
FROM (complex query) AS foo 
JOIN (
    SELECT value AS id
    FROM STRING_SPLIT(@ids, ';')
    -- 关联一个行数匹配的虚拟表，强制优化器调整估算
    CROSS APPLY (SELECT TOP (1000) 1 FROM sys.all_columns) AS dummy
    -- 去重避免重复行
    GROUP BY value
) AS ids ON foo.id = ids.id
OPTION (USE HINT('ASSUME_FULLY_PREDICATED'));

该方法通过构造虚拟关联调整优化器的行数估算，需根据实际行数修改TOP的数值，通用性稍弱。

内容的提问来源于stack exchange，提问作者not a name