INSERT Performance of Row Constructor is quiet bad - by Florian Reischl

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 508112 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 11/4/2009 2:06:34 AM
Access Restriction Public


The new row constructor syntax of SQL Server 2008 to insert several rows is a quiet neat feature, unfortunately the performance is quiet bad. I really thought this would be a great performance optimization and a replacement for the old UNION ALL concatenated rows to add all rows in one bulk. In addition I really don’t understand the restriction to 1000 rows.

If this becomes optimized in future versions this might be a optimization (for time duration and network traffic) for client libraries like ADO.NET. Currently - including .NET 4.0 - ADO creates single insert statements to send a table-valued parameter from client to server.

My tests showed me that single INSERT statements are currently more than ten times (!) faster than using the row constructor. Due to this I don’t really understand the reason for the introduction of the row constructor, since it was already available by using UNION ALL.
Sign in to post a comment.
Posted by Paul White NZ on 5/26/2012 at 10:57 PM
I see no improvement in SQL Server 2012. Row constructors are still expanded as LogOp_UnionAll.
Posted by Microsoft on 12/15/2009 at 4:59 PM
Resolved this item as "Won't fix" for SQL Server 2008, will be resolved in the next major release though.

- Tobias
Posted by Microsoft on 11/23/2009 at 10:21 AM
Hi Florian,

This is a defect in the query optimizer which will be fixed in the next major release of SQL Server. I am not sure right now if this will make it into SQL Server 2008/R2.

Thanks for you help!
- Tobias, SQL Server Engine