Carolyn Evans


The first query, using XML.nodes, shows subtree cost 421(ouch). The second query, using OpenXML, shows subtree cost 3.4. I want to use the first technique, but the high subtree cost has me worried. I'm testing with about 3500 rows returning from the queries.

CPU and Disk Queue are about the same for either query. So, my question is, where is the high subtree cost in the first query coming from

DECLARE @XML XML

SELECT

c.value('@Col1', 'datetime'),

c.value('@Col2', 'varchar(15)'),

c.value('@Col2', 'varchar(15)'),

c.value('@Col4', 'int'),

c.value('@Col5', 'uniqueidentifier'),

c.value('@Col6', 'uniqueidentifier'),

c.value('@Col7', 'varchar(1200)')

FROM @XML.nodes('//Item') AS T(c)

DECLARE @XML XML, @iXML int

EXEC sp_XML_PrepareDocument @iXml OUTPUT, @Xml

SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7

FROM OPENXML (@iXML,'//Item', 1) WITH (

Col1 varchar(15),

Col2 datetime,

Col3 varchar(15),

Col4 int,

Col5 uniqueidentifier,

Col6 uniqueidentifier,

Col7 varchar (1200))

EXEC sp_xml_removedocument @iXml




Re: High Subtree Cost - XML nodes function vs. OpenXML

rclancy


I would also like to know the reason for this





Re: High Subtree Cost - XML nodes function vs. OpenXML

rb1n

are you able to supply something more precise than //Item in the Xml.nodes query

the xquery expression can have a big bearing on performance in my experience






Re: High Subtree Cost - XML nodes function vs. OpenXML

Carolyn Evans

Yes. I can specify /Items/Item, and there is no improvement. I also tried applying an XSD to the XML variable, and there was no improvement.