Whenever I try to match records (e.g. get at list of x thousand ID's in SPSS or .csv format and am told to match with an existing table), I import these ID's into a separate table and do something similar to

SELECT *
FROM <table> t
WHERE EXISTS(select null from <temporarytable> tt where t.id = tt.id)

or, if ID is unique in <temporarytable>

SELECT t.*
FROM <table> t
JOIN temporarytable tt on t.id = tt.id

I should have said that this is how I would have done it, since I normally do not use temporary tables in our main database. Rather, I import the ID's into a permanent table in a different database, and use a program that "joins" the two databases. "Joins" is in double quotes since it actually looks up each record from one of the databases in the other, something that is considerably slower than having all things in the same database (my matches are done once, not regularly, so if matching takes 5 hours (five hours means hundreds of thousands of ID's), that only means that I should let my program run while I'm relaxing at home). QLI - an ancient part of InterBase/Firebird - also supports joining between separate databases, but I haven't seen much documentation on how to use it and don't know whether it supports newer constructs.

I would assume that creating an EXTERNAL TABLE and use the JOIN I described above, would be amongst the quickest ways to get your data, but I don't use those kind of tables and do not know for certain. Also, if your list of records is limited to a few thousand, using Arno's suggestion could be OK (I would assume that Firebird didn't try to use any index on <table>.ID if it had to repeat it several thousand times, so if the table contains hundreds of millions of records, it could be slow).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of partsi
Sent: 3. juli 2008 09:34
To: firebird-support@yahoogroups.com
Subject: [firebird-support] The known limit (1499) in IN clause

Firebird does not support more than 1499 elements in an IN clause
list. Is there any effort to raise this limit? I understand that we
must try to avoid cases where the number of elements in an IN list is
big, but there are circumstances such as DSQL (e.g. using DSQL in a
c++ code) where we are faced with this limit. For example, an
application receives 2000 identifiers and it must perform a query to
get records identified by these identifiers.

So, what is the recommended workaround for this limit (e.g. in terms
of performance)?

Alternatives:

A) Store IDs into a temporary table and use that table in an IN
clause.

SELECT *
FROM <table>
WHERE ID IN ( SELECT ID FROM <temporary_table> )

B) Execute multiple queries, each having maximally 1499 elements in
an IN list, and then combine the results.

SELECT *
FROM <table>
WHERE ID IN ( 1, 2, 3, ..., 1499 )

combined with

SELECT *
FROM <table>
WHERE ID IN ( 1500, 1501, 1502, ..., 2998 )

Other solutions?

Thanks in advance.

Timo