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
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 538
Comments posted to this topic are about the item A Faster BETWEEN Dates
Geoff Johns
Geoff Johns
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 167
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10858 Visits: 11974
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1248 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
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1288 Visits: 1927
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-Addicted
SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)SSC-Addicted (410 reputation)

Group: General Forum Members
Points: 410 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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4866 Visits: 3326
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
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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