优化 XQuery 投影

发布于 2024-10-20 02:14:33 字数 1062 浏览 4 评论 0原文

我从 Sql Server 中的 XQuery 投影获得了一些可怕的性能。编写以下转换的最佳方式是什么？

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

（我知道代码有点断章取义）

如果我检查这段代码的执行计划，就会发现大约有 10 多个连接正在进行。我应该将其分解为结构中每个级别的 $var 吗？

对于更多上下文，这就是我想要实现的目标： http://rogeralsing.com/2011/03/02/linq- to-sqlxml-projections/

我正在编写一个“Linq to XQuery 转换器”/NoSQL Document DB 模拟器，过滤工作起来就像一个魅力，投影遇到性能问题。

原文

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.

分享到QQ

分享到微博