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


A Faster BETWEEN Dates


A Faster BETWEEN Dates

Author
Message
happycat59
happycat59
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8401 Visits: 3281
Michael Ebaya (11/3/2010)
happycat59 (11/3/2010)
Not only is the original article of interest (and it is great to have someone prepared to write about their findings...thanks Terry)
Does no one actually care the entire article is wrong, top to bottom?


The reason for my interest is not just the original post. The discussion that it has generated really does show how much interest there is in this topic. Yes, there have been concerns expressed about whether the OP's original solution is equivalent to the original code. The fact that there are so many replies that have corrected the error or, at least, pointed it out means that I am not concerned. It has made people think and that is more important than anything else



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: 18403 Visits: 12426
sql.monkey (11/4/2010)
I have a table with an unindexed date column in a table of billions of rows
I collect the lowest and highest primary keys between those dates into variables

I set two variables

declare @min int
declare @max int

select @min = min(primarykey) where datecol => 'begindate'
select @max= max(primarykey) where datecol <= 'enddate'

select primarykey, datecol, x,y,z from table where primarykey between @min and @max

works for me

You'll get a syntax error - no FROM clause in the two queries that set @min and @max.

After fixing that, if the datecol column is indeed unindexed, you get two complete table scans for setting the @min and @max variables. You can reduce that to one scan by using
SELECT @min = min(primarykey), @max=max(primarykey)
FROM table
WHERE datecol BETWEEN @begindate AND @enddate;

But it's still a scan. The same scan you would get if you throws away all the unnecessary logic and use
SELECT primarykey, datecol, x,y,z
FROM table
WHERE datecol BETWEEN @begindate AND @enddate;

If you check the execution plan for your query, you will probably find that is uses an index on the datecol column that you had forgotten existed.

And the objection posted by GPO is valid as well - this (useless) technique only gives the correct results if ascending key order and ascending datecol order match up completely. Which is probably only the case if one column is an IDENTITY and the other has a DEFAULT(CURRENT_TIMESTAMP) and is never ever manually changed.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
james_luetkehoelter
james_luetkehoelter
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 229
I just tried this technique on a moderate table size - BETWEEN was around twice as fast. I have a feeling that the improvement of BETWEEN would scale as the data does (indexing and statistics come in to play as well). I also concur with Hugo's statements (imagine that Hugo, we agree!).
Chanson 54862
Chanson 54862
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 22
Wouldn't you be better off using temporary tables rather than table variables, i was under the impression I should only use table variables for very small (500 records) amounts of data?

Sorry I have read more replies and see that table variables were being used as examples and other replies have covered the table variables angle

Chris
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