In() versus Or

[ordendelfai]

Just a quick question. If I have 4 criteria's for a single field, and am trying to determine what function to use,
In( ) or OR.

For example:

="A" or "B" or "C" or "D"
Versus
In("A","B","C","D")

Which one is faster, and why?

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[PHV]

I personally prefer the In (...) versus the Or, as often access is confused with the Or stuff when in Query Design View.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

I would guess the OR is quicker. IN can refer to all sorts of things like another subselect so I imagine it has a higher overhead when being parsed. Then, once it is compiled, it is less likely to be SARGable. I've never seen anything (and I've looked quite hard) that tells you how Jet works but you would imagine it splits selection between the first pass of the data and subsequent processing. A series of ORs should be easiliy processable whereas IN requires optmisation to spot it nets to the same set of ORs.

Having said that, one of the key features of the original relational model is that you should think only about the logic of the query not its execution. Not only should this burden be taken by the optmiser but you should have no direct control on how things are done, so that optimiser can change its mind, the DBA add efficiency factors eg indexes, or the underlying tables be changed (split or merged).

So, for total purity of mind and spirit, choose the one you find most elegant. Only change it if the thing runs too slowly.

 

[ordendelfai]

Thanks for the feedback, I researched further and found if I am performing a simple "OR", to use a union query instead of OR for a large performance boost. For many OR's, I'll just stick with IN( ), seems to works ok for me ;-)

~Joel
As common courtesy and to help other members who might find your thread helpful in the future, please respond if the advice given resolved your issue.

 

[Aqif]

Hi,

I will agree with Mike. IN criteria does takes up more resources specially with large data sets. Not sure whats the accurate reason for that.



Cheers!
ÙÇãá

It's important to learn the rules so that you know how to break them.