Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

INSERT/SELECT from xml.nodes very slow Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 6:47 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:42 PM
Points: 128, Visits: 819
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
;


Post #1569822
Posted Monday, May 12, 2014 7:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:42 PM
Points: 128, Visits: 819
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).
Post #1569834
Posted Monday, May 12, 2014 7:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 1,247, Visits: 3,619
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?
Post #1569839
Posted Monday, May 12, 2014 7:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:42 PM
Points: 128, Visits: 819
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.
Post #1569847
Posted Monday, May 12, 2014 8:50 AM This worked for the OP Answer marked as solution


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:42 PM
Points: 128, Visits: 819
Q.E.D.

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

OPTION ( OPTIMIZE FOR ( @xml = NULL ) )
Post #1569894
Posted Monday, May 12, 2014 9:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 1,247, Visits: 3,619
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.
Post #1569910
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse