Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Performance of OpenXML vs .nodes

Author  Topic 

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-12-05 : 15:04:51
Any one familiar with the performance of OpenXML vs nodes? These are two different queries that both shred an XML doc into relational data. The SQL 2000 method (sp_xml_preparedocument) is only 1% of the cost relative to the batch vs the .nodes 2005 function. I thought I remembered reading recommendations not to use sp_xml_preparedocument any more, but based on this query plan I don't see why I would use the .nodes function. Any one have any experience or suggestions?

DECLARE @XmlDocumentHandle int
DECLARE @XmlDocument nvarchar(max)
DECLARE @Xml xml
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'

SET @Xml = CAST(@XmlDocument AS XML)

SELECT
CustomerID = Customer.CustOrder.value('../@CustomerID','varchar(10)')
,ContactName = Customer.CustOrder.value('../../@ContactName','varchar(15)')
,EmployeeID = Customer.CustOrder.value('../@EmployeeID','varchar(10)')
,OrderID = Customer.CustOrder.value('@OrderID','int')
,ProductID = Customer.CustOrder.value('@ProductID','int')
,Quantity = Customer.CustOrder.value('@Quantity','int')
FROM @Xml.nodes('ROOT/Customer/Order/OrderDetail') as Customer(CustOrder)


-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',1)
WITH (CustomerID varchar(10) '../../@CustomerID',
ContactName varchar(15) '../../@ContactName',
EmployeeID INT '../@EmployeeID',
OrderID int '@OrderID',
ProductID int '@ProductID',
Quantity int '@Quantity'
)
-- Remove the internal representation.
EXEC sp_xml_removedocument @XmlDocumentHandle

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-05 : 15:21:27
what does your testing show?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-12-05 : 15:47:11
If I run the each query query 1000 times, here is the CPU and duration

CPU Reads Writes Duration
OpenXML 843 0 0 4118
Nodes Method 2453 0 0 5203

This is with a very small XML doc provided in the sample above if I increase the size of the document and run it another 100 times, these are the results

CPU Reads Writes Duration
OpenXML 1875 61950 0 2347
Nodes 123110 12 0 123434
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-05 : 17:02:46
interesting.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-12-05 : 17:20:47
Yes, I would have assumed that converting xml to relational data would have improved methods in 2005, but as I have been searching the internet I have found others have run into the same situation.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2175851&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=974338&SiteID=1

I guess I will take the approach to test both methods. I seem to remember recommendations not to use openxml and sp_xml_preparedocument based on it's performance, did this change going to 2005?
Go to Top of Page
   

- Advertisement -