XML, XPath и тройная печаль с производительностью16.12.2015 05:19

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

Уже давно в планах было рассказать про «подводные камни» при работе с 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


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


  
    
    
    
    
    
    
    
    
  
  ...
  
    
    
    
    
    
    
    
    ...
  
  ...


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

Как наиболее эффективно загрузить данные из 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.


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

59ffc4eec3d74bb4b7275006dd6446c0.png

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


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

de38d173c83f499686a1316d8f8fa308.png

Показано, что третий вариант самый оптимальный… Пусть это будет еще одним аргументом не доверять 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 сервер имеет проблемы в операциях чтения родительских узлов:

7d131f338cc34d76b0a57ff60555ab05.png

Как же нам в таком случае быть? Все очень просто… начинать чтение с родительских узлов и вычитывать дочерние с помощью 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).
Планы выполнения брал из dbForge.

© Habrahabr.ru