XML, XQuery и тройная печаль с производительностью

Поездка в Днепропетровск на встречу Dnepr SQL User Group, хронический недосып последние пару дней, но приятный бонус по приезду в Харьков… Зимняя погодка, которая мотивирует на написание чего-то интересного…

Уже давно в планах было рассказать про «подводные камни» при работе с XML и XQuery, которые могут приводить к каверзным проблемам с производительностью.

Для тех кто часто использует SQL Server, XQuery и любит парсить значения из XML рекомендуется ознакомиться с нижеследующим материалом…

Для начала сгенерируем тестовый XML на котором будем проводить эксперименты:

USE AdventureWorks2012
GO

IF OBJECT_ID('tempdb.dbo.##temp') IS NOT NULL
    DROP TABLE ##temp
GO

SELECT val = (
    SELECT
          [@obj_id] = o.[object_id]
        , [@obj_name] = o.name
        , [@sch_name] = s.name
        , (
            SELECT i.name, i.column_id, i.user_type_id, i.is_nullable, i.is_identity
            FROM sys.all_columns i
            WHERE i.[object_id] = o.[object_id]
            FOR XML AUTO, TYPE
        )
    FROM sys.all_objects o
    JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] IN ('U', 'V')
    FOR XML PATH('obj'), ROOT('objects')
)
INTO ##temp

DECLARE @sql NVARCHAR(4000) = 'bcp "SELECT * FROM ##temp" queryout "D:\sample.xml" -S ' + @@servername + ' -T -w -r -t'
EXEC sys.xp_cmdshell @sql

IF OBJECT_ID('tempdb.dbo.##temp') IS NOT NULL
    DROP TABLE ##temp

Для тех, у кого xp_cmdshell отключена нужно выполнить:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

В итоге по указанному пути у нас будет создан файл с такой вот структурой:

<objects>
  <obj obj_id="245575913" obj_name="DatabaseLog" sch_name="dbo">
    <i name="DatabaseLogID" column_id="1" user_type_id="56" is_nullable="0" is_identity="1" />
    <i name="PostTime" column_id="2" user_type_id="61" is_nullable="0" is_identity="0" />
    <i name="DatabaseUser" column_id="3" user_type_id="256" is_nullable="0" is_identity="0" />
    <i name="Event" column_id="4" user_type_id="256" is_nullable="0" is_identity="0" />
    <i name="Schema" column_id="5" user_type_id="256" is_nullable="1" is_identity="0" />
    <i name="Object" column_id="6" user_type_id="256" is_nullable="1" is_identity="0" />
    <i name="TSQL" column_id="7" user_type_id="231" is_nullable="0" is_identity="0" />
    <i name="XmlEvent" column_id="8" user_type_id="241" is_nullable="0" is_identity="0" />
  </obj>
  ...
  <obj obj_id="1237579447" obj_name="Employee" sch_name="HumanResources">
    <i name="BusinessEntityID" column_id="1" user_type_id="56" is_nullable="0" is_identity="0" />
    <i name="NationalIDNumber" column_id="2" user_type_id="231" is_nullable="0" is_identity="0" />
    <i name="LoginID" column_id="3" user_type_id="231" is_nullable="0" is_identity="0" />
    <i name="OrganizationNode" column_id="4" user_type_id="128" is_nullable="1" is_identity="0" />
    <i name="OrganizationLevel" column_id="5" user_type_id="52" is_nullable="1" is_identity="0" />
    <i name="JobTitle" column_id="6" user_type_id="231" is_nullable="0" is_identity="0" />
    <i name="BirthDate" column_id="7" user_type_id="40" is_nullable="0" is_identity="0" />
    ...
  </obj>
  ...
</objects>

Теперь начнем заборные эксперименты…

Как наиболее эффективно загрузить данные из XML? Наверное, не нужно открывать файл блокнотом, копировать содержимое и вставлять в переменную… Думаю, что правильнее будет воспользоваться OPENROWSET:

DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x

SELECT @xml

Но тут есть забавный подвох. Как оказалось, совмещение операций загрузки и парсинга значений из XML может приводить к существенному снижению производительности. Допустим нам нужно получить значения obj_id из ранее созданного файла:

;WITH cte AS 
(
    SELECT x = CAST(BulkColumn AS XML)
    FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x
)
SELECT t.c.value('@obj_id', 'INT')
FROM cte
CROSS APPLY x.nodes('objects/obj') t(c)

На моей машине этот запрос выполняется очень долго:

