December 18, 2007 at 7:19 am
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 .........
December 18, 2007 at 8:24 am
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.
December 18, 2007 at 8:31 am
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
December 18, 2007 at 9:29 am
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
Change is inevitable... Change for the better is not.
December 18, 2007 at 9:47 am
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.
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.
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
December 18, 2007 at 9:53 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply