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

Tab Alleman talleman at Lumpsum.com
Fri Mar 23 09:29:45 CDT 2007


Maybe your production server has gotten a lousy query plan cached somehow.   I've always been a big fan of BETWEEN, and have never had performance issues with it.

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org
> [mailto:thelist-bounces at lists.evolt.org]On Behalf Of Anthony Baratta
> Sent: Thursday, March 22, 2007 8:23 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL: BETWEEN versus (Greater Than and 
> Less Than)
> 
> 
> 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 ! 
> > 
> -- 
> 
> * * 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 ! 
> 



More information about the thelist mailing list