INSERT/SELECT from xml.nodes very slow

Question

INSERT/SELECT from xml.nodes very slow

Bill Talada

SSChampion

Points: 11954
More actions
May 12, 2014 at 6:47 am

#307674

I'm currently using OPENXML to shred a large @xml variable. It can INSERT/SELECT thousands of shredded rows in a second. OPENXML is not scalable and it is deprecated. Therefore, I'm trying to switch to shredding using the new recommended way with .nodes and .value and it shreds in a second. But if I switch from SELECT to INSERT/SELECT it takes 30 seconds. To me that is irrational. Am I doing something wrong with the new method?
/*
-- execute this SELECT to generate some xml
-- click on the xml to open it in a new window
-- copy/paste the xml into the "set @xml" statement below.
select top (3000) name, create_date
from sys.objects
for xml path
*/
declare @xml xml;
-- copy the generated xml into this statement:
set @xml =
'
';
declare @t table (name varchar(128) not null, create_date datetime not null);
--insert into @t -- uncomment this line and it takes 30 seconds. Why?
select
x.data.value('name[1]','varchar(128)') as name,
x.data.value('create_date[1]','datetime') as create_date
FROM
@xml.nodes('/row') x(data)
;
select *
from @t
;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bill Talada SSChampion Points: 11954 More actions · Answer 1

Here is some further information: I ran the script above on a different server and it ran in a second vs. 30 seconds on mine. Both servers are Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Bill Talada (5/12/2014)
Here is some further information: I ran the script above on a different server and it ran in a second vs. 30 seconds on mine. Both servers are Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64).

Sounds like an issue with the machine, the code looks fine. Are you hitting auto growth or something similar?

😎

Bill Talada SSChampion Points: 11954 More actions · Answer 3

I'm sticking with this until it is solved since we have a release coming up in a month. Inside the company I've tried three servers and all take over 30 seconds. Remoting into external customer sites gives 1 second results. I suspect my company's IT dept. blocked some windows updates that could solve this. The external customer is running the same sqlserver SP2 version so it isn't that.

Bill Talada SSChampion Points: 11954 More actions · Answer 4

Q.E.D.

I added this weird line after the FROM clause and now results are immediate!

OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

Bill Talada (5/12/2014)
Q.E.D.
I added this weird line after the FROM clause and now results are immediate!
OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

Good stuff!

I had forgotten about this, if I remember correctly, it is an 2008 sp1 issue.

😎