问题描述
我正在从 .net 调用一些参数化的 sql.我不知道为什么，但是在检查参数是否为 NULL 时，与不包含参数时相比，sql 运行速度很慢:
I'm calling some parametrized sql from .net. I'm not sure why but the sql runs quite slow when checking if the parameter is NULL compared to when its not included:
所以:
exec sp_executesql N'
SELECT [id]
FROM [tblAddress] (nolock)
WHERE 1 = 1
AND ([id] = @id OR @id IS NULL)
',N'@id int',
@id=4395
比这个跑得更快:
exec sp_executesql N'
SELECT [id]
FROM [tblAddress] (nolock)
WHERE 1 = 1
AND ([id] = @id)
',N'@id int',
@id=4395
运行 SQL 探查器，超过 100 万行的顶级查询的持续时间为 175，读取次数为 3720，但第二个查询的持续时间为 1，只有 3 次读取.
Running SQL profiler the duration of the top query over 1 million rows is 175 and its reads are 3720 but the second query's duration is 1 and only 3 reads.
为什么会有这样的差异，如何改进?
Why such a difference and how could it be improved?
推荐答案
OR 子句不是 SARGABLE，所以使用的计划有一个扫描，而不是像第二个那样的搜索
An OR clause isn't SARGABLE so the plan used has a scan, not a seek like the 2nd one
试试这个:2 次搜索
SELECT [id]
FROM [tblAddress]
WHERE [id] = @id
UNION ALL
SELECT [id]
FROM [tblAddress]
WHERE @id IS NULL)
注意:您不需要 NOLOCK 提示.或者 1=1
Note: you don't need the NOLOCK hint. Or 1=1
这篇关于参数化 SQL 和 NULL 运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！