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

Question

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

aureliu

SSC-Addicted

Points: 441
More actions
March 16, 2020 at 7:44 pm

#3734841

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

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

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

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)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

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.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

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)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

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)

aureliu SSC-Addicted Points: 441 More actions · Answer 7

aureliu

SSC-Addicted

Points: 441

March 17, 2020 at 7:05 am

#3734908

Many thanks Gentlemens!

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 8

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