問題描述
我正在從 .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 運行緩慢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！