March 10, 2009 at 4:43 pm
Hi everybody
Why using so many people the UNION ALL syntax to insert multiple values into a table instead of simple INSERT statements?
I just tried with 6000 values:
SET NOCOUNT ON
CREATE TABLE #t (id INT, txt VARCHAR(100))
INSERT INTO #t VALUES (1, 'hello')
INSERT INTO #t VALUES (2, 'hello')
-- and so on...
INSERT INTO #t VALUES (6000, 'hello')
versus:
SET NOCOUNT ON
CREATE TABLE #t (id INT, txt VARCHAR(100))
INSERT INTO #t
SELECT 1, 'hello'
UNION ALL SELECT 2, 'hello'
-- and so on...
UNION ALL SELECT 6000, 'hello'
Inserting 6000 values with simple INSERT statements take less than 2 seconds. Inserting 6000 values with UNION ALL syntax took 6 seconds.
I know Microsoft prefers this syntax but why do you? Because the statement will be done in all or nothing (either everything works or nothing will be inserted)?
Thanking you in anticipation!
Flo
March 10, 2009 at 5:24 pm
[font="Verdana"]I suspect the answer is: "because".
I do know they've got some improved syntax for doing multiple row inserts in SQL Server 2008, so there the "issue" goes away.
I also know if you use the UNION ALL syntax, you can embed the same SELECT statement as part of a CTE or a derived table/in-line view. You can't do that with a whole bunch of INSERT statements.
And I also know if you do the insert with UNION ALLs, it will generate one transaction rather than multiple transactions. Sometimes that's what you want.
But for how you are using it, a whole bunch of inserts is fine.
[/font]
March 10, 2009 at 6:04 pm
Hello Bruce
Thank you for your answer! In my opinion it "because" a absolutely valid reason :).
I just found a third possibility in MSDN forums http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8d2888ab-9ad6-47f2-a01c-0a487b1dc995/ which I never saw. Example:
DECLARE @t TABLE (id INT, txt VARCHAR(100))
INSERT INTO @t VALUES
(1, 'hello'),
(2, 'world')
SELECT * FROM @t
I do not have a specific business case at the moment I just would like to understand why other DBAs/developer do how they do and if there may be any advantage for my work.
Greets
Flo
March 10, 2009 at 6:07 pm
Flo,
I'm afraid I'm unable to duplicate your results. I'm comparing an insert into/SELECT ... union all against a insert into VALUES for 1000 rows. The UNION ALL technique is running consistently faster on my machine, which I would expect because it's a single INSERT, as Bruce already stated.
Whenever possible you want to handle entire sets of data, instead of individual rows. When you look at the execution plan of the SELECT/UNION ALL approach, it amounts to single insert fed by the scan of an internal table of constants.
Bob
P.S. I also find it much easier to type up quick examples like are used in this forum using the SELECT/UNION ALL format.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 10, 2009 at 6:09 pm
POST PostScript:
Row Constructors [ex: Values (1,'hello'), (2,'hello')] are new to 2008.
Be mindful that you are in a 2005 forum.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 10, 2009 at 6:15 pm
Hi Bob!
Bob Hovious (3/10/2009)
Flo,I'm afraid I'm unable to duplicate your results. I'm comparing an insert into/SELECT ... union all against a insert into VALUES for 1000 rows. The UNION ALL technique is running consistently faster on my machine, which I would expect because it's a single INSERT, as Bruce already stated.
Strange...! I tried on SQL Server 2005 in our company and on my SQL Server 2008. The single inserts are always faster than the union syntax..?
Bob Hovious (3/10/2009)
POST PostScript:Row Constructors [ex: Values ((1,'hello') (2,'hello'))] are new to 2008.
Oups... sorry for that. I'm currently investigating SQL Server 2008...
Thanks for your reply!
Greets
Flo
March 10, 2009 at 6:21 pm
florian.reischl (3/10/2009)
Strange...! I tried on SQL Server 2005 in our company and on my SQL Server 2008. The single inserts are always faster than the union syntax..?
[font="Verdana"]Probably dependant on the database recovery model.[/font]
March 10, 2009 at 6:23 pm
I'm currently investigating SQL Server 2008...
No problem. But if you are asking questions about 2008, there is a separate forum for that 🙂
SS2k5 == SQL Server 2005.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 10, 2009 at 11:39 pm
I have used both formats; I usually don't notice on the servers. But most of my servers are pretty powerful; but I have shown both those syntax to developers.
And most of my developer buddies hate UNION ALL because they find it it makes it hard to read. I liked Union all because I don't have to do INSERT INTO .blah blah again...
Me ((0.02/10)/10) cents o.O there is such thing right :doze:
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 11, 2009 at 3:16 am
Hi %
Thank you all for your suggestions! It seems to never mind wich syntax is used.
So as Bruce said; the reason for the used syntax is "because" 🙂 .
Bob Hovious (3/10/2009)
I'm currently investigating SQL Server 2008...
No problem. But if you are asking questions about 2008, there is a separate forum for that 🙂
SS2k5 == SQL Server 2005.
Thanks! I'm only using it to investigate the differences in usage (like the new syntax 😉 ). Currently we only use SQL Server 2000 and 2005 in development, test and production. So for the next time I stay a SQL Server 2005 user.
Thanks to all!
Flo
March 11, 2009 at 5:42 am
hi,
i have done both of them and i think it depends,
somethimes you do not have the ablility to run your query with union all because you may have your result being calculated in a cursor or somthing likethat,
nut the second format looks good for situations that you want to make a single transaction for all your result set,
March 11, 2009 at 7:41 am
you may have your result being calculated in a cursor
Oh no.... you said the "C" word !!! :w00t:
Do you use cursors a lot, Aram?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 11, 2009 at 8:08 am
I generally use Union All because of the transaction control that gives. I have run into problems with the stack when I tried to union too many operators together at once (I think it was 10k rows), but other than that, I've found it works just fine for what I need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2009 at 8:16 am
Hi GSquared
Thank you for your feedback!
Currently I'm using the single INSERT syntax. The main main benefit of the UNION syntax sure is the fact that all data become inserted or not. The single inserts may work for a part of the data and skip the rest after one error anywhere within the middle. In this case if you don't use a transaction you may get into trouble...
Maybe I change my syntax - if I can agree with me old man (>30 :D)
Greets
Flo
March 11, 2009 at 9:51 am
There's a balancing game happening. The union all requires a fair amount of work to merge all of the single rows into a single data set and THEN do the multi-row update. The sinlge statements push all of the work onto the insert operation.
The UNION ALL should yield some gains if it allow you to not have to do multiple updates/inserts to the same data pages over and over and over again. That being said - if your server doesn't have a LOT of RAM - doing a large set of UNION ALL will cause that to be serialized to disk - so you will end up giving most or all of your speed advantage right back.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply