Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adlo
Posting Yak Master
108 Posts |
 Posted - 2010-09-07 : 04:56:29
|
| Hi,I have an app that generates sql codeNow I can have it in two ways:insert into MyTable values (1,2,3)insert into MyTable values (2,2,3)insert into MyTable values (3,2,3)insert into MyTable values (4,2,3)insert into MyTable values (5,2,3)GOOR INSERT INTO MyTable values (1,2,3),(2,2,3),(3,2,3),(4,2,3),(5,2,3)GOIs sql server intelligent enough to make the former example just as fast as the latter or is there a performance improvement of rewriting the apps code to the latter example. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-09-07 : 05:05:27
|
| Both should be same however using the second method, you can only insert 1000 set of valuesMadhivananFailing to plan is Planning to fail |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-07 : 05:06:54
|
Thought this but seems I am wrong  Your second approach will not work. The syntax is wrong/impossible. INSERT ... VALUES ... is only possible for one record at a time.But this will work:INSERT MyTable (colname1, colname2,colname3)SELECT 1,2,3 UNION ALLSELECT 2,2,3 UNION ALLSELECT 3,2,3 UNION ALLSELECT 4,2,3 UNION ALLSELECT 5,2,3
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-09-07 : 05:09:22
|
quote:
Originally posted by webfred
Thought this but seems I am wrong Your second approach will not work. The syntax is wrong/impossible. INSERT ... VALUES ... is only possible for one record at a time.But this will work:INSERT MyTable (colname1, colname2,colname3)SELECT 1,2,3 UNION ALLSELECT 2,2,3 UNION ALLSELECT 3,2,3 UNION ALLSELECT 4,2,3 UNION ALLSELECT 5,2,3
No, you're never too old to Yak'n'Roll if you're too young to die.
OP uses version 2008 and posted at 2005 forum  MadhivananFailing to plan is Planning to fail |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-09-07 : 05:15:29
|
Honestly, I also did not know that it works in 2008  We are always learning something new, that's fine.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-07 : 06:20:44
|
| In theory the first example would involve several transactions while the second one only involves one transaction, hence the second one should be faster. I'm not sure if the difference will be noticeable though...- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-07 : 08:59:15
|
quote:
Originally posted by madhivanan
however using the second method, you can only insert 1000 set of values
For that reason, alone, we avoid it in mechanically generated code |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-09-07 : 09:36:18
|
quote:
Originally posted by Kristen
quote:
Originally posted by madhivanan
however using the second method, you can only insert 1000 set of values
For that reason, alone, we avoid it in mechanically generated code
Thats good. I am not sure why more than 1000 sets are not allowedMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-07 : 13:50:12
|
| By the by, is there a limit to the number ofSELECT A, B, CUNION ALLSELECT ...UNION statements that are allowed? Gotta run out of memory / parsing space / something at some point. Maybe 1,000 is a reasonable limit. Come to think of it I doubt we would do 1,000 UNIONs in a mechanically generated statement either - we're break them up into batches so that they ran in a reasonable time interval ... so on that basic we might as well do up-to-1,000 INSERT VALUES statements |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-08 : 02:40:29
|
quote:
By the by, is there a limit to the number of...
I have inserted at least a few 100k rows like this without a hitch...so I guess the limitation is within a buffer cache or something...- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-08 : 02:45:33
|
| 100K repeats of SELECT 1, 'AAA' UNION ALL SELECT 2, 'BBB'?Interesting thanks. I would imagine it takes SQL Server some time to read & parse it ....I reflected on this yesterday and thought I'd be better off breaking up the batch into "modest" chunks with a "GO" inbetween ...Although I'm sure I doINSERT INTO MyStagingTableSELECT MyValueFROM dbo.MySplitFunction('1,2,3,...')using massive numbers of values - typically that I have got from a Spreadsheet someone has sent me (in that actual example the delimiter will be linebreak, rather than comma) - so I'm not sure that I am walking my talk ... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-09-08 : 03:08:00
|
| It was a few years ago but I remember I was amazed at how fast it was actually. And the situation was that I had a poorly formatted .txt-file that I manually edited using UltraEdit It supports multi-line replace so I found it easier to just create a bunch of selects and then cut/paste in to Management Studio instead of using bcp or sqlcmd - LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
|
|
performance gain of multi row insert vs individual