Search code examples
sql-serverxmlprojectionxquery

Optimizing XQuery projection

I'm getting some horrific performance from an XQuery projection in Sql Server. What would be the best way to write the following transformation?

select DocumentData.query(
'<object type="dynamic">
    <state>
       <OrderTotal type="decimal">
          {fn:sum( 
              for $A in /object[1]/state[1]/OrderDetails[1]/object/state[1] 
               return ($A/ItemPrice[1] * $A/Quantity[1]))}      
       </OrderTotal>
       <CustomerId type="guid">
            {xs:string(/object[1]/state[1]/CustomerId[1])}
       </CustomerId>
       <Details type="collection">
           {/object[1]/state[1]/OrderDetails[1]/object}
       </Details>
    </state>
 </object>') as DocumentData
from documents

(I know the code is a bit out of context)

If I check the executionplan for this code, there is about 10+ joins going on. Should I break this down to use for $var for each level in the structure?

For more context, this is what I'm trying to accomplish: http://rogeralsing.com/2011/03/02/linq-to-sqlxml-projections/

I'm writing a "Linq to XQuery translator" / NoSQL Document DB emulator, filtering works like a charm, projections suffer from perf problems.

Solution

  • This article is quite useful: Performance Optimizations for the XML Data Type in SQL Server 2005

    In particular it recommends that instead of writing paths of the form...

    /object[1]/state[1]/CustomerId[1]

    you should instead write...

    (/object/state/CustomerId)[1]