Блог

У меня есть хранимая процедура, которая принимает XML-документ в качестве параметра, аналогичного по структуре следующему:

 <grandparent name="grandpa bob">
  <parent name="papa john">
    <children>
      <child name="mark" />
      <child name="cindy" />
    </children>
  </parent>
  <parent name="papa henry">
    <children>
      <child name="mary" />
    </children>
  </parent>
</grandparent>
  

Мое требование состоит в том, чтобы «сгладить» эти данные так, чтобы их можно было вставить во временную таблицу и выполнять дальнейшие действия по процедуре, чтобы приведенный выше XML стал:

 Grandparent Name Parent Name     Child Name
---------------- --------------- ---------------
grandpa bob      papa john       mark
grandpa bob      papa john       cindy
grandpa bob      papa henry      mary
  

В настоящее время это выполняется с использованием XML-узлов SQL Server:

 SELECT
    VIRT.node.value('../../../@name','varchar(15)') 'Grandparent Name',
    VIRT.node.value('../../@name','varchar(15)') 'Parent Name',
    VIRT.node.value('@name','varchar(15)') 'Child Name'
FROM
    @xmlFamilyTree.nodes('/grandparent/parent/children/child') AS VIRT(node)
  

Это отлично работает до тех пор, пока я не начну передавать большие объемы данных в процедуру (т. Е. более 1000 child узлов), после чего она останавливается и выполнение занимает от 1 до 2 минут. Я думаю, это может быть связано с тем, что я начинаю с самого низкого уровня ( <child ), а затем просматриваю резервную копию XML-документа для каждого события. Улучшит ли здесь производительность разделение этого отдельного запроса на 3 части (по одной на узел, с которого мне нужны данные)? Учитывая, что ни на одном из этих узлов нет «ключей», которые я мог бы использовать для резервного копирования, может ли кто-нибудь предложить какие-либо указания, как я мог бы это сделать?

Кажется, я ответил на свой собственный вопрос, еще немного покопавшись в Интернете:

 SELECT
    grandparent.gname.value('@name', 'VARCHAR(15)'),
    parent.pname.value('@name', 'VARCHAR(15)'),
    child.cname.value('@name', 'VARCHAR(15)')
FROM
    @xmlFamilyTree.nodes('/grandparent') AS grandparent(gname)
CROSS APPLY
    grandparent.gname.nodes('*') AS parent(pname)
CROSS APPLY
    parent.pname.nodes('children/*') AS child(cname)
  

С помощью CROSS APPLY я могу выбрать grandparent узел верхнего уровня и использовать его для выбора дочерних parent узлов и так далее. Используя этот метод, я сократил время выполнения моего запроса примерно с 1 минуты 30 секунд примерно до 6 секунд.

Интересно, однако, что если я использую «старый» OPEN XML метод для извлечения тех же данных, запрос выполняется за 1 секунду!

Похоже, вам, возможно, придется подходить к использованию этих двух методов в каждом конкретном случае в зависимости от ожидаемого размера / сложности передаваемого документа.