locked
"nullable columns can cause final results to be evaluated as NULL for the predicate" VS "deterministic function call (ISNULL) might cause an unnecessary table scan" RRS feed

  • Question

  • Typical Rock vs Hard Place...

    It seems to me that VS's DBProj REALLY wants my columns to be NOT NULL.  Too bad this doesn't fit the reality of database design (Nullable columns will need to exist, as well as nullable parameters).

    So, in my case, I have dozens of procs in my project which do this in the where clause

    WHERE (ISNULL(@caps_ID,-1) = ISNULL(c.caps_ID,-1))

    I've also tried

    WHERE (@caps_ID IS NULL OR @caps_ID = c.caps_ID)

    Both give build warnings and it's quite annoying because there are ~200 of them.

     

    Please no responses recommending to turn off warnings.  I'm quite aware of how to do that and I'm not looking for an easy workaround to ignoring these warnings.  My purpose here is to learn how to properly satisfy the warnings that the DBProj has presented to me.  If we determine that one or the other warning is inevitable given my situation, maybe MSFT might take note and repair the situation somehow.

     


    Tim Carper
    • Moved by Larcolais Gong Wednesday, September 29, 2010 3:15 AM (From:Visual Studio Setup and Installation)
    Monday, September 27, 2010 2:40 PM

Answers

  • Tim,

    It will help if you can be more up front with all of your environmental requirements -- in this case Linq.  This will frequently keep at least some of us from going on long safaris chasing "wild aquatic fowl".

    I kind of thought the point was to do this, but is part of what you are after here to make these serches efficient?

    Something that you might have to face, and might have to face it now, is this:

    The native language that is used for interfacing with the database is T-SQL and not Linq.  While Linq might be able to generate adequate SQL for many instances encountered in day-to-day programming, there are going to be some things -- perhaps many things, depending on what you are doing -- that give better database performance if written T-SQL rather than Linq.

    This doesn't mean that you can't use the Linq layer to get at this kind of T-SQL code, it just means that you might want to consider cases in which Linq is calling a stored procedure.  There might be other techniques to discuss too.  I would suggest that completely throwing out T-SQL because you want to primarily use Linq might not be the best approach.

    I suggest that when you get into situations in which Linq code doesn't perform well that you consider using T-SQL as a secondary approach.  Also keep in the back of your mind that you might also want to consider CLR functions (.NET) solutions from time as a third alternative.

    Friday, November 5, 2010 7:32 PM
  • I don't know if this helps or not, but this might be a way to avoid the table scans.  Here is a table that I used for testing:

    create table dbo.someTable
    ( record_id integer not null
      constraint pk_SomeTable primary key
      constraint ck_someTable_Record_Id
       check (record_Id > 0),
     filler char(50)
    )
    go
    
    ;WITH
     L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
     L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
     L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
    
    insert into dbo.someTable
    select
     n,
     'This is a sample record; record # ' + cast(n as varchar(7))
    from numbers
    where n <= 999999
    go
    
    update statistics someTable
    

    And here is a query example that might help, but I am not sure that this mumbo jumbo targets the same target problem is your target problem:

    declare @theId integer
    declare @sql nvarchar(800)
    set @theId = 14
    
    --select top 5000 x.*
    --from dbo.someTable x
    --where @theId is null
    --  or x.record_Id = @theId
    
    select top 5000 x.*
    from ( select coalesce(@theId, 0) as theId, 
           case when @theId is null then 0 else 1 end as joinType,
           convert(int, 0x7fffffff) as maxId 
       ) jt
    join dbo.someTable x
     on x.record_Id >= theId * joinType
     and x.record_Id <= theId * joinType + ((joinType+1)%2) * maxId 
    
    /* -------- Output when parameter is specified: --------
    record_id  filler
    ----------- --------------------------------------------------
    14     This is a sample record; record # 14       
    
    (1 row(s) affected)
    
    Table 'someTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    StmtText
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     |--Top(TOP EXPRESSION:((5000)))
        |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[someTable].[pk_SomeTable] AS [x]), SEEK:([x].[record_id] >= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END AND [x].[record_id] <= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+((CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+(1))%(2))*(2147483647)) ORDERED FORWARD)
    */
    
    
    /* -------- Output when parameter is NULL: --------
    record_id  filler
    ----------- --------------------------------------------------
    1      This is a sample record; record # 1        
    2      This is a sample record; record # 2        
    ...
    5000    This is a sample record; record # 5000      
    
    (5000 row(s) affected)
    
    Table 'someTable'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    StmtText
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     |--Top(TOP EXPRESSION:((5000)))
        |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[someTable].[pk_SomeTable] AS [x]), SEEK:([x].[record_id] >= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END AND [x].[record_id] <= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+((CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+(1))%(2))*(2147483647)) ORDERED FORWARD)
    
    (2 row(s) affected)
    
    */
    
    

     

    Wednesday, September 29, 2010 2:16 PM
  • I believe the problem you're discussing is covered in this blog post 

    Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

    and comments to this blog. Try to apply suggestions from that post for your scenario (it may mean you need to use dynamic SQL).

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    I thought it was supposed to work as you say.  Really, that is the point of my other post in the Transact SQL Forum.  I was surprised when it didn't.  The version of SQL Server that I tested on is:

    Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
    Nov 24 2008 13:01:59
    Copyright (c) 1988-2005 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    Is the reason I am not getting performance related to my particular version?

    Wednesday, September 29, 2010 2:54 PM
  • A better version of this query might be something like:

    select *
    from dbo.someTable
    where record_Id between isnull(@theId, convert(int, 0x80000000))
       and isnull(@theId, convert(int, 0x7fffffff))
    
    

    The advantage of this form is that it doesn't depend an id column that contains only positive values.

    EDIT:

    A good bit of credit for this version needs to go to Rami Reddy

    Friday, October 1, 2010 12:56 PM
  • Why not max/min values for integer directly?

    -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

    BTW, is it really better than isnull(@thefld, thefld) ?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, May 27, 2011 8:51 PM
    Answerer

