need help with query having null value in end datate column is null

  • Hi,

     

    I have a table with > 1 mil rows ,and this query takes more time than wishing.

     

    select col1 from table where starttime > 'datetime' or endtime is null and endtime < 'datetime'.

     

    I need to speed up process  using a filter index,column store index or rerating the query> Thanks,Hadrian

  • Changes in RED.  Also, Do add an index but it should NOT be filtered in this case. Don't miss the "equals" sign I added and, yes, the parentheses are necessary.

     

    select col1 from table where starttime >= 'datetime' AND (endtime is null OR endtime < 'datetime')

    --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".

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

  • p.s. This is also why I default EndTime columns to '9999',  which is the same as 9999-01-01.  It means that I don't have to have an OR in my code.

     

    --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".

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

  • Jeff, I think your bracket should be after 'AND', not before it.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Jeff Moden wrote:

    p.s. This is also why I default EndTime columns to '9999',  which is the same as 9999-01-01.  It means that I don't have to have an OR in my code.

    +1000

    I use '9999-12-31' myself, but same principle.

  • Phil Parkin wrote:

    Jeff, I think your bracket should be after 'AND', not before it.

    Thanks for the catch, Phil.  Corrected it.

    --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".

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

  • Jason A. Long wrote:

    Jeff Moden wrote:

    p.s. This is also why I default EndTime columns to '9999',  which is the same as 9999-01-01.  It means that I don't have to have an OR in my code.

    +1000

    I use '9999-12-31' myself, but same principle.

    The reason I don't use that "bitter end date" is because I'll sometimes have code that adds a day, week, month, or quarter to an end date for purposes of finding the beginning of the next period for one reason or another.  Doing so would cause an "overflow" error if I used the "bitter end date".

    But, 9999-12-31 is a shedload better than NULL enddates. 😀

     

    --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".

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

  • Many thanks Gentlemens!

  • Jeff Moden wrote:

    The reason I don't use that "bitter end date" is because I'll sometimes have code that adds a day, week, month, or quarter to an end date for purposes of finding the beginning of the next period for one reason or another.  Doing so would cause an "overflow" error if I used the "bitter end date".

    But, 9999-12-31 is a shedload better than NULL enddates. 😀

    I can only think of two main scenarios where I would need to reference the EndDate:

    -- 1) I want to easily find the "current" row(s)... 
    WHERE t.EndDate = '9999-12-31';

    -- 2) I want to know what row(s) were current on some date in the past...
    WHERE
    t.BegDate <= x.SomeDate
    AND t.EndDate >= x.SomeDate;

    -- or --

    FROM
    dbo.SomeTable x
    JOIN dbo.TemporalTable t
    ON x.SomeDate >= t.BegDate
    AND x.SomeDate <= t.BegDate
    ...

    I can't recall ever needing to add a time period to the EndDate to find the next period. Of course I don't recall what I had for lunch two days ago, so take that for what it's worth...

Viewing 9 posts - 1 through 8 (of 8 total)

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