перекрестное применение xml-запроса работает экспоненциально хуже по мере роста xml-документа

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

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

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

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

Это дает вам добавление еще одного узла элемента с тремя узлами поля.

Возвращено 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-документ и проверяет оператор фильтра для узла, который вам действительно нужен. Вот вам и экспоненциальный рост. Вместо использования родительской оси вы должны использовать одно дополнительное перекрестное применение. Сначала измельчите на предмете, а затем на поле.

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-индекс, но вы все равно выиграете от изменения способа получения значения поля.

Добавление 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