Re: Multiple queries into a single temp table

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 ? And the definitive (IMO) answer is::::: It depends: Let's look at pros and cons of each approach: Union: pro: It is a single query pro: It will eliminate duplicate rows con: It must sort the data in order to eliminate duplicate rows con: If you need to order the data differently than that needed to eliminate duplicate rows (the union orders by col1, col2, etc..) this incurs an additional sort. Repeated SELECTs into TEMP table: pro: It does not automatically sort rows, allowing you to get the data faster if sorting is not required. pro: If you DO require your own ordering scheme, you order once - when selecting from the temp table, bypassing the elimination sort. con: Duplicate rows are not automatically eliminated. (If you select unique form the temp table and then order your own way, you have incurred the extra sort already. Temp table allows you the choice.) In my empirical observations, most apps use an order-by in their queries. SO: If your queries have common rows, your better choice is probably the union. Might as well let the engine do the work to get rid of duplicate rows. If you know that your queries' active sets are mutually exclusive, you should opt for the temp table approach, ordering the data as you blessed well please. > SY, Alexander Alex, please don't sigh; it's a depressing sound. ;-) > If You want to feel OK -- INSTALL Your Windows Every Day ! No, you've got it wrong, Alex. If you feel so OK that you feel no pain, Install your Windows again and again. -- -- Jake (Pondering the color of an asphyxiated smurf) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+