Subject How to handle a large 'IN' clause?
Author phil_hhn
Hi, I need a solution to the following.

In our system we provide the ability for our users to view all the
records in a table. The records are displayed in a list and the user
can sort them, re-order them, etc., and scroll up & down at will.
Because we need to let them move up & down, we read all the data into
a list model (firebird doesn't support fully scrollable cursors,
right? please correct me if I'm wrong).

Here's the good bit. The users can select rows in the list and choose
to omit them from the list, or redisplay the list with only those
selected. (This is useful for various reasons, one being that they can
then print the modified list as it appears on the screen.)
When they make such a selection we simply get the data again with the
condition "... where pk in (aa, bb, cc, dd, ...)". In some ways this
is heaps simpler than keeping the original list and hiding certain
rows, etc.

The problem arises when the list is something like 10,000 records
long. If the user goes to the middle of the table and selects half of
them and calls either 'omit' or 'show' those rows, we run a query
which has a huge 'in' clause. We get the error:

SQL error code -901
Implementation limit exceeded
too many values (more than 1500) in member list to match against

So how should we go about handling this? One solution may be to block
the users in the interface...

I'm also curious about efficiency with a large (but within capability)
'in' clause (although this doesn't seem to make a significant impact).