Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

XML created from FOR XML PATH TYPE may lead to bad performance

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.)

0 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 7

    <=-=Sep 4 2010 4:12PM=-=>

    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, TYPE)
    FROM Northwind.dbo.Orders O
    FOR XML PATH, ROOT —, 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, TYPE)
    FROM Northwind.dbo.Orders O
    FOR XML PATH, ROOT —, TYPE
    )

    EXEC load_orders @attr, @elem;
    GO


    Alejandro Mesa

    <=-=Sep 13 2010 1:13PM=-=>

    Thanks Erland and Alejandro

    We are currently investigating it.

    Cheers
    Michael

    <=-=Apr 22 2013 5:41AM=-=>

    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

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base