Лучший способ измельчить данные XML в столбцы базы данных SQL Server

Каков наилучший способ разбить данные XML на различные столбцы базы данных? До сих пор я в основном использовал узлы и функции значений следующим образом:

INSERT INTO some_table (column1, column2, column3)
SELECT
Rows.n.value('(@column1)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int'),
FROM @xml.nodes('//Rows') Rows(n)

Тем не менее, я считаю, что это происходит очень медленно даже для XML-данных среднего размера.

8 ответов

Решение

Наткнулся на этот вопрос, хотя у меня была очень похожая проблема. Я выполнял запрос, обрабатывающий XML-файл объемом 7,5 МБ (около 10000 узлов), в течение 3,5–4 часов, прежде чем окончательно сдаться.

Однако после небольшого исследования я обнаружил, что после ввода XML-кода с использованием схемы и создания XML-индекса (я бы вставил его в таблицу) тот же запрос был выполнен за ~ 0,04 мс.

Как это для улучшения производительности!

Код для создания схемы:

IF EXISTS ( SELECT * FROM sys.xml_schema_collections where [name] = 'MyXmlSchema')
DROP XML SCHEMA COLLECTION [MyXmlSchema]
GO

DECLARE @MySchema XML
SET @MySchema = 
(
    SELECT * FROM OPENROWSET
    (
        BULK 'C:\Path\To\Schema\MySchema.xsd', SINGLE_CLOB 
    ) AS xmlData
)

CREATE XML SCHEMA COLLECTION [MyXmlSchema] AS @MySchema 
GO

Код для создания таблицы с типизированным столбцом XML:

CREATE TABLE [dbo].[XmlFiles] (
    [Id] [uniqueidentifier] NOT NULL,

    -- Data from CV element 
    [Data] xml(CONTENT dbo.[MyXmlSchema]) NOT NULL,

CONSTRAINT [PK_XmlFiles] PRIMARY KEY NONCLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Код для создания индекса

CREATE PRIMARY XML INDEX PXML_Data
ON [dbo].[XmlFiles] (Data)

Есть несколько вещей, которые следует иметь в виду, хотя. Реализация схемы в SQL Server не поддерживает xsd:include. Это означает, что если у вас есть схема, которая ссылается на другую схему, вам придется скопировать все это в одну схему и добавить ее.

Также я получил бы ошибку:

XQuery [dbo.XmlFiles.Data.value()]: Cannot implicitly atomize or apply 'fn:data()' to complex content elements, found type 'xs:anyType' within inferred type 'element({http://www.mynamespace.fake/schemas}:SequenceNumber,xs:anyType) ?'.

если я попытался перейти выше узла, который я выбрал с помощью функции узлов. Например

SELECT
    ,C.value('CVElementId[1]', 'INT') AS [CVElementId]
    ,C.value('../SequenceNumber[1]', 'INT') AS [Level]
FROM 
    [dbo].[XmlFiles]
CROSS APPLY
    [Data].nodes('/CVSet/Level/CVElement') AS T(C)

Обнаружено, что лучший способ справиться с этим - использовать OUTER APPLY для выполнения "внешнего соединения" в XML.

SELECT
    ,C.value('CVElementId[1]', 'INT') AS [CVElementId]
    ,B.value('SequenceNumber[1]', 'INT') AS [Level]
FROM 
    [dbo].[XmlFiles]
CROSS APPLY
    [Data].nodes('/CVSet/Level') AS T(B)
OUTER APPLY
    B.nodes ('CVElement') AS S(C)

Надеюсь, что это кому-то поможет, так как это был мой день.

В моем случае я использую SQL 2005 SP2 (9.0).

Единственное, что помогло, - это добавление OPTION ( OPTIMIZE FOR ( @your_xml_var = NULL)). Объяснение находится по ссылке ниже.

Пример:

INSERT INTO @tbl (Tbl_ID, Name, Value, ParamData)
SELECT     1,
    tbl.cols.value('name[1]', 'nvarchar(255)'),
    tbl.cols.value('value[1]', 'nvarchar(255)'),
    tbl.cols.query('./paramdata[1]')
FROM @xml.nodes('//root') as tbl(cols) OPTION ( OPTIMIZE FOR ( @xml = NULL ) )

https://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1

Я не уверен, какой метод самый лучший. Я использовал OPENXML конструкцию:

INSERT INTO Test
SELECT Id, Data 
FROM OPENXML (@XmlDocument, '/Root/blah',2)
WITH (Id   int         '@ID',
      Data varchar(10) '@DATA')

Чтобы ускорить его, вы можете создавать XML-индексы. Вы можете установить индекс специально для оптимизации производительности функции значения. Также вы можете использовать типизированные столбцы XML, которые работают лучше.

У нас была похожая проблема здесь. Наш DBA (SP, вы человек) посмотрел на мой код, немного изменил синтаксис, и мы получили ожидаемую скорость. Это было необычно, потому что мой выбор из XML был достаточно быстрым, но вставка была слишком медленной. Так что попробуйте этот синтаксис вместо этого:

INSERT INTO some_table (column1, column2, column3)
    SELECT 
        Rows.n.value(N'(@column1/text())[1]', 'varchar(20)'), 
        Rows.n.value(N'(@column2/text())[1]', 'nvarchar(100)'), 
        Rows.n.value(N'(@column3/text())[1]', 'int')
    FROM @xml.nodes('//Rows') Rows(n) 

Таким образом, указание параметра text() действительно влияет на производительность. Взял нашу вставку из 2К строк из "Я, должно быть, написал неправильно - позвольте мне остановить это" примерно до 3 секунд. Это было в 2 раза быстрее, чем необработанные операторы вставки, которые мы выполняли через соединение.

Я бы не стал утверждать, что это "лучшее" решение, но для этой цели я написал общую процедуру SQL CLR - она ​​берет "табличную" XML-структуру (например, возвращенную FOR XML RAW) и выводит набор результатов,

Он не требует каких-либо настроек / знаний о структуре "таблицы" в Xml и оказывается чрезвычайно быстрым / эффективным (хотя это не было целью разработки). Я просто уничтожил 25-мегабайтную (нетипизированную) переменную XML менее чем за 20 секунд, получив 25 000 строк довольно широкой таблицы.

Надеюсь, что это кому-то поможет: http://architectshack.com/ClrXmlShredder.ashx

Это не ответ, а скорее дополнение к этому вопросу - я только что столкнулся с той же проблемой, и я могу привести цифры, как edg просит в комментарии.

Мой тест имеет xml, что приводит к вставке 244 записей - 244 узла.

Код, который я переписываю, выполняется в среднем за 0,4 секунды.(10 тестов выполняются, разброс от.56 секунд до 344 секунд). Производительность - не главная причина, по которой код переписывается, но новый код также должен работать или лучше. Этот старый код зацикливает узлы xml, вызывая sp для вставки один раз за цикл

Новый код в значительной степени просто один sp; передать XML в; измельчите это.

Тесты с включенным новым кодом показывают, что новый sp занимает в среднем 3,7 секунды - почти в 10 раз медленнее.

Мой запрос находится в форме, размещенной в этом вопросе;

INSERT INTO some_table (column1, column2, column3)
SELECT
Rows.n.value('(@column1)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int'),
FROM @xml.nodes('//Rows') Rows(n)

План выполнения, по-видимому, показывает, что для каждого столбца сервер SQL выполняет отдельную "Табличную функцию [XMLReader]", возвращая все 244 строки, объединяя все резервные копии с помощью Nested Loops(Inner Join). Так что в моем случае, когда я делаю / вставляю в около 30 столбцов, это происходит по отдельности 30 раз.

Мне придется выбросить этот код, я не думаю, что какая-либо оптимизация преодолеет этот метод по своей сути медленным. Я собираюсь попробовать метод sp_xml_preparedocument/OPENXML и посмотреть, будет ли производительность лучше для этого. Если кто-то сталкивается с этим вопросом из веб-поиска (как я), я настоятельно рекомендую вам провести тестирование производительности, прежде чем использовать этот тип измельчения в SQL Server.

Существует COM-объект массовой загрузки XML ( пример.NET)

Из MSDN:

Вы можете вставить данные XML в базу данных SQL Server, используя инструкцию INSERT и функцию OPENXML; однако утилита "Массовая загрузка" обеспечивает лучшую производительность, когда вам нужно вставить большие объемы данных XML.

Мое текущее решение для больших наборов XML (> 500 узлов) состоит в том, чтобы использовать групповое копирование SQL (System.Data.SqlClient.SqlBulk Copy), используя DataSet для загрузки XML в память, а затем передать таблицу в SqlBulkCopy (определение схемы XML помогает).

Очевидно, что есть подводные камни, такие как ненужное использование DataSet и загрузка всего документа в память. Я хотел бы пойти дальше и реализовать свой собственный IDataReader для обхода метода DataSet, но в настоящее время DataSet "достаточно хорош" для этой работы.

По сути, я никогда не находил решения моего первоначального вопроса о низкой производительности для такого типа уничтожения XML. Он может быть медленным из-за того, что типизированные XML-запросы по своей сути медлительны или связаны с транзакциями и журналом SQL Server. Я предполагаю, что типизированные функции xml никогда не были разработаны для работы с нетривиальными размерами узлов.

Массовая загрузка XML: я попробовал это, и это было быстро, но у меня были проблемы с установкой COM-библиотеки DLL для работы в 64-битных средах, и я обычно стараюсь избегать библиотек COM, которые больше не поддерживаются.

sp_xml_preparedocument / OPENXML: я никогда не шел по этому пути, поэтому было бы интересно посмотреть, как он работает.

Другие вопросы по тегам