Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

A Faster BETWEEN Dates Expand / Collapse
Author
Message
Posted Saturday, October 30, 2010 11:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 176, Visits: 461
Comments posted to this topic are about the item A Faster BETWEEN Dates
Post #1013494
Posted Monday, November 1, 2010 12:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:32 AM
Points: 29, Visits: 121
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
Post #1013688
Posted Monday, November 1, 2010 2:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 3:56 PM
Points: 6,086, Visits: 8,354
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
Post #1013699
Posted Monday, November 1, 2010 2:43 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:39 AM
Points: 870, Visits: 911
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
Post #1013704
Posted Monday, November 1, 2010 2:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:02 PM
Points: 21, Visits: 108
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
Post #1013705
Posted Monday, November 1, 2010 3:25 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:33 AM
Points: 832, Visits: 1,593
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
Post #1013719
Posted Monday, November 1, 2010 3:31 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 24, 2014 1:38 AM
Points: 292, Visits: 662
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.


Post #1013722
Posted Monday, November 1, 2010 3:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 7:19 AM
Points: 4, 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


Post #1013729
Posted Monday, November 1, 2010 4:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 2,589, Visits: 2,444
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

Post #1013737
Posted Monday, November 1, 2010 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 21, 2011 5:24 AM
Points: 4, 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?
Post #1013741
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse