Efficiency and usage of IN operator

From: Alexander Staubo <earlybird_at_mop.no>
Date: Sun, 30 May 1999 23:24:13 +0200
Message-ID: <MPG.11bbcae494dced73989681@news.mop.no>

[I'm using MS SQL Server, although I'm interested in information about servers (eg. Oracle, DB2) as well.]

Given a statement such as...

select ids from customers
where parent_ids in (35, 203, 495, 502, 945)

...how efficient is such a query compared to say, using a separate OR test for each value ("parent_id = 35 or parent_id = 203 or ...")?

According to the Transact SQL documentation, "If there are 16 or more values in an IN predicate, the values are sorted and binary search is used to evaluate the predicate. This can result in limiting the overall number of comparisons performed".

This last sentence is ambiguous: Does it mean the IN predicate becomes more _efficient_ because a binary search is used, or does it mean the query will return _fewer_ rows? From this description it sounds like 16 is a hard limit on the number of viable comparisons for an IN predicate.

I'm concerned about speed more than anything here. Reducing the number of index comparisons is important.

--
Alexander Staubo http://www.mop.no/~alex/ "It has taken the planet Earth 4.5 billion years to discover it is 4.5 billion years old." --George Wald Received on Sun May 30 1999 - 16:24:13 CDT