--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> --- 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.

Not at all - the situation is similar to what Adam describes - the
list we start with (and the PK's of the rows) may not be contiguous.
The user also has the opportunity to re-order the list in several
different ways which will effectively shuffle the PK's.

> 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.

Because of the issue above (a non-contiguous list of PK's), once the
selection is known I suppose we could examine the list for sequences
of contiguous PK's......
Hmmmm...