sql-server - 参数导致 SQL Server 中的表扫描

Question

我有两个表a，b，主键是它们的索引。

要求:

如果@filter为空，则选择a、b的所有记录，否则将@filter按任意特定分隔符拆分，查找b.PKey<的记录 在过滤器中。

当前实现:

declare @filter nvarchar(max)= ''

SELECT * 
FROM a
JOIN b ON a.PKey = b.aPKey
       AND (@filter = '' OR b.PKey IN (SELECT item FROM splitFunction(@filter))

我发现最后一条语句 and (@filter = '' or b.PKey in (select item from splitFunction(@filter)) 总是会导致表 b 的表扫描，只有当我去掉@filter=''，会变成index seek。

有什么方法可以实现我的要求并且不损害性能？

Answer 1

对于常量，优化器可以根据给定值的统计数据制定最佳计划。

当您使用变量时，您将强制进行参数化，并且计划将被设计为可针对广泛的值重复使用。所以优化器使用扫描而不是寻找。要克服此问题，请停止在查询中使用局部变量并将其用作存储过程中的参数

create procedure p1
@filter = ''
as
begin
SELECT * 
FROM a
JOIN b ON a.PKey = b.aPKey
       AND (@filter = '' OR b.PKey IN (SELECT item FROM splitFunction(@filter))
option (recompile)
end

这将为您提供正确的计划，因为在我已将您的局部变量转换为参数的存储过程中。