Subject Re: select statement with "or" or "in" clause
Author stefanst

the best option in terms of performance is to use an additional table
with list of filtered IDs and a column with search id (from generator,
in order to enable simultaneus search) and then join the table with
your products table. Do not forget to clean up that table after search.


--- In, "Ulrich" <ulrich.groffy@...>
> Firebird 2 with VB6 & ODBC
> Hello,
> 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
> 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