Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите перейти на OPENXML  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Добрый день.

Есть SQL2k12 EE; SQL2k14 EE

Из некой CLR_ки возвращается большой XML
+ Пример корректного результата
declare @XmlRes xml = N'
<env:Envelope xmlns:enc="http://www.w3.org/2003/05/soap-encoding" xmlns:env="http://www.w3.org/2003/05/soap-envelope">
  <item>
    <item>
      <key xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">ID</key>
      <value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">11</value>
    </item>
  </item>
  <item>
    <item>
      <key xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">ID</key>
      <value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">12</value>
    </item>
    <item>
      <key xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">INFO</key>
      <value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" enc:itemType="ns2:Map" enc:arraySize="1" xsi:type="enc:Array">
        <item xsi:type="ns2:Map">
          <item>
            <key xsi:type="xsd:string">PARENT_ID</key>
            <value xsi:type="xsd:string">11</value>
          </item>
          <item>
            <key xsi:type="xsd:string">NAME</key>
            <value xsi:type="xsd:string">Parent Name 11</value>
          </item>
        </item>
      </value>
    </item>
  </item>
  <item>
    <item>
      <key xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">ID</key>
      <value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">13</value>
    </item>
    <item>
      <key xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">INFO</key>
      <value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" enc:itemType="ns2:Map" enc:arraySize="2" xsi:type="enc:Array">
        <item xsi:type="ns2:Map">
          <item>
            <key xsi:type="xsd:string">PARENT_ID</key>
            <value xsi:type="xsd:string">11</value>
          </item>
          <item>
            <key xsi:type="xsd:string">NAME</key>
            <value xsi:type="xsd:string">Parent Name 11</value>
          </item>
        </item>
        <item xsi:type="ns2:Map">
          <item>
            <key xsi:type="xsd:string">PARENT_ID</key>
            <value xsi:type="xsd:string">12</value>
          </item>
          <item>
            <key xsi:type="xsd:string">NAME</key>
            <value xsi:type="xsd:string">Parent Name 12</value>
          </item>
        </item>
      </value>
    </item>
  </item>
</env:Envelope>
'

;WITH xmlnamespaces('http://www.w3.org/2003/05/soap-envelope' as env)
SELECT 
    t.c.value('(./item[key/text()="ID"]/value/text())[1]','sysname') as [ID]
  , i.*
FROM @XmlRes.nodes('/env:Envelope/item') as t(c)
OUTER APPLY(
  SELECT 
      p.c.value('(./item[key/text()="PARENT_ID"]/value/text())[1]','sysname') as [PARENT_ID]
    , p.c.value('(./item[key/text()="NAME"]/value/text())[1]','sysname') as [NAME]
  FROM t.c.nodes('./item[key/text()="INFO"]/value/item') as p(c)
) as i


Все бы ничего, но на SQL2k12 сильно тормозит. На SQL2k14 работает быстро...

На XML_е подобной структуры (но без './item[key/text()="INFO"]/value/item') переход на OPENXML() дал многократное (тыс. раз) ускорение.

Соответственно попытался перейти на OPENXML()
+ Неудачные попытки
declare @h int
exec sp_xml_preparedocument @h out, @XmlRes

SELECT *
FROM OPENXML( @h, '*/item' )
WITH (
    [ID]  sysname './item[key/text()="ID"]/value/text()'
  , [PARENT_ID] sysname './item[key/text()="INFO"]/value/item/item[key/text()="PARENT_ID"]/value/text()'
  , [NAME] sysname './item[key/text()="INFO"]/value/item/item[key/text()="NAME"]/value/text()'
)


SELECT *
FROM OPENXML( @h, '*/item/item[key/text()="INFO"]/value/item' )
WITH(
    [ID] sysname '../../.././item[key/text()="ID"]/value/text()'
  , [PARENT_ID] sysname './item[key/text()="PARENT_ID"]/value/text()'
  , [NAME] sysname './item[key/text()="NAME"]/value/text()'
)

exec sp_xml_removedocument @h;

Работает быстро, но не правильно.

Как бы рыбку съесть и ног не замочить ?
8 дек 14, 15:57    [16967008]     Ответить | Цитировать Сообщить модератору
 Re: Помогите перейти на OPENXML  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
