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

Откуда:
Сообщений: 864
Коллеги, приветствую!

Есть вот такой xml:

<snils_zl_list>
  <zglv filename="051161PS01001" nfile="001" version="1.0" cod_pfr="051" nrec="100000" />
  <snils>001-012-075 46</snils>
  <snils>001-443-200 35</snils>
  <snils>001-370-159 64</snils>
  <snils>001-560-922 75</snils>
  <snils>001-127-481 84</snils>
  <snils>001-161-713 86</snils>
  <snils>001-218-036 57</snils>
</snils_zl_list>


Пытаюсь распарсить его таким запросом:
;with fxml as 
	(
    SELECT x = CAST(BulkColumn AS XML).query('.')
    FROM OPENROWSET(BULK '\\ssq001\C$\files\051161PS01001.XML', SINGLE_BLOB) xl
	Union all
    SELECT CAST(BulkColumn AS XML).query('.')
    FROM OPENROWSET(BULK '\\ssq001\C$\files\051161PS01002.XML', SINGLE_BLOB) xl
	Union all
    SELECT CAST(BulkColumn AS XML).query('.')
    FROM OPENROWSET(BULK '\\ssq001\C$\files\051161PS01003.XML', SINGLE_BLOB) xl
	)
Select
	x.value('(/snils_zl_list/zglv[1]/@filename)[1]', 'nvarchar(255)') [filename]
	,t.n.value('text()[1]', 'nvarchar(14)') [sn] 
	from fxml
	Cross apply x.nodes('/snils_zl_list/snils') t(n)

И получаю на выходе жутчайщие тормоза. Запрос выполняется порядка 10 часов.
В xml файле - 100000 записей.

Помогите переписать запрос!

+

Я думаю, что ситуация аналогична кейсу Сергея Сыроватченко ( )
Но по аналогии переписать запрос у меня не получилось.
/*
    24 HOP Russian 2016
    Session: XML, XQuery и трюки с производительностью
    Speaker: Sergey Syrovatchenko, Devart
    E-Mail:  sergeys@devart.com
*/

USE tempdb
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON

SELECT
      t.c.value('../@obj_name', 'SYSNAME')
    , t.c.value('@name', 'SYSNAME')
FROM dbo.data
CROSS APPLY x.nodes('objects/obj/*') t(c)

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

------------------------------------------------------

USE AdventureWorks2012
GO

DECLARE @xml 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)
GO

------------------------------------------------------

USE tempdb
GO

DECLARE @xml XML = (SELECT x FROM dbo.data),
        @idoc INT

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

23 авг 16, 09:00    [19576796]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с низкой производительностью парсинга xml  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Доброго дня. Попробуйте такое извращение:

DECLARE @x1 XML
      , @x2 XML
      , @x3 XML

DECLARE @filename1 NVARCHAR(255)
      , @filename2 NVARCHAR(255)
      , @filename3 NVARCHAR(255)

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

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

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

SELECT @filename1 = @x1.value('(/snils_zl_list/zglv/@filename)[1]', 'NVARCHAR(255)')
     , @filename2 = @x2.value('(/snils_zl_list/zglv/@filename)[1]', 'NVARCHAR(255)')
     , @filename3 = @x3.value('(/snils_zl_list/zglv/@filename)[1]', 'NVARCHAR(255)')

SELECT *
FROM (
    SELECT @filename1, t.c.value('text()[1]', 'NVARCHAR(14)')
    FROM @x1.nodes('/snils_zl_list/snils') t(c)

    UNION ALL

    SELECT @filename2, t.c.value('text()[1]', 'NVARCHAR(14)')
    FROM @x1.nodes('/snils_zl_list/snils') t(c)

    UNION ALL

    SELECT @filename3, t.c.value('text()[1]', 'NVARCHAR(14)')
    FROM @x1.nodes('/snils_zl_list/snils') t(c)
) t (filename, sn)

Если не поможет, то укажите версию Вашего SQL Server-а и приложите планы выполнения в формате sqlplan
23 авг 16, 09:38    [19576933]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с низкой производительностью парсинга xml  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Немного опечатался.... @x1, @x1, @x1 на @x1, @x2, @x3 заменить и указать имена файлов уже Ваши. Плюс раз бы пытаетесь вытянуть xml c другого компа быть стоит нужно посмотреть на сетевые задержки. Например проверьте не растет ли ASYNC_NETWORK_IO
23 авг 16, 09:41    [19576956]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с низкой производительностью парсинга xml  [new]
aleks2
Guest
uaggster
Коллеги, приветствую!

