October 5, 2009 at 5:35 pm
Dear Greater Transact-SQL Intellect,
My apologies for asking such a redimentary question...
I was reading Jeff Moden's article: Cross Tabs and Pivots, Part 1 - http://www.sqlservercentral.com/articles/T-SQL/63681/
Excellent article by the way.
And I'm just wondering about the way he is inputting data into his #SomeTable1 table.
The example shows the following:
CREATE TABLE # SomeTable1
(
Year SMALLINT
Quarter TINYINT
Amount DECIMAL (2,1)
)
SELECT 2006, 1, 1.1 UNION ALL
SELECT 2006 2, 1.2 UNION ALL
etc.
What the difference between using the SELECT statement to input data as opposed to:
INSERT INTO #SomeTable1
VALUES (2006, '1', '1.1')
INSERT INTO #SomeTable1
VALUES (2006, '2', 1.2')
etc.
?
Thanks.
Regards,
Mike G.
Seattle, WA
October 5, 2009 at 7:41 pm
The example using a set of SELECT statements linked by UNION ALLs is a single INSERT of the set of all rows. The other example executes a single INSERT for each individual row. There is much less overhead for the single INSERT of all rows, so it runs faster.
P.S. The word is "rudimentary." 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 5, 2009 at 8:54 pm
I'd be curious to know if anyone has tested this. It's one transaction v many, and I would tend to agree with Bob, but on anything less than hundreds or thousands of rows, I wouldn't expect "faster" to be noticeable.
October 5, 2009 at 9:03 pm
Steve Jones - Editor (10/5/2009)
I'd be curious to know if anyone has tested this. It's one transaction v many, and I would tend to agree with Bob, but on anything less than hundreds or thousands of rows, I wouldn't expect "faster" to be noticeable.
I seem to recall some while back that Jeff or someone had posted a test showing the UNION was faster.
Either way, I like it because it really cuts down on visual clutter in the query plan.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 5, 2009 at 9:23 pm
I use it a lot in demonstration code for two reasons...
1. It cuts down on the actual visual clutter and takes less horizontal and vertical space.
2. I'm lazy. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2009 at 9:28 pm
mgodinez (10/5/2009)
Excellent article by the way.
Thanks for the kudo, Mike. I appreciate it.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2009 at 6:26 am
You're absolutely right, Steve. At that number of rows, hardly anything makes a noticeable difference.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 6, 2009 at 9:02 am
Hello,
Much obliged for the replies. I think I understand now.
Agreeed, w/ such a small amount of inserted data...I didn't notice too much of a time difference, running either code. But thousands of lines, I would think the impact would be felt.
Thanks again.
Regards,
Mike G.
Seattle, WA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply