Isnull in a Where Clause

  • luismarinaray

    SSCrazy

    Points: 2808

    Dear friends,

    We used Stored Procedure on our Queries for us to get  valuee we Put the conversion of ISNULL inside the WHERE condition but per checking it affects the Process of our strodproc, it is not using indexes, never index seek always table scan.

    For example,

    SELECT * FROM tblInfo

    WHERE  fldinfo=ISNULL(@parameter,fldinfo)

    What's your recommendation to fix this sentence, because if I remove the isnull function then index seek appears.

    I am interested in some documentation that could support me, to educate our developers team, that functions (any function)  located in WHERE clause are not god ideas, if you can give me some guidance I will appreciate.

    Thanks a lot for your support

     

  • david.edwards 76768

    SSC Eights!

    Points: 999

    What are you trying to achieve?

    Is @parameter an optional parameter? Is tblInfo.fldinfo a NULLable column? Both?

    Certain functions on the right side of the equality do not necessarily always impact performance.

    For example, I never usually have a problem with

    WHERE dateColumn > DATEADD(DAY, -30, GETUTCDATE() )

    because the function is only doing one evaluation of the current date to get a value

    whereas

    WHERE DATEADD(DAY, 30, dateColumn) > GETUTCDATE()

    would be a problem.

  • Steve Collins

    SSC Eights!

    Points: 889

    Imo it's easier to express as 2 queries

    drop table if exists test_tblinfo;
    go
    create table test_tblinfo(fldinfo int);
    go

    insert test_tblinfo values (null), (1);

    declare
    @parameter int=null;

    if @parameter is null
    /* fldinfo=fldinfo will only be true when fldinfo is not null */
    select * from test_tblinfo where fldinfo is not null
    else
    select * from test_tblinfo where fldinfo=@parameter;
  • luismarinaray

    SSCrazy

    Points: 2808

    Thanks for your responses,

    This code is legacy for a client, I am thinking that the original developer tryed to validate their input parameters at the wrong side, fldinfo is a not null value and @parameter is a variable, I am trying the code without the isnull function and this produce an index seek, that's  what I want, only now I am interested in some background information that could help me to give a more informed opinion about this for my client.

    Thanks again

  • ScottPletcher

    SSC Guru

    Points: 98490

    SELECT * 
    FROM tblInfo
    WHERE @parameter IS NULL OR fldinfo = @parameter

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • david.edwards 76768

    SSC Eights!

    Points: 999

    Luis

    I see Scott beat me to it 🙂

    That's what I was going to suggest if you came back and said the parameter was optional. I'm guessing you meant optional rather than variable?

     

     

     

     

  • Steve Collins

    SSC Eights!

    Points: 889

    ScottPletcher wrote:

    SELECT * 
    FROM tblInfo
    WHERE @parameter IS NULL OR fldinfo = @parameter

    Not quite the same as what the OP posted.  Here's another way but readability is not so good imo.

    SELECT * 
    FROM test_tblinfo
    WHERE fldinfo IS NOT NULL OR fldinfo = @parameter;
  • luismarinaray

    SSCrazy

    Points: 2808

    Thanks all for your responses,

    My question now is Why?  Why does not works as is ? we are suggested to a very strict change control policy

    Thanks

  • david.edwards 76768

    SSC Eights!

    Points: 999

    Luis

    You have the same column on the left and right of the equality

    WHERE  fldinfo=ISNULL(@parameter,fldinfo)

    If @parameter is null it's comparing every row to itself.

    Using

    WHERE @parameter IS NULL OR fldinfo = @parameter

    You are comparing against a single value, so the optimiser can make use of statistics to come up with a reasonable plan (parameter sniffing notwithstanding).

    I'm sure someone with more in-depth knowledge can explain the mechanics better, especially how the optimiser interprets it if @parameter is not null, but I'm pretty sure that by putting the column in that function have made the query non sargable.

    Happy to stand corrected.

     

  • Jeff Moden

    SSC Guru

    Points: 996841

    david.edwards 76768 wrote:

    Luis

    You have the same column on the left and right of the equality

    WHERE  fldinfo=ISNULL(@parameter,fldinfo)

    ...

    Happy to stand corrected.

    It's actually because of the function usage on a column.  Every row must be evaluated as a single condition to produce a result before it can use the result to do the comparison.  When you break it apart to two separate conditions, the lookup can happen directly although it would be much better to do this as a dynamic "Catch All" query.  Here's what I consider to be the "bible" on such things...

    https://duckduckgo.com/?t=ffab&q=gail+shaw+catch+all&ia=web

    https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jason A. Long

    SSC-Insane

    Points: 23682

    In addition to Scott's good answer, you should also add "OPTION(RECOMPILE)" to the end of your statement in order to see the desired effect (a seek operation when a value is supplied to the parameter). Other wise, the optimizer will create a "worst case scenario" plan and opt to compile a plan with a scan.

    SELECT * 
    FROM tblInfo
    WHERE @parameter IS NULL OR fldinfo = @parameter
    OPTION(RECOMPILE);

     

  • Steve Collins

    SSC Eights!

    Points: 889

    david.edwards 76768 wrote:

    Using

    WHERE @parameter IS NULL OR fldinfo = @parameter

    You are comparing against a single value

    It's not the same query as the one the OP posted.  NULL does not equal NULL

    drop table if exists test_tblinfo;
    go
    create table test_tblinfo(fldinfo int);
    go
    insert test_tblinfo values
    (null),
    (1);

    declare
    @parameter int=null;


    /* original */
    SELECT * FROM test_tblinfo
    WHERE fldinfo=ISNULL(@parameter,fldinfo);

    /* incorrect */
    SELECT *
    FROM test_tblinfo
    WHERE @parameter IS NULL OR fldinfo = @parameter;

    /* correct */
    SELECT *
    FROM test_tblinfo
    WHERE fldinfo IS NOT NULL OR fldinfo = @parameter;
  • ScottPletcher

    SSC Guru

    Points: 98490

    Any function against a table column in a WHERE clause is not a good idea and is potentially bad for performance, because it prevents index seeks for that comparison.

    https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/

    Remember that SQL has to create one plan before the query runs that always satisfies the query.

    Thus, when you code something like:

    WHERE  fldinfo = ISNULL(@parameter,fldinfo)

    you force SQL to scan the fldinfo column, rather than seek, because it would be too complex for SQL to try to analyze function calls, which could be arbitrarily complex.  Therefore, SQL, to be sure of correct results, must do a scan rather than a seek to match those values.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • david.edwards 76768

    SSC Eights!

    Points: 999

    Steve Collins wrote:

    david.edwards 76768 wrote:

    Using

    WHERE @parameter IS NULL OR fldinfo = @parameter

    You are comparing against a single value

    It's not the same query as the one the OP posted.  NULL does not equal NULL

    drop table if exists test_tblinfo;
    go
    create table test_tblinfo(fldinfo int);
    go
    insert test_tblinfo values
    (null),
    (1);

    declare
    @parameter int=null;


    /* original */
    SELECT * FROM test_tblinfo
    WHERE fldinfo=ISNULL(@parameter,fldinfo);

    /* incorrect */
    SELECT *
    FROM test_tblinfo
    WHERE @parameter IS NULL OR fldinfo = @parameter;

    /* correct */
    SELECT *
    FROM test_tblinfo
    WHERE fldinfo IS NOT NULL OR fldinfo = @parameter;

    Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).

    So presumably @Parameter is optional and can be NULL. In which case the "incorrect" version will return all rows only when @parameter IS NULL, which is presumably the intended behaviour.

  • Steve Collins

    SSC Eights!

    Points: 889

    david.edwards 76768 wrote:

    Yup, of course NULL does not equal NULL, but the fldinfo cannot be NULL (confirmed by OP).

    So presumably @Parameter is optional and can be NULL. In which case the "incorrect" version will return all rows only when @parameter IS NULL, which is presumably the intended behaviour.

    Well you're right the OP did confirm the fldinfo cannot be NULL.  Imo it still makes more sense to have 2 queries (or proc's or whatever) than it does to put "WHERE @parameter IS NULL or..." into 1 query (which I would never do).  So I change my original answer to drop the WHERE clause entirely when @parameter is null.  It could be 2 procs: 1 with mandatory parameter,  and 1 without.

    if @parameter is null
    select * from test_tblinfo;
    else
    select * from test_tblinfo where fldinfo=@parameter;

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply