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

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

Closed

as Fixed

Type:	Bug
ID:	717664
Opened:	1/9/2012 6:03:27 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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 (expand)

English

SQL Server 2012 RC0

SQL Engine

Windows 7 Professional (SP1)

English

DBCC FREEPROCCACHE
SET STATISTICS TIME OFF

CREATE TABLE T
(
A VARCHAR(800),
B VARCHAR(800),
C VARCHAR(800),
D VARCHAR(800),
E VARCHAR(800),
F VARCHAR(800),
G VARCHAR(800),
H VARCHAR(800),
I VARCHAR(800),
J VARCHAR(800)
)

DECLARE @V nvarchar(max) = N',(''A#'',''B#'',''C#'',''D#'',''E#'',''F#'',''G#'',''H#'',''I#'',''J#'')'
DECLARE @I nvarchar(max)

SET @I = (
SELECT REPLACE(@V, '#',STR(number,799))
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 100
FOR XML PATH(''))

SET @I = STUFF(@I,1,1,N'INSERT INTO T
VALUES ')

SET STATISTICS TIME ON
RAISERROR ('100 rows start',0,1) WITH NOWAIT
EXEC(@I)
RAISERROR ('100 rows finish',0,1) WITH NOWAIT
SET STATISTICS TIME OFF

SELECT @I = @I + N',
(''A' + STR(101,799) + N''',''B' + STR(101,799) + N''',''C' + STR(101,799) + N''',''D' + STR(101,799) + N''',''E' + STR(101,799) + N''',''F' + STR(101,799) + N''',''G' + STR(101,799) + N''',''H' + STR(101,799) + N''',''I' + STR(101,799) + N''',''J' + STR(101,799) + N''')'

SET STATISTICS TIME ON
RAISERROR ('101 rows start',0,1) WITH NOWAIT
EXEC(@I)
RAISERROR ('101 rows finish',0,1) WITH NOWAIT
SET STATISTICS TIME OFF

DECLARE @Tail nvarchar(max)

SET @Tail = (
SELECT REPLACE(@V, '#',STR(number,799))
FROM master..spt_values
WHERE type='P' AND number BETWEEN 102 AND 1000
FOR XML PATH(''))

set @I = @I + @Tail

SET STATISTICS TIME ON
RAISERROR ('1000 rows without dupes start',0,1) WITH NOWAIT
EXEC(@I)
RAISERROR ('1000 rows without dupes finish',0,1) WITH NOWAIT
SET STATISTICS TIME OFF

SET @I = (
SELECT REPLACE(@V, '#',STR(1,799))
FROM master..spt_values
WHERE type='P' AND number BETWEEN 1 AND 1000
FOR XML PATH(''))

SET @I = STUFF(@I,1,1,N'INSERT INTO T
VALUES ')

SET STATISTICS TIME ON
RAISERROR ('1000 rows with dupes start',0,1) WITH NOWAIT
EXEC(@I)
RAISERROR ('1000 rows with dupes finish',0,1) WITH NOWAIT
SET STATISTICS TIME OFF

DROP TABLE T

Compile time increases out of proportion to number of rows.

Compile time increases linearly.

0 attachments

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