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

Ответ 1

Наткнувшись на этот вопрос, имея очень похожую проблему, я работал над обработкой XML файла размером 7,5 МБ (~ около 10 000 узлов) примерно за 3,5-4 часа, прежде чем, наконец, отказаться.

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

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

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

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) ?'.

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

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)

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

Ответ 2

в моем случае я запускаю 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

Ответ 3

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

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

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

Ответ 4

У нас была аналогичная проблема. Наш 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() действительно влияет на производительность. Взял нашу вставку из 2K строк из "Я, должно быть, написал это неправильно, позвольте мне остановить его" примерно до 3 секунд. Это было в 2 раза быстрее, чем исходные операторы вставки, которые мы использовали через соединение.

Ответ 5

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

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

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

Ответ 6

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

В моем тесте есть xml, в результате которого вставлено 244 записи - так что 244 узла.

Код, который я переписываю, занимает в среднем 0,4 секунды для запуска. (10 прогонов запускаются с интервалом от 0,56 с до 0,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 строки, объединяя все резервные копии с вложенными циклами (Inner Join). Итак, в моем случае, когда я измельчаю/вставляю около 30 столбцов, это, как представляется, происходит раздельно 30 раз.

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

Ответ 7

Существует XML Bulk load COM-объект (.NET Пример)

От MSDN:

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

Ответ 8

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

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

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

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

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