Forum OpenACS Q&A: Best way to handle queries with a large where clause?

Collapse
1: Best way to handle queries with a large where clause?
Posted by Janine Ohmer on 04/17/03 02:27 PM
I have a query of the "where x is in (a, b, c)" variety where the list has over 1000 items in it.  Oracle does not allow this.  My workaround has been to run the query mulitiple times with 1000 item chunks, but the result is pretty slow and seems a bit primitive.  Is there a better way?  Thanks in advance!
Collapse
2: Re: Best way to handle queries with a large where clause? (response to 1)
Posted by Peter Alberer on 04/17/03 02:49 PM
I have not tried that solution but what about the creation of a temp table and a join between the temp table and the rest of the tables in your query? You could create a temp table like that:

create query_temp_table (
query_id integer primary key,
query_value integer/varchar
);

insert the 1000 values into query_temp_table using a certain query_id for each run. this would make it unnecessary to create/drop tables, you could just get a new query_id from a sequence and wrap the insert in a transaction.

Collapse
3: Re: Best way to handle queries with a large where clause? (response to 1)
Posted by Dirk Gomez on 04/17/03 02:53 PM
Is this Postgres or Oracle?

If Oracle then use "real temporary tables", not a self-maintained one.

Otherwise if your in clause has more than a few items, something is seriously wrong with your approach. You should - as Peter pointed out - join with another table.

Collapse
4: Re: Best way to handle queries with a large where clause? (response to 3)
Posted by Janine Ohmer on 04/17/03 02:59 PM
Well, it isn't my code, I'm just helping to maintain it while the author is on vacation, and I don't want to go rearchitecting the whole thing for him while he's gone. ;)

A temporary table is a good idea, though;  I hadn't thought of that.  Thanks!

Collapse
5: Re: Best way to handle queries with a large where clause? (response to 4)
Posted by Tilmann Singer on 04/17/03 04:21 PM
And a 'where exists (select 1 from tmptable ...)' is propably faster than a join, because it will stop processing as soon as it found a value, and maybe even for other reasons. Maybe someone with more knowledge than me wants to elaborate on that - would be appreciated.
Collapse
6: Re: Best way to handle queries with a large where clause? (response to 2)
Posted by Peter Alberer on 04/17/03 06:19 PM
what a shame, i posted a flawed sql statement for the temp table proposal. of course that should not be a primary key constraint for the first column :)