--- In firebird-support@yahoogroups.com, "phil_hhn" wrote:

> So instead of using the temp table, she then used multiple IN

clauses:

> "...IN (1, 2, 3, ...1000) OR IN (1001, 1002, 1003, ...2000) OR
> IN ... OR IN (4001, 4002, 4003, ...5000)"
>
> She found that although the length of the query is horrendous,
> having one query /does/ work and overall it is significantly faster
> than putting the selected rows into a temp table and running the
> query with the join to the temp table.
>
> It seems creating/inserting the data into the temp table is what
> kills it (and yes, a prepared statement was used).
>
> Should this be so??

I don't know.

However, I know that people are unlikely to go through 10000 records
and carefully select 5000 of them randomly distributed, which is what
your generated SQL seems to assume. Put a little bit more intelligence
into your SQL generator and you would - in your case - end up with
BETWEEN 1 AND 5000 rather than the excessively long IN clause.
Combining [NOT] [IN | BETWEEN] depending on user selection and ANDing
each refinement should not be overly complicated (knowing line number
and sorting order would tell you whether the user (un)selected one or
a group of records) and the resulting SQL could be much easier to
handle (at least within the same transaction).

HTH,
Set