[thelist] SQL: BETWEEN versus (Greater Than and Less Than)
Anthony Baratta
anthony at baratta.com
Thu Mar 22 19:22:48 CDT 2007
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 !
>
More information about the thelist
mailing list