Multiple rows with a single INSERT in SQLServer 2008

Question

I am testing the speed of inserting multiple rows with a single INSERT statement.

For example: INSERT INTO [MyTable] VALUES (5, 'dog'), (6, 'cat'), (3, 'fish)

This is very fast until I pass 50 rows on a single statement, then the speed drops significantly.

Inserting 10000 rows with batches of 50 take 0.9 seconds. Inserting 10000 rows with batches of 51 take 5.7 seconds.

My question has two parts:

1. Why is there such a hard performance drop at 50?
2. Can I rely on this behavior and code my application to never send batches larger than 50?

My tests were done in c++ and ADO.

Edit: It appears the drop off point is not 50 rows, but 1000 columns. I get similar results with 50 rows of 20 columns or 100 rows of 10 columns.

Answer 1

It could also be related to the size of the row. The table you use as an example seems to have only 2 columns. What if it has 25 columns? Is the performance drop off also at 50 rows?