CODEWITHSUNDEEP
sqlfirebird
user3193035
user3193035

Reputation: 1

Firebird query takes unusually long time to process

I am executing following query which contains 3000 conditions inside in operator. 

select * from Orders a where a.key 
 in(101,155,16,45,778,9,8,67,1,44,455,56,88,888,8888,88888,77777,77,78, 3000)

Number of numeric values inside in is 3000. This query is working very slow and sometimes it throws an exception. Is there any other way to write this query.

Upvotes: 0

Views: 702

Answers (3)

Mark Rotteveel
Mark Rotteveel

Reputation: 108941

You can solve this problem by creating a global temporary table and inserting the values into the GTT. You only need to create it once and can reuse it for all your queries afterwards. It will be automatically emptied at the end of the transaction, and values are only visible to your current connection.

For example:

create global temporary table temp_keys (
   keyvalue integer primary key
)

Note that I added primary key because you don't need duplicates and it also creates an index.

Then you can populate it with values and use it in a query.

select * 
from Orders a
inner join temp_keys b on b.keyvalue = a.key

You need to do all this in the same transaction, otherwise you need to create the GTT as on commit preserve rows which will keep the values until your connection is closed.

This might not always be faster (because of the overhead of having to insert the keys into the GTT first).

As to the error you occasionally get, this is because the number of items in the IN clause has a maximum of 1500 items, see Predicates, IN in the Firebird 2.5 language reference.

Upvotes: 2

Rudolf Grauberger
Rudolf Grauberger

Reputation: 198

Here is the link to the limit to the number of elements in an IN clause mentioned by Mark

http://www.firebirdfaq.org/faq175/ (second point)

Regarding the poor performance, I have the following questions:

  1. Did you have an index on the 'key' column?
  2. Change the data in the table frequently?

Upvotes: 0

Ben J. Boyle
Ben J. Boyle

Reputation: 306

You could perhaps use a temporary table, load your keys into the table, and join it to the orders in your query.

Upvotes: 1

Related Questions