Subject Re: IN clause, help.
Author Adam
--- In, "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.


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

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.


select * from Documents where
DocumentID in (1,2,3,....,1499)
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.