Home Dashboard Directory Help
Search

Insert performance with non parameterised table valued constructors degrades non linearly with number of clauses. by Martin Smith


Status: 

Closed
 as Fixed Help for as Fixed


3
0
Sign in
to vote
Type: Bug
ID: 717664
Opened: 1/9/2012 6:03:27 AM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Repro Script Attached.

It does four inserts into a heap containing 10 VARCHAR(800) columns. On my desktop dev machine running SQL Server 2008 R1.

Inserting 100 rows with no duplicate values has a compile time of 0.5 seconds
Inserting 101 rows with no duplicate values has a compile time of 8 seconds
Inserting 1000 rows with no duplicate values has a compile time of 12.5 minutes!
Inserting 1000 rows with all duplicate rows has a compile time of 6 seconds

The 100 row case is auto parameterised. The others are not. It seems that when compiling a plan for specific literal values it does some comparisons of the values against each other (possibly during algebrization?) and performs much worse when all values are different.

I suggest that this is unnecessary overhead.

When posting this I came across this related connect item (https://connect.microsoft.com/SQLServer/feedback/details/508112/insert-performance-of-row-constructor-is-quiet-bad) that is closed and indicates performance will be fixed in Denali so I re-ran the same tests on Denali RC0.

These results were much quicker despite being run on a VM with only 1 processor and 1GB RAM accessible but still show the same pattern of degradation as the number of clauses increases.

Inserting 100 rows with no duplicate values has a compile time of 184 ms
Inserting 101 rows with no duplicate values has a compile time of 1.4 seconds
Inserting 1000 rows with no duplicate values has a compile time of 2.25 minutes
Inserting 1000 rows with all duplicate rows has a compile time of 1 seconds

So I suggest that there is still work to be done here.
Details
Sign in to post a comment.
Posted by Microsoft on 2/1/2012 at 2:48 PM
This issue has been fixed. The fix will be in the next major release (*not* SQL Server 2012).

Andrew Richardson
Developer, SQL Server Query Optimizer.
Posted by Microsoft on 1/11/2012 at 12:25 PM
I realize my previous statement is ambiguous.
Connect item 508112 is fixed.
Connect item 717664 is under investigation.

Jos
Posted by Microsoft on 1/11/2012 at 12:23 PM
It turns out the issue reported here is different from Connect item 508112. The fix will be included in SQL Server 2012 RTM.
We are continuing our investigation of this issue.

Thanks,
Jos de Bruijn
Posted by Martin Smith on 1/11/2012 at 3:47 AM
Hello,

I'm not currently affected by this issue myself. It came up in a question on Stack Overflow (http://stackoverflow.com/q/8635818/73226) where I think the OP was testing various scenarios.

I understand that in general you do not recommend inserting a lot of rows using an ad hoc query but sometimes it is convenient to do so and it would be nice to not have to consider this issue when doing so in the future.
Posted by Microsoft on 1/10/2012 at 4:43 PM
A caveat concerning my previous message: we do not plan to address this issue in SQL Server 2012 RTM. It would be addressed in an upcoming service release.

If you need a hotfix, please contact Microsoft Customer Service and Support.

Thanks,
Jos de Bruijn
Posted by Microsoft on 1/10/2012 at 4:38 PM
Thank you very much for providing this feedback.
We are investigating the issue and verifying what happened with Connect item 508112.

In general we do not recommend inserting a lot of rows using an ad hoc query.

Are you observing the issue in production scenarios, or only in this test case?
Also, do you see many scenarios hitting this issue?


Thanks,
Jos de Bruijn
Program Manager
SQL Server Engine
Sign in to post a workaround.