как компромиссно-промежуточный вариант:
declare @h int
exec sp_xml_preparedocument @h out, @XmlRes, N'<env:Envelope
	xmlns:enc="http://www.w3.org/2003/05/soap-encoding"
	xmlns:env="http://www.w3.org/2003/05/soap-envelope"/>'

;WITH xmlnamespaces('http://www.w3.org/2003/05/soap-envelope' as env)
SELECT x.ID, i.*
FROM OPENXML( @h, '/env:Envelope/item' )
WITH (
    [ID]  sysname '(./item[key/text()="ID"]/value/text())[1]'
  , [xdata] xml './item[key/text()="INFO"]/value'
) x
OUTER APPLY(
  SELECT 
      p.c.value('(./item[key/text()="PARENT_ID"]/value/text())[1]','sysname') as [PARENT_ID]
    , p.c.value('(./item[key/text()="NAME"]/value/text())[1]','sysname') as [NAME]
  from x.xdata.nodes('/value/item') p(c)
) as i
9 дек 14, 06:44    [16969768]     Ответить | Цитировать Сообщить модератору
 Re: Помогите перейти на OPENXML  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
declare @h int
exec sp_xml_preparedocument @h out, @XmlRes,N'<env:Envelope xmlns:enc="http://www.w3.org/2003/05/soap-encoding" xmlns:env="http://www.w3.org/2003/05/soap-envelope"/>' 

SELECT t.[ID],p.[PARENT_ID],p.[NAME]
FROM
(SELECT ID FROM OPENXML( @h, '/env:Envelope/item/item[key/text()="ID"]' )
 WITH ([ID]  sysname 'value/text()'))t
LEFT JOIN
(SELECT [ID],[PARENT_ID],[NAME]
FROM
OPENXML( @h, '/env:Envelope/item/item[key/text()="INFO"]/value/item' )
 WITH (
   [ID]  sysname '../../../item[key/text()="ID"]/value/text()'
  ,[PARENT_ID] sysname 'item[key/text()="PARENT_ID"]/value/text()'
  ,[NAME] sysname 'item[key/text()="NAME"]/value/text()'
))p ON p.[ID] = t.[ID]

exec sp_xml_removedocument @h;
9 дек 14, 07:56    [16969830]     Ответить | Цитировать Сообщить модератору
 Re: Помогите перейти на OPENXML  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
LexusR,

Спасибо, оба варианта работают практически одинаково быстро.
Но
1) промежуточный вариант от Сон Веры Павловны, чуть дольше, да и план запроса ...
2) Простой Left join и по плану и по скорости лучше, но имеет допущение на уникальность поля ID.
В данном конкретном случае это так, но, к сожалению, есть и такие XML_ки, в которых нет уникального поля.
Как быть в этом случае ?
Можно ли, например, как то вытащить порядковый номер верхнего или общего узла "item" для join_а ?
9 дек 14, 09:40    [16970159]     Ответить | Цитировать Сообщить модератору
 Re: Помогите перейти на OPENXML  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Greenhorn,

sp_xml_preparedocument - это для "плоских" документов, устаревшая технология. Используйте xpath и xquery.
9 дек 14, 12:08    [16971135]     Ответить | Цитировать Сообщить модератору
 Re: Помогите перейти на OPENXML  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Владислав Колосов
Greenhorn,

sp_xml_preparedocument - это для "плоских" документов, устаревшая технология. Используйте xpath и xquery.


С этого и начал (см. первый пост) наткнулся на жуткие тормоза у XPath+XQuery.
Попробовал использовать OPENXML() получил тысячекратное ускорение.

На реальных данных 900 строк парсятся около минуты, а OPENXML() меньше 100 ms.

Резюме - в WITH() никак нельзя указать вложенные структуры.
OPENXML() - действительно только для плоских таблиц, но с тормозами то надо как то бороться ...
9 дек 14, 12:33    [16971350]     Ответить | Цитировать Сообщить модератору
 Re: Помогите перейти на OPENXML  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Greenhorn,

900 строк и минута - это очень много. Всё же попробуйте избавиться от OUTER APPLY, те вы включаете парсер для каждой строчки, т.е. 900 раз. А это очень накладно.
9 дек 14, 13:00    [16971567]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить