Parent navigations in .value results in absymal performance. - by Erland Sommarskog

Status : 

 


9
0
Sign in
to vote
ID 577806 Comments
Status Active Workarounds
Type Bug Repros 1
Opened 7/23/2010 11:13:09 AM
Access Restriction Public

Description

Say that you have an XML document which contains Orders and Order Details and you want to shred it into relational format. A natural way to get the details would be something like:

   SELECT OrderID   = T.Item.value('../@OrderID', 'int'),
	       ProductID = T.Item.value('@ProductID', 'smallint'),
	       Price     = T.Item.value('@Price', 'decimal(10,2)'),
	       Qty       = T.Item.value('@Qty', 'int')
   FROM	@x.nodes('/Orders/Order/OrderDetails') AS T(Item)

But performance of this can be very poor. In the repro there are 830 orders and 2155 details, which does not seem very extreme. But on my machine it takes 45 seconds to shred it. If I remove the reference to ../@OrderID, the execution time is subsecond. There is no logical reason why it would be so. There is an unambious reference to the parent, and there is no reason to search all over the XML document (or whatever you are doing.)
Sign in to post a comment.
Posted by Erland Sommarskog on 9/4/2010 at 2:52 PM
Permit me to say that I find this answer as quite arrogant. The workaround may be "widely communicated", but I had not seen it before. Nor I can find any text in Books Online that recommends against the use of the parent axis. Thus, as widely commented as the workaround maybe, there will still be people who will use parent-axis, and therefore answering that is is low priority is not a very satisfactory from a customer perspective.

As it happens, there are two small changes needed to make the repro to perform well. The first concerns how the XML is constructed. Take out TYPE to produce an nvarchar value (this because of an issue I have reported in
https://connect.microsoft.com/SQLServer/feedback/details/594968/xml-created-from-for-xml-path-type-may-lead-to-bad-performance#).
The second change concerns the query itself. Change the retrieval of the attribute to:
T.Item.value('../@OrderID[1]', 'int')
This gives a different query plan, that does not include any table-spool operator.

Since ../@OrderID and ../@OrderID[1] means the same thing, there is no reason that there should be different query plans, and I have reported this spearately,
https://connect.microsoft.com/SQLServer/feedback/details/594982/unexpected-query-plans-with-attribute-centric-xml

As I have suggested in yet a Connect item, there is all reason to review how query plans for XML are generated. Given that it is very difficult to get accurate estimates, there is all reason to consider more rule-based optimization and avoid plans that will perform badly when the actual number of rows grossly exceeds the estimates. For instance, plans that depends on spool operators should probably be avoided.
Posted by Michael [MSFT] on 8/11/2010 at 6:17 PM
Hi Erland.

Thanks for reporting this issue. We are aware of this performance issue and currently recommend that such queries are being rewritten using multiple nodes() methods and cross apply:

SELECT OrderID = O.Item.value('@OrderID', 'int'),
         ProductID = T.Item.value('@ProductID', 'smallint'),
         Price     = T.Item.value('@Price', 'decimal(10,2)'),
         Qty     = T.Item.value('@Qty', 'int')
FROM @x.nodes('/Orders/Order') AS O(Item) cross apply T.Item.nodes('OrderDetails') AS T(Item)

Given that the workaround has been widely communicated, we are having this item on a low priority in our things to fix.

Please let me know if that causes any problems.
Thanks
Michael