Re: performance of IN vs ORs

} Subject: performance of IN vs ORs } To: informix-list@vprp333q.prop.telecom.com.au } Date: Tue, 13 Dec 1994 15:05:56 +1100 (AEDT) } From: Ian Timms <itimms@vprp333q.prop.telecom.com.au> } } Does anyone have anything to say with regard to the performance } of using IN as opposed to a group of ORs under Informix SE 4.12? } } ie. WHERE COL1 IN ( var1, var2, var3 ) } vs } WHERE COL1 = var1 } OR COL1 = var2 } OR COL3 = var3 } } and also with regard to } } WHERE COL1 NOT IN ( var1, var2, var3 ) } vs } WHERE COL1 <> var1 } AND COL1 <> var2 } AND COL1 <> var3 } } I know the implications with regard to DB2 should I presume a } similar situation for Informix? } } Cheers, Ian. } } %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% } % Ian Timms (DBA/Administrator) % Disclaimer: All opinions expressed % } % itimms@vprp333q.prop.telecom.com.au % herein are mine and mine alone! % } % Ph. 61+3+634-9144 % ==__( pretty piccy goes here )__== % } %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% In my experience, Informix handles ORs slowly. I would definitely recommend using the IN construct. I have not used the NOT IN with a small list. In general, I recommend *against* using NOT IN with a subselect, but with a short list such as yours I could not say for certain. MY guess (only a guess) is that the NOT IN and multiple AND ... <> ... would have similar performance. I would appreciate knowing what the DB2 implications are, via either e-mail or the newsgroup. Regards, Alan +---------------------------+-----------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, SLS | Voice: 303-977-9998 | | P.O. Box 179, M/S 3810 | Standard disclaimers apply. Cutesy ones, too. | | Denver, CO 80201-0179 USA | Your mileage may vary. Void where prohibited. | +---------------------------+-----------------------------------------------+