SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Faster BETWEEN Dates


A Faster BETWEEN Dates

Author
Message
terrance.steadman
terrance.steadman
Mr or Mrs. 500
Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)Mr or Mrs. 500 (563 reputation)

Group: General Forum Members
Points: 563 Visits: 538
Comments posted to this topic are about the item A Faster BETWEEN Dates
Geoff Johns
Geoff Johns
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 169
Novel approach !

It would also be interesting to compare these two approaches using the Calendar table, but with an Integer PK (as per a data warehouse DIM table), then in the FACT table carry this Integer as a FK.

Well done & kind regards
Geoff Johns
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18921 Visits: 12426
Hi Terry,

Thanks for the article.

It's definitely an interesting approach - but I miss an explanation. WHY does the second version run faster than the first? I would have expected the reverse, but your measurements disagree - there really should have been a paragraph or two to explain this in your article.

I tried to verify your findings on my computer, and I found that each version ran in subsecond duration; your tests must have been done on much older hardware, or on much larger tables. I'm a bit short for time now, but I plan (if I find the time) to do some more experiments, maybe on larger tables. For now, I can only say that the execution plans do not support your observation that the second query is faster, and that the execution times are too short to be sure.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SQLZ
SQLZ
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3134 Visits: 940
Hi Terry,

You begin by saying "Finding records that exist between two dates can sometimes be slow". In my experience this has never been true when the table in question is properly indexed.

That aside however, as with Hugo's observation, I see no difference between your final two statements. The execution plans are identical.

Instead of using a table variable (which I would never use for large data sets anyway) try creating a temp table and create suitable indexes on the date fields.

You could then completely avoid having to create a calender table and directly query the one table instead with a simple between statement in the where clause that should be even faster.

Karl
source control for SQL Server
admin-499013
admin-499013
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 114
Hello Terry

Afraid I see the same results as Hugo. The elapsed time for the second approach seems much higher and far more records are read. Looking at the plan the problem seems to be that SQL Server is generating an intermediate table and then scanning it for each candidate record in a nested loop. Its a sub optimal plan probably resulting from the relatively small number of records in the driving table.

Interesting approach though, thanks for the article.

Pete
GPO
GPO
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1738 Visits: 1938
As a slight aside, I have to do this sort of a lot at the moment, and it's common to have tables with start_date_time and end_date_time columns. You'll often want to select all the records that were extant at a point in time. In other words if you wanted to retrieve all the events that had started but not finished on 1 Jan 2010 you might go (untested freehand follows):

select blah
from events ev
where ev.start_date_time <= '20100101'
and (ev.end_date_time > '20100101' or ev.end_date_time is null)



similarly to find all events that overlapped with a period of time, you might go:
select blah
from events ev
where ev.start_date_time <= '20100701'
and (ev.end_date_time > '20100101' or ev.end_date_time is null)




If it was your job to design these tables, would you be tempted to have an end_date_time column that could not be null, and instead substitute a ridiculous future date for all events that have not yet ended (like 22000101)? Are there indexing or other performance advantages to making your end_date_time column not null?

:-)

One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important.
Bertrand Russell

wildh
wildh
SSC Eights!
SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)SSC Eights! (978 reputation)

Group: General Forum Members
Points: 978 Visits: 675
I'd have to agree with earlier posts. In the set up we have here I dont see any real beneifts. I've tried on a larger set of data but it could be our hardware set up that is hiding the gains you see. It would be interesting to know your set up and a bit more detail on results you got.



mdrost77
mdrost77
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 18
Hi Terry,

It was indeed very interesting as an aproach. We tried it but for larger volumes, Our result; the 'between'-statement was 7 times faster (we are running on mssql 2008)

Still any try that can make a faster between I will always look at with interest.

thanks for the demo

best regards

Marcel
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7555 Visits: 3399
Hello, terrance
May you explain this syntax?
I think it is very dangerous to transfer search condition in the WHERE clause,
because you can alter result of "LEFT JOIN" that becomes a pure JOIN.

FROM @WorkTable AS wt
LEFT JOIN @CalTable AS ct ON
wt.Work_Start <= ct.CalDate
WHERE
(
CASE
WHEN ct.CalDate <= wt.Work_End THEN 1
ELSE 0
END
) = 1



I run on tuttopodismo
abradley-908168
abradley-908168
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 54
doesnt it defeat the purpose of a 4th generation language (specify desired results, not exact method), if you have to use such non-intuitive different ways of specifying the query in order to gain speed: if you cant trust the 4gl, might as well use a 3rd generation language?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search