XML created from FOR XML PATH TYPE may lead to bad performance - by Erland Sommarskog

Status : 

 


7
0
Sign in
to vote
ID 594968 Comments
Status Active Workarounds
Type Bug Repros 1
Opened 9/4/2010 1:13:26 PM
Access Restriction Public

Description

In the repro, two XML variables are loaded through a FOR XML PATH TYPE query. The documents are nested: they described Orders and OrderDetails.  The documents are similar, the one difference is that one is attribute-centric and the other is element-centric.  The documents include 830 orders and 2155 details elements.

These variables is then passed to a stored procedure, which first selects the size the of the XML documetns and then shreds the documents and inserts orders and details into tables. This done for both parameters. The execution time for the query that retrieves the details is unacceptable > 10 seconds.

Next the variables are assigned to nvarchar(MAX) variables, and the stored procedure is called anew. The performance of all queries is now perfectly acceptable. The size of the documents is smaller this time as well.

The conclusion is that the XML values created by the FOR XML TYPE queries include some extra fluff that makes the XML readers go crazy. 

This is regression from SQL 2005; This behaviour does not appear in SQL 2005 SP3. (But the document has difference sizes on SQL 2005 as well.)
Sign in to post a comment.
Posted by Microsoft on 4/22/2013 at 5:41 AM
Hello,
Thank you for submitting this feedback. After carefully evaluating all of the suggestion items in our pipeline, we are closing items that we will not implement in the near future due to current higher priority items. We will re-evaluate the closed suggestions again in the future based on the product roadmap.

Thanks again for providing the product suggestion and continued support for our product.
Zlatko
Posted by Microsoft on 9/13/2010 at 1:13 PM
Thanks Erland and Alejandro

We are currently investigating it.

Cheers
Michael
Posted by Hunchback on 9/4/2010 at 4:12 PM
Definitely the TYPE keyword is doing something funny. Just comment the most outer TYPE keyword and the execution time improve drastically.

DECLARE @attr xml,
         @elem xml,
         @v_attr nvarchar(MAX),
         @v_elem nvarchar(MAX)

SET @attr = (SELECT O.OrderID    AS [@OrderID],
                    O.CustomerID AS [@CustomerID],
                    O.OrderDate AS [@OrderDate],
                    O.EmployeeID AS [@EmployeeID],
                 (SELECT O.OrderID    as [@OrderID],
                            OD.ProductID as [@ProductID],
                            OD.UnitPrice as [@Price],
                            OD.Quantity as [@Qty]
                    FROM    Northwind.dbo.[Order Details] OD
                    WHERE (O.OrderID = OD.OrderID)
                    FOR XML PATH('OrderDetails'), TYPE)
             FROM Northwind.dbo.Orders O
             FOR XML PATH('Order'), ROOT('Orders')    --, TYPE
             )

SET @elem = (SELECT O.OrderID    AS [OrderID],
                    O.CustomerID AS [CustomerID],
                    O.OrderDate AS [OrderDate],
                    O.EmployeeID AS [EmployeeID],
                 (SELECT OD.OrderID as [OrderID],
                            OD.ProductID as [ProductID],
                            OD.UnitPrice as [Price],
                            OD.Quantity as [Qty]
                    FROM    Northwind.dbo.[Order Details] OD
                    WHERE (O.OrderID = OD.OrderID)
                    FOR XML PATH('OrderDetails'), TYPE)
             FROM Northwind.dbo.Orders O
             FOR XML PATH('Order'), ROOT('Orders')    --, TYPE
             )

EXEC load_orders @attr, @elem;
GO


--
Alejandro Mesa