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.


5
0
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

Description

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