RE: Multiple queries into a single temp table
Jay Hannah wrote:
> Alexander V.Didytch wrote:
> > Jay Hannah wrote:
> > > Here are a couple solutions for you:
> > >
> > > 1) Use the UNION operator:
> > > select id as col1, dealer as col2 from dealer
> > > UNION -- (or UNION ALL)
> > > select id as col1, manufacturer as col2 from manufacturer
> > > into temp dlr_manu_tbl;> > >
> > > 2) Create the temporary table as a seperate step:
> > > create TEMP table dlr_manu_tbl (col1 int, col2 =varchar(40));
> > > insert into dlr_manu_tbl
> > > select id as col1, dealer as col2 from dealer;
> > > insert into dlr_manu_tbl
> > > select id as col1, manufacturer as col2 from =manufacturer;
> > >
> >
> > Could someone tell me about performance of each approach ?.
> > Which solution is more preferable with terrific amount of data in =
each select
>
> My completely uneducated guess would be that with very large amounts of
> data, the second method may be faster since it ties up less memory per
> transaction...
>
> If I were you, I'd just run them both and see which one completes =
faster
> (but I'm goofy like that).
>
> Jay
To toss in my $0.02, I'd say there is almost no difference between the =
second method and the UNION ALL, unless parallelism is available to you, =
in which case the UNION ALL would be my choice. There's no guarantee =
that both halves of the query would run simultaneously, but it can't =
hurt.
In any case, a simple UNION will be much slower, since the results have =
to be sorted and UNIQUE-d.
cheers
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+