(495 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 20788, ..., lob logical reads 7817781, ..., lob read-ahead reads 1022368.
 SQL Server Execution Times:
   CPU time = 53688 ms,  elapsed time = 53911 ms.

Попробуем разделить загрузку и парсинг:

DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x

SELECT t.c.value('@obj_id', 'INT')
FROM @xml.nodes('objects/obj') t(c)

Все отработало очень быстро:

(1 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 7, ..., lob logical reads 2691, ..., lob read-ahead reads 344.
 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 51 ms.

(495 row(s) affected)
 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 125 ms.

Так в чем же была проблема? Давайте проанализируем план выполнения:



Как оказалось, проблема кроется в преобразовании типов, поэтому старайтесь изначально передавать в функцию nodes параметр в типе XML.

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

Для наглядности сказанного покажу, что в данном запросе функция value будет выполнена дважды:

SELECT t.c.value('@obj_id', 'INT')
FROM @xml.nodes('objects/obj') t(c)
WHERE t.c.value('@obj_id', 'INT') < 0

(404 row(s) affected)
 SQL Server Execution Times:
   CPU time = 116 ms,  elapsed time = 120 ms.

Данный нюанс может снижать производительность, поэтому рекомендуется сокращать вызовы функций:

SELECT *
FROM (
    SELECT id = t.c.value('@obj_id', 'INT')
    FROM @xml.nodes('objects/obj') t(c)
) t
WHERE t.id < 0

(404 row(s) affected)
 SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 74 ms.

Как вариант можно фильтровать еще так:

SELECT t.c.value('@obj_id', 'INT')
FROM @xml.nodes('objects/obj[@obj_id < 0]') t(c)

(404 row(s) affected)
 SQL Server Execution Times:
   CPU time = 110 ms,  elapsed time = 119 ms.

но говорить о существенно выигрыше не приходится. Хотя QueryCost говорит об обратном:



Показано, что третий вариант самый оптимальный… Пусть это будет еще одним аргументом не доверять QueryCost, который является всего лишь внутренней оценкой.

И самый интересный пример на закуску… Есть еще одна ОЧЕНЬ важная особенность при парсинге из XML. Выполним запрос:

SELECT
      t.c.value('../@obj_name', 'SYSNAME')
    , t.c.value('@name', 'SYSNAME')
FROM @xml.nodes('objects/obj/*') t(c)

и посмотрим на время выполнения, которое может устроить только тех, кто уже никуда не торопится:

(5273 row(s) affected)
 SQL Server Execution Times:
   CPU time = 66578 ms,  elapsed time = 66714 ms.

Почему это происходит? SQL Server сервер имеет проблемы в операциях чтения родительских узлов из дочерних (если проще говорить, то SQL Server тяжело «смотреть назад»):



Как же нам в таком случае быть? Все очень просто… начинать чтение с родительских узлов и вычитывать дочерние с помощью CROSS/OUTER APPLY:

SELECT
      t.c.value('@obj_name', 'SYSNAME')
    , t2.c2.value('@name', 'SYSNAME')
FROM @xml.nodes('objects/obj') t(c)
CROSS APPLY t.c.nodes('*') t2(c2)

(5273 row(s) affected)
 SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 184 ms.

Еще интересно рассмотреть ситуацию, когда нам нужно посмотреть на 2 уровня выше. Проблема со чтением родительского элемента у меня не воспроизвелась:

USE AdventureWorks2012
GO

DECLARE @xml XML
SELECT @xml = (
    SELECT
          [@obj_name] = o.name
        , [columns] = (
            SELECT i.name
            FROM sys.all_columns i
            WHERE i.[object_id] = o.[object_id]
            FOR XML AUTO, TYPE
        )
    FROM sys.all_objects o
    WHERE o.[type] IN ('U', 'V')
    FOR XML PATH('obj')
)

SELECT
      t.c.value('../../@obj_name', 'SYSNAME')
    , t.c.value('@name', 'SYSNAME')
FROM @xml.nodes('obj/columns/*') t(c)

Еще хотел упомянуть об одной интересной особенности. Проблем с чтением родительских элементов OPENXML не имеет:

DECLARE
      @xml XML
    , @idoc INT

SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x

EXEC sys.sp_xml_preparedocument @idoc OUTPUT, @xml 

SELECT *
FROM OPENXML(@idoc, '/objects/obj/*') 
WITH (
    name  SYSNAME '../@obj_name', 
    col   SYSNAME '@name'
)

EXEC sys.sp_xml_removedocument @idoc

(5273 row(s) affected)
 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 137 ms.

Но не нужно теперь думать, что OPENXML имеет явные преимущества над XQuery. У OPENXML тоже хватает косяков. Например, если мы забываем вызывать sp_xml_removedocument, то могут возникать сильные утечки памяти.

Все тестировалось на SQL Server 2012 SP3 (11.00.6020).

Если хотите поделиться этой статьей с англоязычной аудиторией:
XML, XQuery & Perfomance Issues