union

Question

union

Ananth-431739

Ten Centuries

Points: 1185
More actions
December 18, 2007 at 7:19 am

#127362

which is the optimized query?
insert into select * from table1
insert into select * from table2
insert into select * from table3
or
insert into select * from table1
union
insert into select * from table2
union
insert into select * from table3
The 3 tables hold unique records .........

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 1

Neither one. They will run differently. The first runs as 3 separate transactions, assuming you do not have implicit transactions changed. It could result in some data being inserted and some not.

I'm not sure the second is valid. It should be

insert into tablexx

select * from table1

union

select * from table2

union

select * from table3

And it will require more resources as it will pull all the data into a union query before inserting it. It will run as one transaction, so you'll get all data or none inserted.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You may even get a different number of rows inserted into the table in the second one. Union eliminates duplicate rows.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

Actually, if you look at the bigger picture, you have a much larger problem... why would you select from 3 different tables into 1? Except, possibly, for reporting, you now have duplicate data in the database... and that's a larger problem.

And, if you want the two queries to match perfectly, you must use UNION ALL instead of UNION, as Steve and Gail kinda suggested.

On a differenet note... where has the sense of adventure and discovery in the world of programing gone? Why would anyone possibly take the word of other folks without building a test of your own? "Trust, but verify!" And, in doing so, you might learn something you hadn't intended to. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

toniupstny Hall of Fame Points: 3986 More actions · Answer 4

If you do a UNION ALL you will get all the rows and take less resources as there is no need to sort out the DISTINCT rows.

http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/

There are also some references to test trials with multiple insert commands vs one insert with mult Select/Union All clauses mostly indicating that the single Insert stmt is the better performer.

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

When I tried it 3 separate ways (multiple inserts, insert with mult select/Union and insert with mult Select/Union All), Union All took the least time (Constant scan + Concat), Union took about another 30% or so (had to do Merges (for distinct) instead of Concatenation ) and multiple INSERTs was the most costly by far (each Insert was about the same cost as the single UNION ALL insert so the total is orders of Magnitude more).

Of course your results may vary.

Toni

/* Multiple Inserts */

/* Multiple Insert Statements */

DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

SET DATEFORMAT DMY

INSERT@Sample

SELECT'inv1', 'acc1', '01/01/2007'

Insert @sample

SELECT'inv2', 'acc1', '01/02/2007'

Insert @sample

SELECT'inv3', 'acc1', '01/03/2007'

Insert @sample

SELECT'inv4', 'acc1', '01/04/2008'

Insert @sample

SELECT'inv5', 'acc2', '11/05/2007'

Insert @sample

SELECT'inv6', 'acc2', '12/06/2007'

Insert @sample

SELECT'inv7', 'acc2', '01/07/2008'

Insert @sample

SELECT'inv8', 'acc3', '13/08/2007'

/* SINGLE INSERT WITH UNION */

/* SINGLE INSERT WITH UNION *?

DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

SET DATEFORMAT DMY

INSERT@Sample

SELECT'inv1', 'acc1', '01/01/2007' UNION

SELECT'inv2', 'acc1', '01/02/2007' UNION

SELECT'inv3', 'acc1', '01/03/2007' UNION

SELECT'inv4', 'acc1', '01/04/2008' UNION

SELECT'inv5', 'acc2', '11/05/2007' UNION

SELECT'inv6', 'acc2', '12/06/2007' UNION

SELECT'inv7', 'acc2', '01/07/2008' UNION

SELECT'inv8', 'acc3', '13/08/2007'

/* SINGLE INSERT WITH UNION ALL */

/* SINGLE INSERT WITH UNION ALL */

DECLARE@Sample TABLE (Invoice CHAR(4), Account CHAR(4), Date DATETIME)

SET DATEFORMAT DMY

INSERT@Sample

SELECT'inv1', 'acc1', '01/01/2007' UNION ALL

SELECT'inv2', 'acc1', '01/02/2007' UNION ALL

SELECT'inv3', 'acc1', '01/03/2007' UNION ALL

SELECT'inv4', 'acc1', '01/04/2008' UNION ALL

SELECT'inv5', 'acc2', '11/05/2007' UNION ALL

SELECT'inv6', 'acc2', '12/06/2007' UNION ALL

SELECT'inv7', 'acc2', '01/07/2008' UNION ALL

SELECT'inv8', 'acc3', '13/08/2007'

Toni

toniupstny Hall of Fame Points: 3986 More actions · Answer 5

Hmmm.... I missed the 3 different table part so let me know if you try that out. It would be interesting to see if things change.

Thank you

Toni