[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