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

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.