Slow performance in converting from XML

Question

Post reply

Slow performance in converting from XML

bwhitehe

Mr or Mrs. 500

Points: 503
More actions
May 22, 2014 at 6:46 am

#74254

I'm creating a stored function to convert an NTEXT string to XML then to a NVARCHAR table for use in queries, but am having a large performance hit when building the temp table. This can be seen in the code below:
DECLARE @string NVARCHAR(MAX)
SET @string=N'<row><col>text</col><col>text</col><col>text</col><col>text</col><col>text</col></row>'
--Build STring for XML
SELECT @string = @string + @string + @string + @string + @string
SELECT @string = @string + @string + @string + @string + @string
SELECT @string = @string + @string + @string + @string + @string
SELECT @string = @string + @string + @string + @string + @string
SELECT @string = @string + @string + @string + @string + @string
SELECT @string = N'<rows>' + @string + N'</rows>'
DECLARE @xmlstring XML
SET @xmlstring = CONVERT(XML, @string)
DECLARE @table TABLE
(
c1 NVARCHAR(10),
c2 NVARCHAR(10),
c3 NVARCHAR(10),
c4 NVARCHAR(10),
c5 NVARCHAR(10)
)
INSERT INTO @table
SELECT xmls.rndx.value('col[1]', 'nvarchar(10)') AS c1,
xmls.rndx.value('col[2]', 'nvarchar(10)') AS c2,
xmls.rndx.value('col[3]', 'nvarchar(10)') AS c3,
xmls.rndx.value('col[4]', 'nvarchar(10)') AS c4,
xmls.rndx.value('col[5]', 'nvarchar(10)') AS c5
FROM @xmlstring.nodes('rows/row') AS xmls(rndx);
SELECT *
FROM @table
When I run the code building and returning the table, it takes over 2.5 minutes.
If I comment out the "INSERT INTO @table" line, it runs almost immediately.
If I load the table with text from another table, it runs almost immediately.
I've spent 2 day searching Microsoft, Google and SQLServerCentral and haven't had any luck finding a solution to increase performance.
Any help would be greatly appreciated.
Bob

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

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

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 1

Quick question, are you on SQL2008 SP1? If so, try OPTION ( OPTIMIZE FOR ( @xml = NULL ) ).

😎

bwhitehe Mr or Mrs. 500 Points: 503 More actions · Answer 2

Thanks for the reply. Unfortunately, the OPTION ( OPTIMIZE FOR ( @xmlstring= NULL ) ) didn't help.

We're on SQL 2008 R2 SP2.

Bob

bwhitehe Mr or Mrs. 500 Points: 503 More actions · Answer 3

Eirikur, thanks for the suggestion on the ( OPTIMIZE FOR ( @xmlstring = NULL ) ) .

While it didn't solve the problem, it led me to a post with the (what I thought was a) solution. I needed to tell the XML parser what type of data the XML file contained. Changing the following solved it. Back to sub-second. (but just returning nulls)INSERT INTO @table

SELECT xmls.rndx.value('(col/NVARCHAR)[1]', 'nvarchar(10)') AS c1,

xmls.rndx.value('(col/NVARCHAR)[2]', 'nvarchar(10)') AS c2,

xmls.rndx.value('(col/NVARCHAR)[3]', 'nvarchar(10)') AS c3,

xmls.rndx.value('(col/NVARCHAR)[4]', 'nvarchar(10)') AS c4,

xmls.rndx.value('(col/NVARCHAR)[5]', 'nvarchar(10)') AS c5

FROM @xmlstring.nodes('rows/row') AS xmls(rndx);

SELECT *

FROM @table

Bummer. Just returns nulls. Still looking.

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 4

Eirikur Eiriksson

SSC Guru

Points: 182879

May 22, 2014 at 10:22 am

#1715766

Good stuff!

😎

bwhitehe Mr or Mrs. 500 Points: 503 More actions · Answer 5

OK - Answer.

Create an XML Schema Collection using:

CREATE XML SCHEMA COLLECTION dbo.MyXMLSchema

AS

N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="rows">

<xs:complexType>

<xs:sequence>

<xs:element name="row" maxOccurs="unbounded" minOccurs="0">

<xs:complexType>

<xs:sequence>

<xs:element type="xs:string" name="col" maxOccurs="unbounded" minOccurs="0"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>'

go

I created the schema definition by using http://www.freeformatter.com/xsd-generator.html and pasting a sample of the XML I needed a schema for.

After creating the schema collection, I changed the code as follows:

DECLARE @string NVARCHAR(MAX)

SET @string=N'<row><col>text</col><col>text</col><col>text</col><col>text</col><col>text</col></row>'

--Build STring for XML

SELECT @string = @string + @string + @string + @string + @string

SELECT @string = N'<rows>' + @string + N'</rows>'

DECLARE @xmlstring XML (dbo.MyXMLSchema)

SET @xmlstring = CONVERT(XML, @string)

DECLARE @table TABLE

(

c1 NVARCHAR(10),

c2 NVARCHAR(10),

c3 NVARCHAR(10),

c4 NVARCHAR(10),

c5 NVARCHAR(10)

)

INSERT INTO @table

SELECT xmls.rndx.value('col[1]', 'nvarchar(10)') AS c1,

xmls.rndx.value('col[2]', 'nvarchar(10)') AS c2,

xmls.rndx.value('col[3]', 'nvarchar(10)') AS c3,

xmls.rndx.value('col[4]', 'nvarchar(10)') AS c4,

xmls.rndx.value('col[5]', 'nvarchar(10)') AS c5

FROM @xmlstring.nodes('rows/row') AS xmls(rndx);

SELECT *

FROM @table

Before Creating XML Schema Collection: 164756 ms

After Creating XML Schema Collection: 360 ms 🙂