Re: many Union all vs. inserting results to temp table

<deann15_at_yahoo.com> wrote in message
news:1142981681.639856.314160_at_v46g2000cwv.googlegroups.com...
:I have a dynamic query which basically loop through an ID field in a
: table then build queries dynamically then union all the queries
: together, such as:
:
: select * from (complex subquery) where a = 1
: union all
: select * from (complex subquery) where a = 2
: union all
: select * from (complex subquery) where a = 3
: ...
:
: Depending on what a user select, the query could have a few thousand
: unions. My question is about scalability and performance: will it be
: better if inserting the results of every query to a global temp table
: then select * from the temptable instead of executing the long query
: all at once?
:
: Thanks!
:

if your generating the query (whch may or may not be the best approach) why not just generate a single query:

select * from (complex subquery) where a in (1,2,3... 9999999)

or add the 'a in (...)' predicate to the 'complex subquery'?

++ mcs Received on Tue Mar 21 2006 - 17:38:16 CST