SQL Server不使用XML索引来查询XML行集 [英] SQL Server not using XML Indexes for querying XML rowset

问题描述

希望有人可以帮忙解决这个问题。

Hopefully someone can help with this.

我们需要从SQL中的rowset XML列返回一个数据字段。例程有效，但不使用任何创建的XML索引（Value / Path / Property上的Primary + Secondary，但没有使用Selective XML索引）。由于XML的数量，查询引擎的不断粉碎会大大减慢查询速度。有关如何让SQL Server（2012）在XML列上实际使用XML索引的任何想法吗？

We have the need to return a data field from a rowset XML column in SQL. The routine works but does not use any of the created XML indexes (Primary + Secondary ones on Value/Path/Property but no Selective XML indexes). Due to the amount of XML the constant shredding by the Query Engine is slowing down queries considerably. Anybody any ideas on how to get SQL Server (2012) to actually use the XML indexes on the XML column?

没有速度差异，数据读取或计划差异与关闭或打开XML索引，因此肯定不会使用它们。

There are no speed differences, data read or plan diffs with XML indexes off or on so it is definitely not using them.

示例查询（@Field是所需的字段名，@ XML是XML）。

Example query (@Field is the field name required, @XmlData the XML).

@XmlData.value(
      'declare namespace rs="urn:schemas-microsoft-com:rowset";
      declare namespace z="#RowsetSchema";
      (/xml/rs:data/rs:insert/z:row[@fm_field=sql:variable("@Field")]/@fm_data)[1]','nvarchar(255)')

示例XML片段......

Example XML snippet...

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
  <s:Schema id="RowsetSchema">
    <s:ElementType name="row" content="eltOnly" rs:updatable="true">
      <s:AttributeType name="fm_field" rs:number="1" rs:write="true">
        <s:datatype dt:type="string" dt:maxLength="255" rs:precision="0" rs:fixedlength="true" rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="fm_data" rs:number="2" rs:write="true">
        <s:datatype dt:type="string" dt:maxLength="255" rs:precision="0" rs:fixedlength="true" rs:maybenull="false" />
      </s:AttributeType>
      <s:extends type="rs:rowbase" />
    </s:ElementType>
  </s:Schema>
  <rs:data>
    <rs:insert>
      <z:row fm_field="ABSOLVELIA" fm_data="No" />
      <z:row fm_field="ADJNO" fm_data="" />
      <z:row fm_field="AIRPORTS" fm_data="No" />

推荐答案

我理解原帖有点模糊但是时间有点稀疏，所以现在要澄清一些更多的信息.​​..

I understand the original post is a bit vague but time was a bit sparse so some more information to clarify now...

XML数据的数量可能很大（每个记录平均约15K记录集）数百万）可能有一些TB大小的dbs（大多数只有几百GB，所以它们更容易管理）。

There is a potentially large(ist) amount of XML data (about 15K average per record wth recordsets in the millions) in potentially some dbs of TB size each (most are smaller at only a few hundred GB so they are more managable).

它采用了所有的单核CPU（由于SQL用于解析它的较低级别的进程，XML在SQL引擎中处理单核）因此，任何解析XML信息都将永远无法在其上投入更多核心（我们使用的是现代3GHz + VM这个位的服务器场。）

It is taking all of single core CPU (XML is processed single core in the SQL engine due to the lower level processes SQL uses for parsing it) so any parsing of the XML information is taking forever with no avenue to throw more cores at it (we are using a modern 3GHz+ VM server farm for this bit).

为了加快报告速度，我在客户端上为他们希望报告的400多个XML字段属性添加了一个索引视图，用于数据挖掘， index是从备份构建的（他们不希望实时数据库发生更改或拥有任何内容低位插入或有任何复制它或实际上是等等，所以索引视图是从备份构建的。

To speed up reporting I added an indexed view for the client on over 400 XML field attributes they wish to report off for data mining, this index is built from a backup (they do not wish the live database to change or have anything that slows inserts or have anything replicating off it or indeed HA etc. on live so the Indexed view is built from a backup).

XML格式不可更改所以数据保存在属性（行集格式字段/数据）中阻止SQL使用任何类型的XML索引（我知道XML索引是过去经验中的cr @ p所以我抓着吸管）。

The XML format is not changable so the data being held in attributes (rowset format field/data) is stopping SQL from using any type of XML index (I know XML indexes are cr@p from past experience so I was clutching at straws).

我希望至少有一些方法可以让它使用Primary索引，因此碎片开销较小但无济于事。选择性索引也失败了。从我自己的跟踪来看，很明显，对于这种格式的XML，我们坚持使用XML数据上的索引视图的单个核心构建。

I was hoping there was at least some way to get it to use the Primary index so the shred overhead was less but to no avail. Selective indexes fail also. From tracing this myself it has become apparent that with XML in this format we are stuck with a single core build for Indexed Views on the XML data.

我可以用C＃编写多线程预粉碎机来获得速度和核心用途，但希望SQL有一个我错过的可行答案。

I can code a multi-threaded pre-shredder in C# to get the speed and the core use but was hoping SQL had a workable answer I had missed.

感谢您花时间去寻找无论如何的人。

Thanks for taking the time to look anyhow folks.

这篇关于SQL Server不使用XML索引来查询XML行集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！