И получаю на выходе жутчайщие тормоза. Запрос выполняется порядка 10 часов.
В xml файле - 100000 записей.

Помогите переписать запрос!


Учитесь схемы писать (тем более, что структура у вас простенькая) и изучайте sqlXmlBulkLoad.
Откосите за пару секунд.

Другого спасения вам не светит.
23 авг 16, 09:59    [19577033]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с низкой производительностью парсинга xml  [new]
uaggster
Member

Откуда:
Сообщений: 864
aleks2
Учитесь схемы писать (тем более, что структура у вас простенькая) и изучайте sqlXmlBulkLoad.
Откосите за пару секунд.

Другого спасения вам не светит.

Да тут дело в принципе как бы :-)
Структура простенькая, и ничего не предвещает. А тем не менее - тормозит. И 300000 раз сканирует CTE.
И если бы мне нужны были схемы - я бы спросил про схемы.

AlanDenton, большое спасибо за идею!!!
Переписал вот так:
;with fxml as 
	(
    SELECT x = CAST(BulkColumn AS XML).query('.')
    FROM OPENROWSET(BULK '\\ssq001\C$\files\051161PS01001.XML', SINGLE_BLOB) xl
	Union all
    SELECT CAST(BulkColumn AS XML).query('.')
    FROM OPENROWSET(BULK '\\ssq001\C$\files\051161PS01002.XML', SINGLE_BLOB) xl
	Union all
    SELECT CAST(BulkColumn AS XML).query('.')
    FROM OPENROWSET(BULK '\\ssq001\C$\files\051161PS01003.XML', SINGLE_BLOB) xl
	),
	fxml2 as 
	(Select
	x.value('(/snils_zl_list/zglv[1]/@filename)[1]', 'nvarchar(255)') [filename]
	,x.query('/snils_zl_list/snils') [SS] 
	from fxml) 
Select [filename], t.n.value('(/snils/text())[1]', 'nvarchar(14)') [SS] from fxml2
	Cross apply [SS].nodes('*') t(n)

Всё, вопрос решен, считает 8 секунд, этого достаточно.
Сервер был 2014SP2


AlanDenton, еще раз спасибо!
23 авг 16, 10:37    [19577218]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с низкой производительностью парсинга xml  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
uaggster, пожалуйста... zglv[1] к слову можно и без [1] использовать на один оператор в плане меньше будет. Рад что мой видос Вам понравился.
23 авг 16, 10:45    [19577257]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с низкой производительностью парсинга xml  [new]
uaggster
Member

Откуда:
Сообщений: 864
AlanDenton
uaggster, пожалуйста... zglv[1] к слову можно и без [1] использовать на один оператор в плане меньше будет. Рад что мой видос Вам понравился.

Вы имеете ввиду так?
	x.value('(/snils_zl_list/zglv/@filename)[1]', 'nvarchar(255)') [filename]

Выполняется, кстати, на 0.3c быстрее. Что, кстати, удивительно, потому что парсится вроде как должно всего 3 значения.

 
SQL Server Execution Times:
CPU time = 7625 ms, elapsed time = 7789 ms.

(289287 row(s) affected)

SQL Server Execution Times:
CPU time = 7813 ms, elapsed time = 8041 ms.
(289287 row(s) affected)
Но это, ИМХО, не совсем правильно! т.к. хоть и не бывает, но zglv может быть не один. Чем черт не шутит? Хотя, наверное, под такой случай и логика запроса должна быть сильно другая, надо подумать...

Кстати, говорите - это Ваша презентация?
Большое Вам СПАСИБО!
Узнал несколько очень важных и полезных вещей, и вообще - много нового. Конечно, занимаюсь чем то подобным редко, по теперь уже перешел из состояния "лишь бы работало" в состояние "а почему так медленно". И на несколько кейсов в презентации - прям 200% в тему.
:-)
23 авг 16, 13:50    [19578650]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с низкой производительностью парсинга xml  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
автор
Вы имеете ввиду так?

Да. Если разделить парсинг единичных значений и того что выгребать "ожидается" много, то получится быстрее. По аналогии с тем примером, что я первым привел (также в теории можно UNION ALL заменить на UNION это для FROM ... .nodes в некоторых случаях это будет немного быстрее).

автор
Кстати, говорите - это Ваша презентация?

Это особой роли не играет. Если маякнете на почту, что у Вас в спойлере была, то могу скинуть архив с примерами часть из которых в видео не вошли.
23 авг 16, 14:33    [19578991]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить