October 30, 2007 at 5:45 pm
A fellow developer asked me what is the difference between using the following statement as opposed to breaking up the union statement into a select into statement followed by an insert statement:
EXAMPLE 1:
SELECT * INTO MYTABLE FROM
(SELECT A,B,C FROM FIRSTTABLE
UNION
SELECT A,B,C FROM SECONDTABLE) X
EXAMPLE 2:
SELECT * INTO MYTABLE FROM FIRSTTABLE
INSERT INTO MYTABLE SELECT * FROM SECONDTABLE
What might be the major performance difference? Example 2 takes 25 minutes but Example 1 takes over 1 hour.
October 30, 2007 at 6:52 pm
UNION will combine dupilcate result sets to a single set.
UNION ALL will produce the same result as the second query and performance will be similar.
October 30, 2007 at 7:04 pm
Another thing:
1st query will perform one transaction, 2nd will do 2 transactions.
But your performance hit definitely is caused by grouping invoked by UNION.
You should also take into consideration the fact that your system catalogue is locked by SELECT INTO process for the entire duration of the insert.
Not really safe as for production environment.
I would suggest CREATE TABLE first and perform insert(s) after that.
If you don't want to code CREATE TABLE statement you may use
[font="Courier New"]SELECT * INTO NewTable
FROM ...
WHERE 1=0[/font]
Then do INSERT INTO statement(s).
_____________
Code for TallyGenerator
October 30, 2007 at 8:04 pm
UNION is slower because there is more overhead by optimizer because it has to combine two result set into one set.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply