Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INSERT/SELECT from xml.nodes very slow


INSERT/SELECT from xml.nodes very slow

Author
Message
Bill Talada
Bill Talada
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 1803
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
;



Bill Talada
Bill Talada
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 1803
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
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6681 Visits: 17676
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?
Cool
Bill Talada
Bill Talada
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 1803
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
Bill Talada
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 1803
Q.E.D.

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

OPTION ( OPTIMIZE FOR ( @xml = NULL ) )
Eirikur Eiriksson
Eirikur Eiriksson
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6681 Visits: 17676
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.
Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search