none
Is this worth the effort? RRS feed

  • Question

  • This post comes as a result of a discussion related to this post in the Database Design forum:

    http://social.technet.microsoft.com/Forums/en-US/databasedesign/thread/2c991301-5b80-454a-80d8-090a5a475643

    I used this table for test purposes:

    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
    

    I tested this code:

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

    Is this mumbo jumbo worth the effort?

    Mud slinging comments?  Insults?

     

    Wednesday, September 29, 2010 2:27 PM
    Moderator

Answers

  • a further simplication.. No need of 2 variables also now... :)

    declare @TheID int = null
    select top 5000 x.*
    from dbo.someTable x
     where x.record_Id >= isnull(@TheID,0) and x.record_Id <= isnull(@TheID,convert(int, 0x7fffffff))
    

    Wednesday, September 29, 2010 3:06 PM
  • I think this constraint is not required.... I removed that constraint and i changed the query like below... seems to be working as expected in results and perf. both...

     

    declare @TheID int = null
    select top 5000 x.*
    from dbo.someTable x
     where x.record_Id >= isnull(@TheID,( -1 * convert(int, 0x7fffffff) )-1) and x.record_Id <= isnull(@TheID,convert(int, 0x7fffffff))
     
     
     
     
    

     

    Wednesday, September 29, 2010 4:46 PM

All replies

  • Kent,

          nice idea.... Innovation at its best..

    For me I need to simplify like this..otherwise, I will forgot that... Not sure any drawbacks of this simplication....

     

    declare @theId integer
    declare @sql nvarchar(800)
    set @theId = null
    declare @MinID int = isnull(@theID,0)
    declare @MaxID int = isnull(@theID,convert(int, 0x7fffffff))
    
    
    
    select top 5000 x.*
    from dbo.someTable x
     where x.record_Id >= @MinID and x.record_Id <= @MaxID
    
    

     

     

    Wednesday, September 29, 2010 2:54 PM
  • Kent,

          nice idea.... Innovation at its best..

    For me I need to simplify like this..otherwise, I will forgot that... Not sure any drawbacks of this simplication....

     

    declare @theId integer
    
    declare @sql nvarchar(800)
    
    set @theId = null
    
    declare @MinID int = isnull(@theID,0)
    
    declare @MaxID int = isnull(@theID,convert(int, 0x7fffffff))
    
    
    
    
    
    
    
    select top 5000 x.*
    
    from dbo.someTable x
    
     where x.record_Id >= @MinID and x.record_Id <= @MaxID
    
    
    
    

     

     


    Yes, thank you for picking me up, Rami.  This is intuitive -- way better.

    :)

     

    Wednesday, September 29, 2010 3:02 PM
    Moderator
  • a further simplication.. No need of 2 variables also now... :)

    declare @TheID int = null
    select top 5000 x.*
    from dbo.someTable x
     where x.record_Id >= isnull(@TheID,0) and x.record_Id <= isnull(@TheID,convert(int, 0x7fffffff))
    

    Wednesday, September 29, 2010 3:06 PM
  • I think it looks pretty good.  Is there a reason you switched from the BETWEEN operator to inequalties?
    Wednesday, September 29, 2010 3:31 PM
    Moderator
  • No..... It came as part of copy process... :).... But i dont think there will be any difference btn those.... I think optimizer should treat as same.
    Wednesday, September 29, 2010 3:33 PM
  • Yeah, that is what I thought also. The key to all of this is enforcing positive values on the ID column.  Do you see any problem with the check constraint that I put on the ID column:

    check (record_Id > 0),

    ??

    Wednesday, September 29, 2010 3:41 PM
    Moderator
  • I think this constraint is not required.... I removed that constraint and i changed the query like below... seems to be working as expected in results and perf. both...

     

    declare @TheID int = null
    select top 5000 x.*
    from dbo.someTable x
     where x.record_Id >= isnull(@TheID,( -1 * convert(int, 0x7fffffff) )-1) and x.record_Id <= isnull(@TheID,convert(int, 0x7fffffff))
     
     
     
     
    

     

    Wednesday, September 29, 2010 4:46 PM
  • I think that idea was proposed by Alejandro Mesa a while ago. I read it in one of the threads a month or so ago.
    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 5:16 PM
    Moderator
  • I think this constraint is not required.... I removed that constraint and i changed the query like below... seems to be working as expected in results and perf. both...

     

    declare @TheID int = null
    
    select top 5000 x.*
    
    from dbo.someTable x
    
     where x.record_Id >= isnull(@TheID,( -1 * convert(int, 0x7fffffff) )-1) and x.record_Id <= isnull(@TheID,convert(int, 0x7fffffff))
    
     
    
     
    
     
    
     
    
    

     


    The lower boundary condition can be further simplified to:

    where x.record_Id >= isnull(@TheID, convert(int, 0x80000000)) 
     and x.record_Id <= isnull(@TheID,convert(int, 0x7fffffff))
    
    
    

     

    Wednesday, September 29, 2010 5:53 PM
    Moderator
  • I think that idea was proposed by Alejandro Mesa a while ago. I read it in one of the threads a month or so ago.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    Thank you, Naomi.  I will dig around and see if I can find that reference.
    Wednesday, September 29, 2010 5:54 PM
    Moderator
  • Yes......As I dont know how the sql stores negative nos, I did using math... now i got it...

     

    it starts from 0(0x00000000) to 2147483647(0x7fffffff) and - 2147483648(0x80000000) to -1(0xffffffff)

     

    Nice way of storing....... In my studies i saw the way of using a bit seperately for sign information only..........

    • Edited by ramireddy Wednesday, September 29, 2010 6:09 PM
    Wednesday, September 29, 2010 5:59 PM
  • Wednesday, September 29, 2010 6:06 PM
    Moderator
  • You need to keep in mind, that this solution is great when we don't have NULLs in the int column. If we do, this solution is not going to select them. Relevant thread in another forum

    tek-tips thread demonstrating that this solution doesn't work with NULLs in the column


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

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, December 16, 2010 8:29 PM
    Moderator