For instance if we take a look at the following statements, they both have the same net result with the difference that the first statement is a single statement using the multiple row insert syntax, while the second is the way things are typically done.
insert into humanresources.employeepayhistory
(1, getdate(), 100, 1, getdate()),
(2, getdate(), 100, 1, getdate()),
(3, getdate(), 100, 1, getdate()),
(4, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (1, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (2, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (3, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (4, getdate(), 100, 1, getdate())
Having a look at the difference between the two is very interesting. I ran both queries on the sample AdventureWorks database (inside a begin/rollback transaction so I could work from a consistent base point) using SQL 2008 CTP4 (July 2007)and looked at the execution plans and the client statistics to see what the difference is on something simple like this.
The two pictures below show the differences between the execution plans. As you can see the individual inserts are executed singly and has a TRIVIAL optimisation level applied. The cost of each statement is 0.0132898, so the 4 statements will have a total cost of 0.0531592
The multi-row insert version is quite different. It has an optimisation level of FULL and the whole statement has a cost of 0.0187608. A cost reduction of 0.0343984 (or about 65%!). That's seriously significant when we're talking about such a small amount of data.
You can also see that there is a lot more work being done earlier in the execution plan (apologies for the cramped visuals) which is where the real savings are occurring.
Here's the visual difference between the two plans (click for a larger view):
The client statistics are also very interesting. The single statement (multi-row inserts) has far fewer selects and inserts and this is obviously where the major savings occur, though the overall time saving of 40% was also a surprise.
|Run||Single Statement||Separate Statements|
|Query Profile Statistics|
|Number of INSERT, DELETE and UPDATE statements||2||8|
|Rows affected by INSERT, DELETE, or UPDATE statements||4||4|
|Number of SELECT statements||1||4|
|Rows returned by SELECT statements||1||4|
|Number of transactions||1||1|
|Number of server roundtrips||3||3|
|TDS packets sent from client||3||3|
|TDS packets received from server||11||24|
|Bytes sent from client||626||934|
|Bytes received from server||33338||88924|
|Client processing time||30||50|
|Total execution time||30||50|
|Wait time on server replies||0||0|
The moral: When inserting multiple rows into the database use the new multi-row insert syntax rather than the traditional method of having multiple insert statements.
[Note: CTP performance is always a work in progress and performance information here will likely be improved upon in the final RTM release]