0

I was excited to find a syntax for multi-row insert, as we have seen slowness when inserting many rows in production systems where there is latency between the application and the db server.

But, testing on my development system (which does not have latency), the new syntax is considerably slower than individual inserts. I'm curious if anyone has an explanation for that, or a suggestion for how it could be improved.

In my test, inserting 60,000 rows, using

INSERT INTO table 
VALUES (a, b,...),(c, d,...),...(x,y,...);

which needed about 60 individual statements (due to 1000 row limit on this approach), took about 15 seconds. Inserting the same rows one at a time took only 8 seconds.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matthew White
  • 349
  • 3
  • 2
  • 2
    I guess [this answer](http://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values/8640583#8640583) sheds some light and also [the other answer](http://stackoverflow.com/a/8635891/284240) in the same question. – Tim Schmelter Feb 26 '15 at 14:43
  • 2
    If you want to insert 60k rows, look at bulk inserts. –  Feb 26 '15 at 14:44
  • Or since you know the values you could do an insert using a select statement. Bulk insert would still be my first choice though for that many. – Sean Lange Feb 26 '15 at 14:45
  • 1
    It does look like your question is (amazingly well) answered at @TimSchmelter's dupe –  Feb 26 '15 at 14:46
  • 1
    To summarize the answer Tim linked to (which is excellent, but a bit much): the T-SQL parser was never optimized for huge inputs. Use either a bulk insert, or individual inserts wrapped in a transaction (to cut down on log traffic). – Jeroen Mostert Feb 26 '15 at 14:47

0 Answers0