All replies

  • Hi Tim,

    Your concern is more like SQL.

    I will help you moving the thread to appropriate forum.

    If you have any Visual Studio, please feel free to let me know.

    Best Regards

    Larcolais


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, September 29, 2010 3:14 AM
  • Can you please explain clearly what is your exact issue??

     

    thanks,


    - MS (We are what we repeatedly do, Excellence, then, is not an act, but a habit.)
    Wednesday, September 29, 2010 12:21 PM
  • I don't know if this helps or not, but this might be a way to avoid the table scans.  Here is a table that I used for testing:

    create table dbo.someTable
    ( record_id integer not null
      constraint pk_SomeTable primary key
      constraint ck_someTable_Record_Id
       check (record_Id > 0),
     filler char(50)
    )
    go
    
    ;WITH
     L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
     L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
     L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
     L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
     L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
     L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),--4294967296 rows
    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
    
    insert into dbo.someTable
    select
     n,
     'This is a sample record; record # ' + cast(n as varchar(7))
    from numbers
    where n <= 999999
    go
    
    update statistics someTable
    

    And here is a query example that might help, but I am not sure that this mumbo jumbo targets the same target problem is your target problem:

    declare @theId integer
    declare @sql nvarchar(800)
    set @theId = 14
    
    --select top 5000 x.*
    --from dbo.someTable x
    --where @theId is null
    --  or x.record_Id = @theId
    
    select top 5000 x.*
    from ( select coalesce(@theId, 0) as theId, 
           case when @theId is null then 0 else 1 end as joinType,
           convert(int, 0x7fffffff) as maxId 
       ) jt
    join dbo.someTable x
     on x.record_Id >= theId * joinType
     and x.record_Id <= theId * joinType + ((joinType+1)%2) * maxId 
    
    /* -------- Output when parameter is specified: --------
    record_id  filler
    ----------- --------------------------------------------------
    14     This is a sample record; record # 14       
    
    (1 row(s) affected)
    
    Table 'someTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    StmtText
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     |--Top(TOP EXPRESSION:((5000)))
        |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[someTable].[pk_SomeTable] AS [x]), SEEK:([x].[record_id] >= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END AND [x].[record_id] <= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+((CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+(1))%(2))*(2147483647)) ORDERED FORWARD)
    */
    
    
    /* -------- Output when parameter is NULL: --------
    record_id  filler
    ----------- --------------------------------------------------
    1      This is a sample record; record # 1        
    2      This is a sample record; record # 2        
    ...
    5000    This is a sample record; record # 5000      
    
    (5000 row(s) affected)
    
    Table 'someTable'. Scan count 1, logical reads 46, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    
    StmtText
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     |--Top(TOP EXPRESSION:((5000)))
        |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[someTable].[pk_SomeTable] AS [x]), SEEK:([x].[record_id] >= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END AND [x].[record_id] <= CASE WHEN [@theId] IS NOT NULL THEN [@theId] ELSE (0) END*CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+((CASE WHEN [@theId] IS NULL THEN (0) ELSE (1) END+(1))%(2))*(2147483647)) ORDERED FORWARD)
    
    (2 row(s) affected)
    
    */
    
    

     

    Wednesday, September 29, 2010 2:16 PM
  • I believe the problem you're discussing is covered in this blog post 

    Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

    and comments to this blog. Try to apply suggestions from that post for your scenario (it may mean you need to use dynamic SQL).


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, September 29, 2010 2:34 PM
    Answerer
  • I believe the problem you're discussing is covered in this blog post 

    Do you use Column=@Param OR @Param IS NULL in your WHERE clause? Don't, it doesn't perform

    and comments to this blog. Try to apply suggestions from that post for your scenario (it may mean you need to use dynamic SQL).

     


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog


    I thought it was supposed to work as you say.  Really, that is the point of my other post in the Transact SQL Forum.  I was surprised when it didn't.  The version of SQL Server that I tested on is:

    Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
    Nov 24 2008 13:01:59
    Copyright (c) 1988-2005 Microsoft Corporation
    Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

    Is the reason I am not getting performance related to my particular version?

    Wednesday, September 29, 2010 2:54 PM
  • A better version of this query might be something like:

    select *
    from dbo.someTable
    where record_Id between isnull(@theId, convert(int, 0x80000000))
       and isnull(@theId, convert(int, 0x7fffffff))
    
    

    The advantage of this form is that it doesn't depend an id column that contains only positive values.

    EDIT:

    A good bit of credit for this version needs to go to Rami Reddy

    Friday, October 1, 2010 12:56 PM
  • So, it seems the prevailing school of thought is to use Dynamic SQL to construct the WHERE clause.  Their recommendation is to use

    SET @sql = 'SELECT t.foo from bar t WHERE 1=1'

    IF (@param1 is not null)

       SET @sql=@sql + ' AND t.column1 = @param1'

    IF (@param2 is not null)

       SET @sql=@sql + ' AND t.column1 = @param2'


    EXEC SP_EXECUTESQL @SQL,N'@Param1 INT,@Param2 INT',@col1,@Col2


    Tim Carper
    Friday, October 29, 2010 6:20 PM
  • If such cases only dynamic sql will more efficient

    http://www.sommarskog.se/dyn-search.html


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, October 31, 2010 9:04 AM
  • So, it seems the prevailing school of thought is to use Dynamic SQL to construct the WHERE clause.  Their recommendation is to use

    SET @sql = 'SELECT t.foo from bar t WHERE 1=1'

    IF (@param1 is not null)

       SET @sql=@sql + ' AND t.column1 = @param1'

    IF (@param2 is not null)

       SET @sql=@sql + ' AND t.column1 = @param2'


    EXEC SP_EXECUTESQL @SQL,N'@Param1 INT,@Param2 INT',@col1,@Col2


    Tim Carper

     

    Based on what you are saying, yes, I would suggest dynamic SQL

    Tuesday, November 2, 2010 5:42 PM
  • Rats... the Web Dev's are giving me grief now about Linq not liking D-SQL.  I've done a bit of research on the subject and apparently they'd have to edit the DBML themselves... bad times.

    Tim Carper
    Friday, November 5, 2010 4:29 PM
  • Tim,

    It will help if you can be more up front with all of your environmental requirements -- in this case Linq.  This will frequently keep at least some of us from going on long safaris chasing "wild aquatic fowl".

    I kind of thought the point was to do this, but is part of what you are after here to make these serches efficient?

    Something that you might have to face, and might have to face it now, is this:

    The native language that is used for interfacing with the database is T-SQL and not Linq.  While Linq might be able to generate adequate SQL for many instances encountered in day-to-day programming, there are going to be some things -- perhaps many things, depending on what you are doing -- that give better database performance if written T-SQL rather than Linq.

    This doesn't mean that you can't use the Linq layer to get at this kind of T-SQL code, it just means that you might want to consider cases in which Linq is calling a stored procedure.  There might be other techniques to discuss too.  I would suggest that completely throwing out T-SQL because you want to primarily use Linq might not be the best approach.

    I suggest that when you get into situations in which Linq code doesn't perform well that you consider using T-SQL as a secondary approach.  Also keep in the back of your mind that you might also want to consider CLR functions (.NET) solutions from time as a third alternative.

    Friday, November 5, 2010 7:32 PM
  • Why not max/min values for integer directly?

    -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

    BTW, is it really better than isnull(@thefld, thefld) ?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, May 27, 2011 8:51 PM
    Answerer