Using BETWEEN versus >= AND <=

[ZABADAK]

In Access 2000 in SQL is there any difference when comparing dates to the efficiency of?

WHERE MYDATE BETWEEN ... AND ...
and
WHERE MYDATE >= ... AND MYDATE <= ...

 

[LNBruno]

The BETWEEN...AND operator is treated differently in different databases:

1) BETWEEN..AND only selects fields that are between and excludes the test values.

2) BETWEEN..AND selects fields that are between and includes the test values.

3)BETWEEN..AND selects fields between the test values, including the first test value and excluding the last test value.

MS Access uses the 2nd (all inclusive) method.

< M!ke >

 

[ZABADAK]

Thanks for the response, unfortunately it does not throw any light on my question...

 

[LNBruno]

Sorry, you were looking for the EFFICIENCY difference between the two methods, not the actual values that would be included in them?

My guess is that, because the BETWEEN...AND is a single set of comparison factors and the >=...<= is a double set of comparison factors, the BETWEEN...AND would be more efficient.

Nanoseconds! Who knew they could add up so fast? ;-)


< M!ke >

 

[ZABADAK]

That seems like questionable logic to me. I was hoping someone would know.

 

[BNPMike]

It's very difficult to get information about how Jet works. Having been brought up on things like DB2 I find this frustrating. You get snippets on the Microsoft site but no complete picture.

Personally I wouldn't have thought there was any difference. I would assume Access would translate BETWEEN into >=/<= before it scanned the index or data pages.

 

[pbrodsky]

I would guess, and it's only a guess, that BETWEEN ... AND would be more efficient (if there's any difference) because it could be be optimized better than the separate >=, <= which could be any two conditions and which would have to be treated in a more general way.

 

[LNBruno]

That seems like questionable logic to him. He was hoping someone would know.

< M!ke >

 

[joebloeonthego]

why don't you just test it? run a zillion queries both ways and time it.

 

[SilentAiche]

Because he was hoping someone would know.

Tim

[blue]_____________________________________________________
If you need immediate assistance, please raise your hand.
If you are outside of Raleigh, raise your hand and say[/blue] [red]Ooh! Ooh![/red]