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

Collapse
Posted by Dirk Gomez on
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
Posted by Janine Ohmer on
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!

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.