Subject select statement with "or" or "in" clause
Author Ulrich
Firebird 2 with VB6 & ODBC


my question is, whether inside a select statement the "in()" or the
"or" clause should be used. So far I readed, that only few arguements
for the "in()" clause is ok, but the performance will go down for many
arguments. In my actual application a customers wish is, to filter
products by their names and show all with this products associated
events. That means, if there are approx 2000-3000 products and the
filter result is 100-200 products, I have to use this 100-200 Ids for
filtering the associated events.

I have the primary index on the PRODUCT_ID field with type BigInt, and
an index on the field 'Name' which is the name of the product.

So, actual I know three possible select statements :

a) select a,b,c from product_event where PRODUCT_ID in (ID1,ID2,ID3,....)

b) select a,b,c from product_event where PRODUCT_ID in (select ID from
products where NAME starting with 'searchstring' order by NAME) order
by ID

c) select a,b,c from product_events where PRODUCT_ID=xx or
PRODUCT_ID=xx or PRODUCT_ID=xx .....

Are there more (better) possibilities?

With best regards - Ulrich Groffy