Как известно для загрузки XML-документов в таблицы базы данных можно использовать функцию openxml в сочетании с хранимыми процедурами sp_xml_preparedocument и sp_xml_removedocument. Эти процедуры используют объектную модель DOM. При этом XML-документ полностью загружается в оперативную память. Тестирование показывает, что когда объем XML-данных становится громадным, то данная методика напрягает оперативную память и работает очень медленно. Более того, если объем XML большой по сравнению с имеющейся памятью, то метод может отработать с ошибками.
В этой статье мы расскажем об ином подходе к загрузке XML-данных в таблицу SQL Server. Можно воспользоваться компонентом SQL XML Bulk Load. Компонент SQL XML Bulk Load не напрягает оперативную память. Он разбивает большой XML-документ на меньшие фрагменты и обрабатывает их независимо.
Компонент SQL XML Bulk Load является компонентом COM. Таким образом его можно применять в любом языке, поддерживающем OLE-автоматизацию. Я сравниваю его с инструкцией bulk insert, которая также используется при загрузке в базу больших объемов данных из файлов. Когда создается экземпляр SQLXMLBulkLoad, то у этого объекта есть несколько свойств. Наиболее важные среди них такие. Свойство ConnectionString используется для соединения с сервером. Свойство ErrorLogFile указывает файл, в котором будет собираться информация о возможных ошибках, например, данные о поврежденном XML-файле или данные об ошибках на стороне сервера. Для блокирования таблицы можно применять свойство ForceTableLock. Когда оно включено, то таблицу нельзя прочесть с nolock в ходе массовой загрузки. Также есть много других свойств, похожих на те, которые есть у инструкции bilk insert. Например, можно установить свойства, которые позволяют отключать триггеры, проверку уникальности, проверку пустых полей и прочие ограничения целостности.
Загрузка данных в таблицу начинается с вызова метода Execute, который имеет 2 параметра. Первый параметр это имя XML-документа, содержащего схему, которая описывает структуру документа и данные мэппинге между узлами XML и объектами базы данных. Второй параметр это имя файла с данными. Метод Execute выполняет нелогируемую высокоскоростную операцию загрузки.
Есть еще одно замечательное свойство этого компонента. Несмотря на то, что таблица может быть заблокирована для других соединений в процессе работы SQL XML, можно создать несколько соединений, которые одновременно наполняют таблицу из одного или нескольких XML-файлов. Они не будут друг друга блокировать и загрузка может стать еще более быстрой. Для управления параллельными соединениями можно использовать Service Broker. Пример этого есть в одной из моих предыдущих статей: Многопоточные операции и компонент Service Broker.
Ниже мы будут созданы фрагменты кода для загрузки xml с помощью SQLXMLBulkLoad. Также приведем сравнительные данные о скорости работы SQLXMLBulkLoad и функции openxml.
Перед началом работы на серверном компьютере необходимо установить компонент SQLXML. После этого для создания объекта воспользуется процедурами sp_OA...
begin try
set @step = N'Объект'
exec sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload.4.0', @han out
if @res <> 0 raiserror ( @step, 16, 1 )
if @res <> 0 raiserror ( @step, 16, 1 )
set @step = N'Блокировка'
exec @res = sp_OASetProperty @han, 'ForceTableLock', 1
if @res <> 0 raiserror ( @step, 16, 1 )
set @step = N'Строка подклоючения'
exec @res = sp_OASetProperty @han, 'ConnectionString',
'Provider=SQLNCLI10;Server=EDYNAK\SHADOW;Database=TestClr;Trusted_Connection=Yes'
if @res <> 0 raiserror ( @step, 16, 1 )
if @res <> 0 raiserror ( @step, 16, 1 )
end try
begin catch
if error_message () = @step
begin
exec sp_OAGetErrorInfo @han, @src out, @desc out
set @Err = N'Ошибка в работе компонента. Операция: ' + @step + N'. Номер: ' +
cast ( @res as nvarchar ( 100 ) ) + '; описание: ' + isnull ( @desc,
N'неизвестно' ) + N'; источник: ' + isnull ( @src, N'неизвестно' )
end
else
begin
set @Err = N'Ошибка в работе сервера. Номер: ' + isnull ( cast ( error_number ()
as nvarchar ( 100 ) ), N'' ) + '; описание: ' + isnull ( error_message (),
N'неизвестно' ) + N'; номер строки: ' + isnull ( cast ( error_line () as
nvarchar ( 100 ) ), N'неизвестно' )
end
end catch
begin
raiserror ( @Err, 16, 1 )
end
Файл data.xml имеет простую структуру, выглядит он так:
<test>
<DatesList>
<OneDate>2013-01-01</OneDate>
</DatesList>
<DatesList>
<OneDate>2014-01-01</OneDate>
</DatesList>
</test>
То есть в таблица должна содержать один столбец типа datetime, в который будет загружаться одно и то же значение. В файле sch.xml будет информация о структуре документа. Также схема должна нести информацию о том в какую таблицу делать загрузку и какой столбец соответствует узлу OneDate. Выглядит схема так:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="test">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="unbounded" name="DatesList" sql:relation="LogDate">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OneDate" type="xsd:date" sql:field="dtDate" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Это обычная в целом XML-схема. Но надо особо обратить внимание на использование пространства имен sql. Именно оно позволяет сообщить о том, что узлу DatesList соответствует таблица LogDate, а элементу OneDate соответствует столбец dtDate.
Для проверки того насколько быстрее работает SQL XML по сравнению с функцией openxml приведем код, который решает ту же задачу загрузки с помощью функции openxml.
declare @han int, @xml xml, @Err nvarchar ( max ), @filename nvarchar ( 300 ) = 'C:\Документы\Временные\test\data.xml', @sql nvarchar ( max )
begin try
set @sql = N'
select @xml = BulkColumn
from openrowset ( bulk ''' + @filename + ''', single_clob ) MyFile
'
exec sp_executesql @sql, N'@xml xml out', @xml = @xml out
exec sp_xml_preparedocument @han out, @xml
insert into dbo.LogDate with ( tablock ) ( dtDate )
select dtDate
from openxml ( @han, N'/test/DatesList', 1 )
with ( dtDate date 'OneDate' )
end try
begin catch
set @Err = N'Ошибка в работе сервера. Номер: ' + isnull ( cast ( error_number () as
nvarchar ( 100 ) ), N'' ) + '; описание: ' + isnull ( error_message (),
N'неизвестно' ) + N'; номер строки: ' + isnull ( cast ( error_line () as nvarchar
( 100 ) ), N'неизвестно' )
end catch
if isnull ( @han, 0 ) > 0
begin
raiserror ( @Err, 16, 1 )
end
Я проводил тестирование на компьютере с операционной системой Windows 7 x64, объемом оперативной памяти 4 Гб, с одним 4-х ядерным процессором. В файле data.xml сначала было около 1-ого млн. элементов OneDate. Затем для каждого нового теста я удваивал число узлов в нем. В таблице представлены результаты по продолжительности работы процедур загрузки.
При загрузке документов с помощью openxml, в которых содержалось 8-16 млн. элементов, объем памяти, который использовался сервером, увеличился с 0.4 до 1.9 Гб. Компьютер стал жутко тормозить. Видно, что при использовании openxml загрузка начинает работать на порядок медленнее. Есть еще одна опасность: поскольку процедуре sp_xml_preparedocument надо передавать переменную с текстом документа, то объем файлов, которые могут быть таким способом импортированы ограничены 2-я Гб, ввиду ограничений на типы данных (n)char, (n)varchar, image, xml. При использовании SQLXML Bulk Load таких ограничений нет.
В этой статье мы расскажем об ином подходе к загрузке XML-данных в таблицу SQL Server. Можно воспользоваться компонентом SQL XML Bulk Load. Компонент SQL XML Bulk Load не напрягает оперативную память. Он разбивает большой XML-документ на меньшие фрагменты и обрабатывает их независимо.
Компонент SQL XML Bulk Load является компонентом COM. Таким образом его можно применять в любом языке, поддерживающем OLE-автоматизацию. Я сравниваю его с инструкцией bulk insert, которая также используется при загрузке в базу больших объемов данных из файлов. Когда создается экземпляр SQLXMLBulkLoad, то у этого объекта есть несколько свойств. Наиболее важные среди них такие. Свойство ConnectionString используется для соединения с сервером. Свойство ErrorLogFile указывает файл, в котором будет собираться информация о возможных ошибках, например, данные о поврежденном XML-файле или данные об ошибках на стороне сервера. Для блокирования таблицы можно применять свойство ForceTableLock. Когда оно включено, то таблицу нельзя прочесть с nolock в ходе массовой загрузки. Также есть много других свойств, похожих на те, которые есть у инструкции bilk insert. Например, можно установить свойства, которые позволяют отключать триггеры, проверку уникальности, проверку пустых полей и прочие ограничения целостности.
Загрузка данных в таблицу начинается с вызова метода Execute, который имеет 2 параметра. Первый параметр это имя XML-документа, содержащего схему, которая описывает структуру документа и данные мэппинге между узлами XML и объектами базы данных. Второй параметр это имя файла с данными. Метод Execute выполняет нелогируемую высокоскоростную операцию загрузки.
Есть еще одно замечательное свойство этого компонента. Несмотря на то, что таблица может быть заблокирована для других соединений в процессе работы SQL XML, можно создать несколько соединений, которые одновременно наполняют таблицу из одного или нескольких XML-файлов. Они не будут друг друга блокировать и загрузка может стать еще более быстрой. Для управления параллельными соединениями можно использовать Service Broker. Пример этого есть в одной из моих предыдущих статей: Многопоточные операции и компонент Service Broker.
Ниже мы будут созданы фрагменты кода для загрузки xml с помощью SQLXMLBulkLoad. Также приведем сравнительные данные о скорости работы SQLXMLBulkLoad и функции openxml.
Перед началом работы на серверном компьютере необходимо установить компонент SQLXML. После этого для создания объекта воспользуется процедурами sp_OA...
declare @han int, @res int, @filename nvarchar ( 300 ) = 'C:\Документы\Временные\test\data.xml', @logfile nvarchar ( 300 ) = 'C:\Документы\Временные\test\sch.xml',
@src varchar ( 300 ), @desc varchar ( 300 ), @step nvarchar ( 100 ), @Err nvarchar ( max )begin try
set @step = N'Объект'
exec sp_OACreate 'SQLXMLBulkLoad.SQLXMLBulkload.4.0', @han out
if @res <> 0 raiserror ( @step, 16, 1 )
set @step = N'Лог'
exec @res = sp_OASetProperty @han, 'ErrorLogFile', 'C:\Документы\Временные\test\log.log'if @res <> 0 raiserror ( @step, 16, 1 )
set @step = N'Блокировка'
exec @res = sp_OASetProperty @han, 'ForceTableLock', 1
if @res <> 0 raiserror ( @step, 16, 1 )
set @step = N'Строка подклоючения'
exec @res = sp_OASetProperty @han, 'ConnectionString',
'Provider=SQLNCLI10;Server=EDYNAK\SHADOW;Database=TestClr;Trusted_Connection=Yes'
if @res <> 0 raiserror ( @step, 16, 1 )
set @step = N'Загрузка'
exec @res = sp_OAMethod @han, 'Execute', null, @logfile, @filenameif @res <> 0 raiserror ( @step, 16, 1 )
end try
begin catch
if error_message () = @step
begin
exec sp_OAGetErrorInfo @han, @src out, @desc out
set @Err = N'Ошибка в работе компонента. Операция: ' + @step + N'. Номер: ' +
cast ( @res as nvarchar ( 100 ) ) + '; описание: ' + isnull ( @desc,
N'неизвестно' ) + N'; источник: ' + isnull ( @src, N'неизвестно' )
end
else
begin
set @Err = N'Ошибка в работе сервера. Номер: ' + isnull ( cast ( error_number ()
as nvarchar ( 100 ) ), N'' ) + '; описание: ' + isnull ( error_message (),
N'неизвестно' ) + N'; номер строки: ' + isnull ( cast ( error_line () as
nvarchar ( 100 ) ), N'неизвестно' )
end
end catch
exec
sp_OADestroy @han
if
isnull ( @Err, N'' ) <> N''begin
raiserror ( @Err, 16, 1 )
end
Файл data.xml имеет простую структуру, выглядит он так:
<test>
<DatesList>
<OneDate>2013-01-01</OneDate>
</DatesList>
<DatesList>
<OneDate>2014-01-01</OneDate>
</DatesList>
</test>
То есть в таблица должна содержать один столбец типа datetime, в который будет загружаться одно и то же значение. В файле sch.xml будет информация о структуре документа. Также схема должна нести информацию о том в какую таблицу делать загрузку и какой столбец соответствует узлу OneDate. Выглядит схема так:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="test">
<xsd:complexType>
<xsd:sequence>
<xsd:element maxOccurs="unbounded" name="DatesList" sql:relation="LogDate">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="OneDate" type="xsd:date" sql:field="dtDate" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
Это обычная в целом XML-схема. Но надо особо обратить внимание на использование пространства имен sql. Именно оно позволяет сообщить о том, что узлу DatesList соответствует таблица LogDate, а элементу OneDate соответствует столбец dtDate.
Для проверки того насколько быстрее работает SQL XML по сравнению с функцией openxml приведем код, который решает ту же задачу загрузки с помощью функции openxml.
declare @han int, @xml xml, @Err nvarchar ( max ), @filename nvarchar ( 300 ) = 'C:\Документы\Временные\test\data.xml', @sql nvarchar ( max )
begin try
set @sql = N'
select @xml = BulkColumn
from openrowset ( bulk ''' + @filename + ''', single_clob ) MyFile
'
exec sp_executesql @sql, N'@xml xml out', @xml = @xml out
exec sp_xml_preparedocument @han out, @xml
insert into dbo.LogDate with ( tablock ) ( dtDate )
select dtDate
from openxml ( @han, N'/test/DatesList', 1 )
with ( dtDate date 'OneDate' )
end try
begin catch
set @Err = N'Ошибка в работе сервера. Номер: ' + isnull ( cast ( error_number () as
nvarchar ( 100 ) ), N'' ) + '; описание: ' + isnull ( error_message (),
N'неизвестно' ) + N'; номер строки: ' + isnull ( cast ( error_line () as nvarchar
( 100 ) ), N'неизвестно' )
end catch
if isnull ( @han, 0 ) > 0
begin
exec sp_xml_removedocument @han
end
if isnull ( @Err, N'' ) <> N''begin
raiserror ( @Err, 16, 1 )
end
Я проводил тестирование на компьютере с операционной системой Windows 7 x64, объемом оперативной памяти 4 Гб, с одним 4-х ядерным процессором. В файле data.xml сначала было около 1-ого млн. элементов OneDate. Затем для каждого нового теста я удваивал число узлов в нем. В таблице представлены результаты по продолжительности работы процедур загрузки.
Объем файла (число млн. узлов OneDate)
|
Время
работы компонента SQL
XML Bulk Load
|
Время
работы функции openxml
|
1
|
5
сек.
|
23
сек.
|
2
|
10
сек.
|
47
сек.
|
4
|
22
сек.
|
1
мин. 41 сек.
|
8
|
1
мин.
|
5 мин. 49 сек.
|
16
|
2 мин. 58 сек.
|
1 час 13 мин. 28 сек.
|
При загрузке документов с помощью openxml, в которых содержалось 8-16 млн. элементов, объем памяти, который использовался сервером, увеличился с 0.4 до 1.9 Гб. Компьютер стал жутко тормозить. Видно, что при использовании openxml загрузка начинает работать на порядок медленнее. Есть еще одна опасность: поскольку процедуре sp_xml_preparedocument надо передавать переменную с текстом документа, то объем файлов, которые могут быть таким способом импортированы ограничены 2-я Гб, ввиду ограничений на типы данных (n)char, (n)varchar, image, xml. При использовании SQLXML Bulk Load таких ограничений нет.
Комментариев нет:
Отправить комментарий