Импорт и анализ большого XML-файла в SQL Server (когда обычные методы работают довольно медленно)

У меня есть большой файл XML, который мне нужно импортировать и преобразовать в табличную структуру («свести») в SQL Server. Под «большим» я подразумеваю файл размером около 450 МБ, содержащий до 6-7 вложенных уровней и множество элементов, ~300.

Я попытался проанализировать файл с помощью OPENXML и Xml.Nodes. Оба метода медленные. Частичный запрос, который считывает родительский элемент и его вложенные внуки, выполняется несколько минут, если не десятков.

Я попытался использовать метод массовой загрузки SQLXML. К сожалению, я не мог - потому что файл неправильно структурирован. Существует элемент, который логически является родительским элементом, который физически не вложен как родитель.

Считаете ли вы, что единственным оставшимся решением является использование .NET или Java? Есть что-то, что мне не хватает?

В какой-то степени я бы предпочел динамическое решение. Я не хочу, чтобы разработчики SQL Server передавали процедурный, скомпилированный код, о котором они не имеют контроля/знаний, в случае, если произойдут некоторые изменения (в структуре XML).

Большое спасибо.


xml sql-server xml-parsing openxml sqlxml
person Ido Gal    schedule 27.05.2014    source источник
comment
Вы сводите свой XML к табличной структуре?   -  person Santiago Cepas    schedule 27.05.2014
comment
Верно. Сглаживание в некоторой степени - в зависимости от структуры базы данных, которую еще предстоит полностью определить.   -  person Ido Gal    schedule 27.05.2014
comment
Мне бы очень хотелось знать, какое время выполнения у вас было для OPENXML и XML.nodes соответственно.   -  person Mikael Eriksson    schedule 27.05.2014
comment
ХОРОШО. Кажется, я что-то упустил и неправильно использовал Xml.Nodes(). После устранения проблемы результаты довольно интересные. 4:42 секунды для запроса OPENXML. 0:47 для запроса Nodes(). Вау, совсем другая разница! Во всяком случае, я построил еще один запрос, который берет один элемент дедушки с 5-го уровня, несколько с 8-го уровня и несколько с 9-го уровня. Потребовалось 1:08 минуты для 9155 строк результатов, которые я получил. Не так уж и плохо, в конце концов. Тем не менее, может быть, это немного медленно для извлечения всего XML? Комментарии приветствуются.   -  person Ido Gal    schedule 27.05.2014
comment
Можете ли вы показать запрос? Там могут быть другие вещи, которые вы могли бы попробовать. Например, использование родительской оси может быть плохой вещью, а указание узла text() в предложении значений также полезно для производительности.   -  person Mikael Eriksson    schedule 28.05.2014
comment
Какой другой метод я могу использовать, кроме родительской оси, чтобы получить дочерние элементы родителя? Я использовал функцию text().   -  person Ido Gal    schedule 29.05.2014
comment
Вы можете складывать cross apply nodes(). В вашем случае похоже, что первый вызов узлов должен быть уничтожен на пятом уровне, второй - на уровне восьмерок, а последнее перекрестное применение должно быть на девятом уровне. stackoverflow.com/a/22216941/569436   -  person Mikael Eriksson    schedule 29.05.2014
comment
Вопрос по использованию родительской оси. XML-запрос с перекрестным применением работает экспоненциально хуже по мере роста XML-документа   -  person Mikael Eriksson    schedule 13.06.2014
comment
Я уже пришел к такому же выводу. Спасибо.   -  person Ido Gal    schedule 15.06.2014

Ответы (3)


arrow_upward
1
arrow_downward

ХОРОШО. Я создал индекс XML для столбца данных XML. (Только основной на данный момент).

Запрос, который раньше занимал ~4:30 минуты, теперь занимает ~9 секунд! Кажется, что таблица, в которой хранится XML с правильным XML-индексом, и анализ данных с помощью функции xml.nodes() являются возможным решением.

Спасибо вам всем.

person Ido Gal    schedule 05.06.2014

arrow_upward
0
arrow_downward

Поскольку вам нужна табличная структура, вы можете преобразовать XML в файл CSV (используя этот java или этот инструмент .NET или даже преобразование XSLT), а затем выполнить массовую вставку.

Конечно, все это зависит от того, правильно ли сформирован ваш XML.

person Santiago Cepas    schedule 27.05.2014
comment
Спасибо Сантьяго. Я бы предпочел использовать решение с большей надежностью, даже создавая приложение .NET. Использование csv напрашивается на неприятности. - person Ido Gal; 27.05.2014

arrow_upward
0
arrow_downward

Ну, во-первых, я не совсем понимаю, зачем использовать OpenXml для загрузки файла. Я почти уверен, что это приведет к внутреннему запуску целой группы тестов на достоверность в соответствии со стандартом OpenXml ISO.

Но - Xml.Nodes() (я предполагаю, что это означает способ загрузки данных DOM) - безусловно, самый медленный способ загрузки и анализа данных Xml. Вместо этого рассмотрите подход SAX с использованием XmlReader или аналогичного. Я понимаю, что статья 2004 года, но она все еще довольно хорошо объясняет.

person Jesper Lund Stocholm    schedule 28.05.2014
comment
ХОРОШО. Итак, вы говорите, что для получения хорошей производительности я должен отказаться от XML-движка SQL Server (и кода SQL) и написать код .NET? (впоследствии интегрируя его в CLR и т.д...) - person Ido Gal; 28.05.2014
comment
ну, вы сказали, что вместо этого рассматриваете возможность написания приложения .Net, но, возможно, я неправильно понял :-). Но я узнал кое-что новое - я никогда не слышал о ключевом слове OPENXML в SQL Server до вашего поста, поэтому я явно неправильно понял /эту/ часть. - person Jesper Lund Stocholm; 30.05.2014