why is inserting a single row x times faster than inserting x rows at once [duplicate]

Question

Possible Duplicate:
Multiple INSERT statements vs. single INSERT with multiple VALUES

Im doing some performance analysis on transactions for batch processing for a blog post and I've noticed that when you use a batch insert statement it performs much slower than the equivalent individual SQL statements.

inserting 1000 rows as below takes approx 3s

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 below takes 130ms

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 appears to happen on the first time you use a batch insert on the table but its reproducible.

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

EDIT:

heres my repro case with the dummy random data im using for this case: https://gist.github.com/2489133

Answer 1

The issue here according to Multiple INSERT statements vs. single INSERT with multiple VALUES is that when SQL gets the query it has to calculate a query plan on first execution. For a single insert this is nice and quick, as there's not much to calculate, and after it has built the query plan it just re-uses it 1000 times.

in the batch scenario there are 3k variables which need to be built into the query plan which takes much longer to calculate.

One crazy feature which @MartinSmith points out is that there is a magic performance number around a batch size of up to 250 rows which means that the plan calculation is very low.

breaking my above query into 5 200 row statements reduces the execution time to 94ms for 1000 rows