Subject | Re: The known limit (1499) in IN clause |
---|---|
Author | partsi |
Post date | 2008-07-03T09:27:44Z |
Thanks to all for your comments and suggestions.
Our server application which is connecting to a data store (Firebird)
receives these identifiers through RPC. On the client side, a user
selects come items such as customers or projects in order to perform
some action on these items and our client software transfers the IDs
of these items to the server that eventually performs the desired
action e.g. deletes the items.
Based on your comments, I think the best way is using a global
temporary table as a workaround for the IN limitation. After I haved
populated the temporary table with a context ID and the IDs of the
items, I can identify the items in SQL queries with no limits no
matter how many items are involved or what operation needs to be
performed (e.g. update or delete).
I can also use Arnos's suggestion (ORed IN clauses) when I have
guarantees that the SQL text limit is not exceeded and the query
executes fast.
For these kinds of needs, it would be good to support pure in-memory
based temporary tables. Even though forced writes are off for global
temporary tables and there is a fast cleanup mechanism for their
contents, doing these things purely in memory would be the best
choice. Can someone tell if Firebird supports this feature in the
future?
Timo
Our server application which is connecting to a data store (Firebird)
receives these identifiers through RPC. On the client side, a user
selects come items such as customers or projects in order to perform
some action on these items and our client software transfers the IDs
of these items to the server that eventually performs the desired
action e.g. deletes the items.
Based on your comments, I think the best way is using a global
temporary table as a workaround for the IN limitation. After I haved
populated the temporary table with a context ID and the IDs of the
items, I can identify the items in SQL queries with no limits no
matter how many items are involved or what operation needs to be
performed (e.g. update or delete).
I can also use Arnos's suggestion (ORed IN clauses) when I have
guarantees that the SQL text limit is not exceeded and the query
executes fast.
For these kinds of needs, it would be good to support pure in-memory
based temporary tables. Even though forced writes are off for global
temporary tables and there is a fast cleanup mechanism for their
contents, doing these things purely in memory would be the best
choice. Can someone tell if Firebird supports this feature in the
future?
Timo