如何优化“XQuery"SQL [英] How to optimise the 'XQuery' SQL

问题描述

我在有 10,000 条记录的表的 XML 类型列中有这样的 XML 层次结构-

I have an XML hierarchy like this in an XML type column of a table with 10,000 records-

<Root>
     <Elem1>
         <Parent1>
              <Separator>
                  <Child1/>
              </Separator>
         </Parent1>
     </Elem1>
</Root>

<小时>

我有一个这样的查询 -

---

I have a query like this -

DECLARE @Root VARCHAR(50)
DECLARE @Entity VARCHAR(50)
DECLARE @ParentNode VARCHAR(50)
DECLARE @Separator VARCHAR(50)
DECLARE @ChildNode VARCHAR(50)


SET @Root = 'Root'
SET @Entity = 'Elem1'
SET @ParentNode = 'Parent1'
SET @Separator = 'separator'
SET @ChildNode = 'Child1'

select Parent.P.value('.', 'varchar(max)') as MyValue, 
T.uniqueId, T.XMLCol
from [XMLTable] as T
cross apply 
 (SELECT
         XMLTable.XMLCol.query('(/*[local-name()=sql:variable("@Root")]/*[local-name(.)=sql:variable("@Entity")]/*[local-name(.)=sql:variable("@ParentNode")]/*[local-name(.)=sql:variable("@Separator")]/*[local-name(.)=sql:variable("@ChildNode")])[1]'
 )  as Parent(P)

如何进一步优化此查询.目前，它需要 2 秒，如果我进一步进行 INNER JOINS，它会增加时间.我尝试在 XML 列上创建 PRIMARY 索引，但需要更多时间！

How can I further Optimize this query. Currently, it is taking 2 secs and if I do further INNER JOINS, it adds up to the time. I tried creating a PRIMARY Index on the XML Column, but it takes more time !

编辑 - 如果我对路径进行硬编码而不是使用变量，则只需不到一秒钟.但是，我想要它在一个表值函数中并且不能硬编码路径?

select x.value('(Parent1/Separator1/Child1)[1]', 'varchar(max)') as Col1,
x.value('(Parent2/Separator2/Child2)[1]', 'varchar(max)') as Col2,
x.value('(Parent3)[1]', 'varchar(max)') as Col3
from [XMLTable] T 
cross apply T.XMLCOL.nodes('/Root/Elem1') a(x)

上述查询只需一秒.这似乎是最快的.因此，可以使用 ADO.NET 功能

The above query takes just about one sec. It seems to be the fastest of all. So, instead of going for a Table valued function with params, the above query can be dynamically prepared and executed on the fly using ADO.NET capabilities

请纠正我...?

推荐答案

如果您只想要每一行中的一个值，则无需使用 cross apply.

If you only want one value out of every row there is no need to use cross apply.

select XMLCol.value('(/*[local-name()=sql:variable("@Root")]
                      /*[local-name(.)=sql:variable("@Entity")]
                      /*[local-name(.)=sql:variable("@ParentNode")]
                      /*[local-name(.)=sql:variable("@Separator")]
                      /*[local-name(.)=sql:variable("@ChildNode")])[1]', 'varchar(max)')
from XMLTable

获得相同结果的另一种方法是使用 FLWOR.在我有限的测试中，这会运行得更快一些.

Another way to get the same is to use FLWOR. In my limited tests this will run a bit faster.

select XMLCol.value('(for $n1 in /*,
                          $n2 in $n1/*,
                          $n3 in $n2/*,
                          $n4 in $n3/*,
                          $n5 in $n4/*
                      where $n1[local-name(.) = sql:variable("@Root")] and
                            $n2[local-name(.) = sql:variable("@Entity")] and
                            $n3[local-name(.) = sql:variable("@ParentNode")] and
                            $n4[local-name(.) = sql:variable("@Separator")] and
                            $n5[local-name(.) = sql:variable("@ChildNode")]
                      return $n5
                     )[1]', 'varchar(max)')
from XMLTable

这篇关于如何优化“XQuery"SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！