Поездка в Днепропетровск, хронический недосып последние пару дней, но приятный бонус по приезду в Харьков… Зимняя погодка, которая мотивирует на написание чего-то интересного…
Давно уже хотелось рассказать про «подводные камни» при работе с 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, который является всего лишь внутренней оценкой.
И самый интересный пример на закуску… Есть еще одна ОЧЕНЬ важная особенность при парсинге из 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 сервер имеет проблемы в операциях чтения родительских узлов:
Как же нам в таком случае быть? Все очень просто… начинать чтение с родительских узлов и вычитывать дочерние с помощью 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.
ссылка на оригинал статьи http://habrahabr.ru/post/273189/
Добавить комментарий