Cross apply xml query выполняет экспоненциально хуже, поскольку XML-документ растет

Ответ 1

Что делает запрос cross cross выполнить так плохо на этом простом XML документ и экспоненциально медленнее по мере роста набора данных?

Использование родительской оси для получения идентификатора атрибута из элемента node.

Эта часть плана запроса является проблематичной.

Обратите внимание на то, что 423 строки выходят из нижней табличной функции.

Добавление только одного элемента node с тремя узлами поля дает вам это.

Возвращено 732 строк.

Что делать, если мы удвоим узлы от первого запроса до всего 6 узлов элемента?

Мы вернемся к колоссальной 1602 строке.

Рисунок 18 в верхней функции - это все узлы поля в вашем XML. У нас есть 6 элементов с тремя полями в каждом элементе. Эти 18 узлов используются во вложенных петлях, соединяющих другую функцию, поэтому 18 исполнений, возвращающих 1602 строки, дают, что он возвращает 89 строк на итерацию. Это всего лишь точное число узлов во всем XML. Ну это на самом деле один больше, чем все видимые узлы. Я не знаю почему. Вы можете использовать этот запрос, чтобы проверить общее количество узлов в вашем XML.

select count(*)
from @XML.nodes('//*, //@*, //*/text()') as T(X)  

Таким образом, алгоритм, используемый SQL Server для получения значения, когда вы используете родительскую ось .. в функции значений, состоит в том, что он сначала находит все узлы, из которых вы клонируете, 18 в последнем случае. Для каждого из этих узлов он разбивает и возвращает весь XML-документ и проверяет в операторе фильтра для node, который вы действительно хотите. Там у вас есть экспоненциальный рост. Вместо использования родительской оси вы должны использовать один дополнительный крест. Сначала надавите на элемент, а затем на поле.

select I.X.value('@name', 'varchar(5)') as item_name,
       F.X.value('@id', 'uniqueidentifier') as field_id,
       F.X.value('@type', 'int') as field_type,
       F.X.value('text()[1]', 'nvarchar(15)') as field_value
from #temp as T
  cross apply T.x.nodes('/data/item') as I(X)
  cross apply I.X.nodes('field') as F(X)

Я также изменил способ доступа к текстовому значению поля. Использование . заставит SQL Server искать дочерние узлы в field и объединить эти значения в результат. У вас нет дочерних значений, поэтому результат один и тот же, но лучше не использовать эту часть в плане запроса (оператор UDX).

В плане запроса нет проблемы с родительской осью, если вы используете индекс XML, но вам все равно будет полезно изменить способ получения значения поля.

Ответ 2

Добавление XML-индекса делало трюк. Теперь 6500 записей, которые заняли 20 минут, 4 секунды.

create table #temp (id int primary key, x xml)
create primary xml index idx_x on #temp (x)

insert into #temp (id, x)
values (1, '
<data item_id_type="1" cfgid="{4F5BBD5E-72ED-4201-B741-F6C8CC89D8EB}" has_data_event="False">
  <item name="1">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.506543009706267</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.79500402346138</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.0152649050024924</field>
  </item>
  <item name="2">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.366096802804087</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.386642801354842</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.031671174184115</field>
  </item>
  <item name="3">
    <field id="{EA032B25-19F1-4C1B-BDDE-3113542D13A5}" type="2">0.883962036959074</field>
    <field id="{71014ACB-571B-4C72-9C9B-05458B11335F}" type="2">-0.781459993268713</field>
    <field id="{740C36E9-1988-413E-A1D5-B3E5B4405B45}" type="2">0.228442351572923</field>
  </item>
</data>
')

select c.value('(../@name)','varchar(5)') as item_name
      ,c.value('(@id)','uniqueidentifier') as field_id
      ,c.value('(@type)','int') as field_type
      ,c.value('(.)','nvarchar(15)') as field_value
from   #temp cross apply
       #temp.x.nodes('/data/item/field') as y(c)

drop table #temp