--- In firebird-support@yahoogroups.com, "William Gonzáles S."
<wgonzaless@...> wrote:

>
> Hello all.
> I have a query with IN clause and always it has many
> elements:
>
> select * from DOCUMENTS where DOCUMENT_ID in (
> '1000',
> '1001',
> '1002',
> '3001',
> '3002'
> ...
> --An so on, this could has 1500 entries.
> )
>
> DOCUMENT_ID is PK and DOCUMENTS table has 25000
> records, also I have this query inside a loop,
> becasuse the IN clause limitation (1500 in FB 1.5) so,
> sometimes this query executes more than 1 time; I am
> thinking to change it: reading all table and compare
> DOCUMENT_ID iterating in each record.
> What option is the best? (I need more speed in
> queries) can I keep the IN clause?
>
> FB 2 has this IN clause limitation ?

Each member inside an IN clause is a separate indexed read. If
possible, you should try to substitute is for a between. In your
client side logic. Each clause is an index hit rather than each member.

eg

select * from DOCUMENTS where
(
(Document_ID>=1000 and Document_ID<=1002)
OR
(DocumentID>=3001 and Document_ID<=3002)
OR
(....)
)

The IN clause is limited to 1499 because of an internal limit that I
believe is still a problem in FB 2.

You can OR two IN clauses together if you have to.

eg.

select * from Documents where
(
DocumentID in (1,2,3,....,1499)
OR
DocumentID in (1500,1501,1502,...)
)

Apart from exceeding the limit of the plan size which will prevent a
PLAN from being returned, this sort of logic will work up until your
query reaches the maximum query size of 64KB.

Adam