March 16, 2020 at 7:44 pm
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
March 16, 2020 at 8:20 pm
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
Change is inevitable... Change for the better is not.
March 16, 2020 at 8:23 pm
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
Change is inevitable... Change for the better is not.
March 16, 2020 at 8:27 pm
Jeff, I think your bracket should be after 'AND', not before it.
March 16, 2020 at 9:10 pm
Jeff, I think your bracket should be after 'AND', not before it.
Thanks for the catch, Phil. Corrected it.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2020 at 9:13 pm
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
Change is inevitable... Change for the better is not.
March 17, 2020 at 7:05 am
Many thanks Gentlemens!
March 17, 2020 at 12:34 pm
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy