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

Jason Handby jason.handby at corestar.co.uk
Thu Mar 22 19:42:23 CDT 2007

Hi Anthony,

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

You're right of course; I was just pleased that I could offer
*something* :-)

> 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.

Just thinking out loud at you, in case it helps...

Do you have the live and test databases on the same physical server? If
they're on different servers, do those servers have the same
configuration? The same date and time settings? Are they in the same
time zone?

Assuming you're in the US, did this start happening when you moved over
to Daylight Saving Time? I know this happened a couple of weeks early
this year, and I wonder if that's relevant.

One more thought -- have you tried running DBCC CHECKDB to see if you
have any as-yet-undiagnosed problems with your live database? I'm sure I
had a problem once where something was unexpectedly taking an age, and
it turned out that the database had a tiny problem somewhere which
showed up when I ran DBCC CHECKDB.

I'm really intrigued to know what the problem is now :-)


More information about the thelist mailing list