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?
There are no speed differences, data read or plan diffs with XML indexes off or on so it is definitely not using them.
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)')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" />
2条答案
按热度按时间ovfsdjhp1#
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).
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).
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).
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).
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.
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.
v9tzhpje2#
You might read this . The XML index will help in very few situations... Some scenarios will even get worse...
One scenario, where the XML index would help: Imagine a datatable with an XML column. There are many rows. Each row carries a huge XML. Somewhere in this XML is the information, that a product has the category "abc". Now you want to filter all table rows, where you'll find a product with this given category.
Without an index, the engine would have to look into every single XML, while the index might (but only in a few situations!) provide a more direct access.
If I understand your example well, there is no query, where an XML index could even be considered as helpful.
You do not provide enough information...
<rs:insert>nodes or just one?My magic crystal ball tells me, that your problem is situated somewhere else .