SQL Server Performance Forum – Threads Archive
ISNULL vs OR IS NULL For Evaluting Parameter
Hi All, This is more like a conceptual question. What is better in terms of performace: select * from AccountWhere ((@Account is null) OR (Account like @Account + ‘%’)) OR select * from Account
Where Account like isnull(@Account,Account) + ‘%’ Thanks!!
I don’t think thes any difference in peformance differnce check the query plan for both… I think you should get both plans are same… I see some times better performance when you use UNION ALL instead of OR in like this query.
Mohammed U.
This is probably better: IF @Account IS NULL
select * from Account
ELSE
select * from Account where Account like @Account + ‘%’
use coalesce() instead of isnull() if you care about standards compliance. isnull() is ms only, coalesce() is ansi. also it’s much easier to read with multiple args. SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
www.elsasoft.org
Using IsNULL (or any function for that matter) will eliminate the chances of using an index on that column. Roji. P. Thomas
http://toponewithties.blogspot.com
Since ISNULL prevents from using indexes, I avoid using it in search queries. Adriaan’s suggestion IF @Account IS NULL
select * from Account
ELSE
select * from Account where Account like @Account + ‘%’ would work only for the one-parameter query. But, most of the time we have to search on multiple parameters, many of which may be NULLs. Therefore, the following query looks better. select *
from Account
where ((@Account is null) OR (Account like @Account + ‘%’)) and
((@CustomerName is null) or (CustometName like @CustomerName + ‘%’)) and
… On the other hand, if this query is part of the stored procedure, its execution plan will depend
on which parameters were not NULL at the time of the first call. So – we would have to do some IF statements anyway to map the usage of major indexes? Or – use WITH RECOMPILE?
What do you think? -vitaly
I think the accepted wisdom is that for multiple optional filter criteria, the best option is to use dynamic SQL, as it improves the chances of finding a specific execution plan in cache.
Dont use WITH RECOMPILE if you expect heavy load on the proc. You could run a trace for the 2 recommended methods the one you already have and the one suggested by Adriaan – using dynamic queries but make sure to use sp_ExecuteSQL so the query plans are cached, and compare the query plans for a few runs. Whichever uses better indexes/runs faster for the parameters you supply you can go with that. ***********************
Dinakar Nethi
SQL Server MVP
***********************
]]>