OPENXML: 11MB XML file: is 25 seconds to parse and load normal?

Question

OPENXML: 11MB XML file: is 25 seconds to parse and load normal?

Jesse Reich

Hall of Fame

Points: 3020
More actions
September 21, 2009 at 12:21 am

#103826

I have a stored procedure which is taking about 25 seconds to parse an 11MB XML document and load the data into table variables. I'm trying to figure out if this is normal.
The specs:
XML is all attribute-centric
XML has 8 main nodes which are loaded into 8 table variables
the largest node has 33,000 records with 50 columns to check for (I didn't design that)
Total number of records is 44,000
2 table variables have 50 columns
I basically do this eight times:
INSERT @table_var
SELECT
col1,
...coln
FROM OPENXML (@idoc, '\amespace:mainnodeamespace:nodeitem')
WITH (
col1 datatype,
...coln datatype
)
Does 25-28 seconds sound normal? This process was created for performance improvement but the C# code it's supposed to replace can iterate through the XML (it's transformed into an object) and do a series of 44,000 stored procedure calls (one line inserts) faster than SQL Server can load the XML.
I haven't posted the code because I would have to modify everything (NDA). I can do that if necessary. My main question is in regards to the time required and if that sounds normal for the amount of data and XML size.

Viewing 12 posts - 1 through 11 (of 11 total)

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

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

Try to use the "nodes" method of the xml datatype (see BOL). You can also use an "xml schema collection" for the xml data.

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 2

Thanks. I tried the nodes method as well but that performance was even worse. I would like to try the nodes method with a schema collection (which I've read can improve things drastically for nodes) but our schemas come from C# and are not entirely SQL Server compliant. But I'm wondering if 25 seconds sounds reasonable for the scenario described...

LutzM SSC Guru Points: 107049 More actions · Answer 3

8kb (9/21/2009)
But I'm wondering if 25 seconds sounds reasonable for the scenario described...

As usual: it depends.

A few questions (aside of the standard questions for server load, available CPU a.s.o.):

How many levels does the xml strucute have?

What's your general process to split the data? (Are you 'climbing up the node levels' or do you use the CROSS APPLY function?)

What I would do is to load the file into a temp table with xml column, add an xml index and use XQuery with CROSS APPLY to split the data.

That shouldn't take 25sec.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 4

Laptop specs are as follows:

SQL Server Express SP 3

Dell Latitude E6400

3.5 GB RAM

Core Duo 2.53 Ghz

The xml structure is below:

="xml"

The method of getting the data is going through each main node and get all of its sub nodes (one level below):

Example:

INSERT @List1

SELECT

col1,

...

coln

FROM OPENXML (@doc_id, 'Root\Items\List1\List1_item', 1)

WITH (

col1 datatype,

...

coln datatype

)

INSERT @List2

SELECT

col1,

...

coln

FROM OPENXML (@doc_id, 'Root\Items\List2\List2_Item', 1)

WITH (

col1 datatype,

...

coln datatype

)

and so on...

The big performance hit is when you get to List4. There are fifty columns and it takes 10-13 seconds just to extract the data.

There is no load on the machine while the tests are running except for basic background processes in XP.

CROSS APPLY sounds interesting. Actually we're going to roll back to the original method of running straight inserts from C# because it's so much faster. But I'm wondering if OPENXML slows down the process every time you do an extraction of data. Especially with bigger files.

LutzM SSC Guru Points: 107049 More actions · Answer 5

I'm still not sure what your query looks like.

The way you posted your sample data it appears to be a query against just one level.

If you're staying within one node CROSS APPLY wouldn't be necessary from my point of view.

The following code should work and shouldn't take too much time.

It would be a totally different story if you'd had to climb up and down the nodes or even parallel nodes. But there's just not enough information....

SELECT

c.value('.','VARCHAR(10)') AS List1

FROM @xml.nodes('ROOT/ITEMS/LIST1/LIST1_ITEM') AS T(c)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 6

Just a question - do you get any better performance loading into a temp table?

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Jesse Reich Hall of Fame Points: 3020 More actions · Answer 7

The use of a temp table vs. table variable seems to make no difference.

So here is the code. At least this code will build an XML object that is very close to what we get in our stored procedure.

-- Create temp version of table that exists in database. It has 50+ columns

DECLARE @t TABLE (

col_01 varchar(6), col_02 varchar(7), col_03 varchar(3), col_04 varchar(1), col_05 varchar(4),

col_06 varchar(11), col_07 varchar(1), col_08 varchar(14), col_09 varchar(12), col_10 varchar(2),

col_11 varchar(1), col_12 varchar(2), col_13 varchar(5), col_14 numeric(38, 0), col_15 numeric(38, 0),

col_16 numeric(38, 0), col_17 numeric(38, 0), col_18 numeric(38, 0), col_19 varchar(5), col_20 varchar(3),

col_21 varchar(5), col_22 varchar(10), col_23 varchar(10), col_24 varchar(12), col_25 datetime,

col_26 datetime, col_27 varchar(4), col_28 varchar(4), col_29 varchar(4), col_30 varchar(4), col_31 varchar(4),

col_32 varchar(21), col_33 varchar(11), col_34 varchar(6), col_35 varchar(2), col_36 varchar(6), col_37 varchar(5),

col_38 varchar(1), col_39 varchar(6), col_40 varchar(7), col_41 varchar(11), col_42 varchar(2), col_43 varchar(3),

col_44 varchar(1), col_45 varchar(12), col_46 varchar(3), col_47 varchar(3), col_48 varchar(5), col_49 varchar(17),

col_50 varchar(25), col_51 numeric(38, 0), col_52 varchar(10), col_53 varchar(10), col_54 varchar(13)

)

-- Insert sample row of data. Most columns are not populated during the imports.

INSERT @t (

col_01,

col_02,

col_04,

col_05,

col_06,

col_08,

col_09,

col_12,

col_13,

col_14,

col_15,

col_16,

col_17,

col_18,

col_26,

col_33,

col_39,

col_40,

col_41,

col_51 )

SELECT

'ABCDEF',

'123456',

'A',

2590,

'11089121',

'ABCDEFGHIKLMNO',

'PAD',

'RC',

7102,

0,

'2009-04-02 20:38:50.000',

'11089121',

'ABCDEF',

'702396',

'12064078',

0

-- Create XML object that has 33000 rows of the sample data. This is comparable in size to the document I import

DECLARE @x xml

SET @x = (

SELECT *

FROM (

SELECT NULL AS x

) ItemList

CROSS JOIN

(SELECT t.*

FROM @t t

CROSS JOIN (SELECT TOP 33000 v1.* FROM master..spt_values v1 CROSS JOIN master..spt_values v2) v

) Item

FOR XML AUTO

)

-- Run the code I was attempting to use in order to extract the data. It takes around 10-15 seconds to get everything back

DECLARE

@doc_id int,

@time datetime

SET @time = getdate()

EXEC sp_xml_preparedocument @doc_id output, @x

SELECT *

FROM OPENXML(@doc_id, '//ItemList/Item')

WITH (