Why inserting one row is x times faster than inserting x rows at once

Question

Possible duplicate:
Multiple INSERT statements against one INSERT with multiple VALUES values

I am doing some analysis of transactional performance for batch processing for a blog post, and I noticed that when you use the package insert statement, it runs much slower than the equivalent individual SQL statements.

Inserting 1000 rows as shown below takes approximately 3 s

INSERT TestEntities (TestDate, TestInt, TestString) VALUES  
('2011-1-1', 11, 'dsxcvzdfdfdfsa'),
('2011-1-1', 11, 'dsxcvzdfdfdfsa'),
('2011-1-1', 11, 'dsxcvzdfdfdfsa')

Inserting 1000 rows as shown below takes 130 ms

INSERT TestEntities (TestDate, TestInt, TestString) VALUES ('2011-1-1', 11, 'dsxcvzdfdfdfsa')
INSERT TestEntities (TestDate, TestInt, TestString) VALUES ('2011-1-1', 11, 'dsxcvzdfdfdfsa')
INSERT TestEntities (TestDate, TestInt, TestString) VALUES ('2011-1-1', 11, 'dsxcvzdfdfdfsa')

This only happens the first time you use a batch insert in a table, but playable it.

Also note that im data insertion is random (but the same for both queries)

EDIT:

heres im, : https://gist.github.com/2489133

Answer 1

INSERT INSERT VALUES , , SQL , . , , , , 1000 .

3k , , .

, @MartinSmith , 250 , , .

5 200 94ms 1000

Answer 2

- , , , , 1000 .

1000 , 3 , . , 3 .

Answer 3

The first is a single statement that runs as a single transaction. The second is 1000 applications with an overhead of 1000 transactions. The difference should decrease if you enclose the second in begin transactionand commit transaction.