INSERT from .nodes with element-based XML has poor performance on SP2 with x64 - by Erland Sommarskog

Status : 

 


17
0
Sign in
to vote
ID 250407 Comments
Status Active Workarounds
Type Bug Repros 7
Opened 1/7/2007 7:35:22 AM
Access Restriction Public

Description

The performance of the repro is stunning - it takes several minutes!

The defect only happens when
1) WIth SP2, 9.00.3033. There is also an SP1 instance on the same machine,
    and execution time is subsecond.
2) On x64. I tried on a VMware machine on x86 with SP2. Good performance.
3) The data is inserted into a table. A plain SELECT returns quickly.
4) The XML is element-centred. Attribute-centred is as on SP1.
Sign in to post a comment.
Posted by dma550 on 2/25/2015 at 6:17 AM
apparrrently has not made it into Service pack 3, SLQ2008r2. 10 seconds on my 2005 server, 1.5 hrs (and counting) on my 2008r2 server. The workarounds work, but there goes my SQL version independence! Will this fix make it into 2008 ever?
Posted by QAnonymous999 on 1/30/2013 at 11:05 AM
Still happens with SQL2008R2 - 10.50.2550
Posted by rrozema on 4/15/2011 at 6:02 AM
Still the same problem on:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

insert 1000 records from an element based xml using:

insert into dbo.table(c1,c2) select r.c.value('col1','int'), r.c.value('col2','int')
from @xml.nodes('/row') r(c)

and it takes over 5 seconds.

insert the same 1000 records from the same element based xml using:

select r.c.value('col1','int') as c1, r.c.value('col2','int') as c2
into #temp
from @xml.nodes('/row') r(c);

insert dbo.table(c1,c2)
select c1, c2
from #temp;

and the same job is done in less than 200 ms.

Please fix this in SQL 2008, like you've done in 2005 SP3, A.S.A.P. Even though there is a work around, it still takes a lot of time to identify the issue when you hit it.
Posted by StevePritchard on 10/16/2009 at 3:42 AM
See my workaround, but in summary, you simply need to use the text() function to access the contents of the element, and that seems to fix the performance issue.

Cheers,

Steve.
Posted by Erland Sommarskog on 9/25/2009 at 6:22 AM
I've tested this in SQL 2008 SP1, and the behaviour remains. Some of my test cases work better now, but the repro doesn't. Interesting enough, the performance in SQL 2005 SP3 is good for the repro.
Posted by Stuckish on 11/15/2007 at 1:16 AM
I can not agree with Erland that this only happens on Heps.

We are only noticing the problem on x64, Both heaps and table with clustered key.

Have another server with SP2 (x86) with the same table and there it is as fast as ever.

/Ola
Posted by Stevemcn on 9/6/2007 at 2:31 PM
We recently converted all our OPENXML stored procs to use xquery nodes/value, to avoid the memory leak. Now I've just run into this limitation on a new development project. This is really troubling.
Posted by noirs on 8/30/2007 at 2:11 PM
I've hit this issue too, but we ran inserted 5,000,000 records really fast, 10,000/s then suddenly it dropped to 80/s, apparanetly due to the XML.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2077113&SiteID=1

We switched to use Attributes instead of Elements and it seems to stay at 10,000/s now instead of dropping to 80/s for no apparent reason.

We're using x64 SP2 also.

- Alex
Posted by Erland Sommarskog on 3/27/2007 at 1:02 PM
I should add there are a few things that are wrong in my findings:
1) The issue is not unique to x64.
2) The issue does not appear to arise if the table inserted to has a clustered index, only
    heaps are affected.
Posted by LoudRock on 3/27/2007 at 8:51 AM
PS
This is a practice MS endorses.
See
http://support.microsoft.com/kb/315968
(which needs a 2005 version update, fyi)
Posted by LoudRock on 3/27/2007 at 8:48 AM
//quote
but in general I don't think this issue will have broad customer impact.
//end quote

Anyone who uses (strongly typed) DataSet, and the DataSet.GetXml() method to push xml into sql server, IS FORCED into element based xml.
Thus this issue affects anyone using a DataSet as an xml source. Which has to affect alot of people.

I couldn't disagree with the "no broad impact" more statement.

MS, you need to address this issue.
Posted by Microsoft on 1/12/2007 at 2:54 PM
Yes, you're right that the workaround may not be applicable in all cases. We may need to issue a QFE for those cases, but in general I don't think this issue will have broad customer impact. At this point we are locking down for SP2, so this wouldn't make the bar. We're planning to fix it in Katmai and may backport it to a future Yukon SP (if one is announced).

Thanks,
Tomer
Posted by Erland Sommarskog on 1/12/2007 at 1:01 PM
Since using attribute-based XML gives better performance anyway, there is a good
workaround. Then again, you cannot always control the format of the XML sent into you.
So I am little puzzled that you postpone this to Katmai. Isn't there a risk that
you will have to produce a hotfix for this, if someone runs into this for real?

If you insist on letting this be - I really hope you include in the README as a known
issue!

(Since I don't don't if the comments of resolved cases makes it to you, I'm changing the
status to Active.)
Posted by Microsoft on 1/12/2007 at 11:00 AM
Hi Erland,

We can reproduce this problem. Since it does not make the SP2 bar at this time, I copied the bug to Katmai and postponed this one.

Thank you,
Tomer Verona
SQL Server Development