[thelist] SQL: BETWEEN versus (Greater Than and Less Than)

[Anthony Baratta]

Granted the tweak is not the best, but even with it's inefficiencies it's 100 million times faster than using the between statement.

That's the $64K Question. Why does between take so long to return. I can remove the between statement from the where clause and return 2K rows in less than a second. When I keep it in, it takes 1.5 minutes to return 135 rows. Go figure.

I just double checked the speed times between copies of our live and test DBs. Live sucks when it uses Between, test has no issues. I did an SQL Compare (Red-Gat3e Tool) between the two environments and can not find a difference between the target DBs.

Argh.

-----Original message-----
From: "Jason Handby" jason.handby at corestar.co.uk
Date: Thu, 22 Mar 2007 17:02:02 -0700
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL: BETWEEN versus (Greater Than and Less Than)

> I wrote:
> 
> 
> > > convert(char(8), create_dt, 112)  >= '20070319' and 
> > > convert(char(8), create_dt, 112)  <= '20070319'
> > 
> > 
> > Didn't you mean to write
> > 
> >   convert(char(8), create_dt, 112)  >= '20070319' and 
> >     convert(char(8), create_dt, 112)  < '20070320'
> > 
> > 
> > ...?
> 
> 
> Actually that's a bit long-winded -- you can just write
> 
>    convert(char(8), create_dt, 112)  = '20070319'
> 
> But this will mean calling convert for every row of your table, in order
> to generate a value that can be compared to your date string.
> 
> 
> I'm not Rudy or Ken, but I bet it's a lot more efficient to do it the
> other way around, so that the conversion only has to happen once for the
> entire result set and so that you can make use of any index you might
> have on the date column:
> 
>     create_dt >= convert(DATETIME, '20070319') AND create_dt <
> convert(DATETIME, '20070320')
> 
> 
> 
> Jason
> -- 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
> 
> For unsubscribe and other options, including the Tip Harvester 
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt ! 
>