SQL Server Performance

ISNULL vs OR IS NULL For Evaluting Parameter

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by flaviotsf, Nov 23, 2006.

  1. flaviotsf New Member

    Hi All,

    This is more like a conceptual question.

    What is better in terms of performace:

    select * from Account
    Where ((@Account is null) OR (Account like @Account + '%'))

    OR

    select * from Account
    Where Account like isnull(@Account,Account) + '%'

    Thanks!!
  2. MohammedU New Member

    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.
  3. Adriaan New Member

    This is probably better:

    IF @Account IS NULL
    select * from Account
    ELSE
    select * from Account where Account like @Account + '%'
  4. jezemine New Member

    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
  5. Roji. P. Thomas New Member

  6. vitaly New Member

    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
  7. Adriaan New Member

    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.
  8. ndinakar Member

    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
    ***********************

Share This Page