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

Откуда: Москва
Сообщений: 63
Помогите понять ситуацию. Есть xml, довольной больной, больше 2000 записей, размер строки примерно 4 млн. знаков. Нужно из него вставить данные в таблицу. В тупую, никаких джоинов.

Первый вариант (в примере только 2 столбца, на самом деле штук 50):

INSERT INTO Tbl (Col1, Col2)
SELECT x.rows.value('Col1[1]', 'uniqueidentifier') AS Col1, x.rows.value('Col2[1]', 'int') AS Col2 
FROM @XML.nodes('/DS/Tbl') x(rows)

Выполняется очень долго. Ждал наверно минут 15, потом прибил.

Второй вариант, через временную таблицу:

SELECT x.rows.value('Col1[1]', 'uniqueidentifier') AS Col1, x.rows.value('Col2[1]', 'int') AS Col2 
INTO #tmp 
FROM @XML.nodes('/DS/Tbl') x(rows)

INSERT INTO Tbl (Col1, Col2) 
SELECT Col1, Col2 
FROM #tmp

Выполнился за 13 секунд.

Объясните в чем тут фишка? Первый вариант пробовал много раз и на разных инстансах.

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
16 июн 09, 12:03    [7304189]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Создай тестовый пример - скрипт таблицы и генерации XML. Если тестовый пример ведёт себя также, выкладывай его сюда.
17 июн 09, 00:26    [7307838]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
Если полей много, то может стоит отказаться от методов типа Node и вернуться к OPENXML ?
С уважением, Алексей
17 июн 09, 08:33    [7308096]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Aleksey-K
Если полей много, то может стоит отказаться от методов типа Node и вернуться к OPENXML ?
Хотя на вопрос "почему" это не отчечает. :)
А ещё может и XMLBulkLoad лучше подойдёт, задача то не известна.
17 июн 09, 12:28    [7309567]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
alex407
Member

Откуда: Москва
Сообщений: 63
Ну, время второго запроса в 13 секунд меня более чем устраивает, так что варианты другие мне искать уже не надо.
Как просили скрипты для теста.

Таблица:
CREATE TABLE [dbo].[Table1](
	[Fileld1] [varchar](1024) NULL,
	[Fileld2] [varchar](1024) NULL,
	[Fileld3] [varchar](1024) NULL,
	[Fileld4] [varchar](1024) NULL,
	[Fileld5] [varchar](1024) NULL,
	[Fileld6] [varchar](1024) NULL,
	[Fileld7] [varchar](1024) NULL,
	[Fileld8] [varchar](1024) NULL,
	[Fileld9] [varchar](1024) NULL,
	[Fileld10] [varchar](1024) NULL,
	[Fileld11] [varchar](1024) NULL,
	[Fileld12] [varchar](1024) NULL,
	[Fileld13] [varchar](1024) NULL,
	[Fileld14] [varchar](1024) NULL,
	[Fileld15] [varchar](1024) NULL,
	[Fileld16] [varchar](1024) NULL,
	[Fileld17] [varchar](1024) NULL,
	[Fileld18] [varchar](1024) NULL,
	[Fileld19] [varchar](1024) NULL,
	[Fileld20] [varchar](1024) NULL
) ON [PRIMARY]

Скрипт с формированием XML и запросами:
--Формируем XML
PRINT CONVERT(VARCHAR(20), GETDATE(), 109)
DECLARE @Field NVARCHAR(max), @Record NVARCHAR(max), @AllRecords NVARCHAR(max), @XML XML, @i INT

SET @i = 1
SET @Record = ''
WHILE @i <= 20 BEGIN
	SET @Record = @Record + '<Fileld' + CAST(@i AS NVARCHAR(2)) + '>SampleTextSampleTextSampleTextSampleTextSampleTextSampleTextSampleTextSampleText</Fileld' + CAST(@i AS NVARCHAR(2)) + '>'
	SET @i += 1          	
END
SET @Record = '<Table1>' + @Record + '</Table1>'

SET @i = 0
SET @AllRecords = ''
WHILE @i < 2000 BEGIN
	SET @AllRecords = @AllRecords + @Record
	SET @i += 1          	
END
SET @AllRecords = '<DS>' + @AllRecords + '</DS>'
SET @XML = @AllRecords
PRINT CONVERT(VARCHAR(20), GETDATE(), 109)

-- Запрос 1
INSERT INTO Table1 (Fileld1, Fileld2, Fileld3, Fileld4, Fileld5, Fileld6, Fileld7, Fileld8, Fileld9, Fileld10, Fileld11, Fileld12, Fileld13, Fileld14, Fileld15, Fileld16, Fileld17, Fileld18, Fileld19, Fileld20)   SELECT x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld1, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld2, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld3, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld4, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld5, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld6, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld7, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld8, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld9, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld10, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld11, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld12, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld13, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld14, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld15, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld16, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld17, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld18, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld19, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld20 FROM @XML.nodes('/DS/Table1') x(rows)

-- Запрос 2
SELECT x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld1, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld2, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld3, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld4, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld5, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld6, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld7, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld8, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld9, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld10, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld11, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld12, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld13, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld14, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld15, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld16, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld17, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld18, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld19, x.rows.value('Fileld1[1]', 'varchar(1024)') AS Fileld20 INTO #tmp FROM @XML.nodes('/DS/Table1') x(rows)  INSERT INTO Table1 (Fileld1, Fileld2, Fileld3, Fileld4, Fileld5, Fileld6, Fileld7, Fileld8, Fileld9, Fileld10, Fileld11, Fileld12, Fileld13, Fileld14, Fileld15, Fileld16, Fileld17, Fileld18, Fileld19, Fileld20) SELECT Fileld1, Fileld2, Fileld3, Fileld4, Fileld5, Fileld6, Fileld7, Fileld8, Fileld9, Fileld10, Fileld11, Fileld12, Fileld13, Fileld14, Fileld15, Fileld16, Fileld17, Fileld18, Fileld19, Fileld20 FROM #tmp

PRINT CONVERT(VARCHAR(20), GETDATE(), 109)

XML получается по размеру приблизительно такой как у меня в реальной задаче.
На формирование XML у меня уходит около минуты.
Второй запрос отрабатывает 3 СЕКУНДЫ!!!
Сколько отрабатывает первый с 2000 записей я не знаю, т.к. окончания не дождался и срубил его после 30 МИНУТ выполнения.

Вот такие пироги, а как у вас?
17 июн 09, 13:40    [7310175]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> Вот такие пироги, а как у вас?

оба варианта ~3 секунды.

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft
Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

Posted via ActualForum NNTP Server 1.4

17 июн 09, 14:09    [7310361]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31194
alex407
На формирование XML у меня уходит около минуты.

Вот такие пироги, а как у вас?
А у меня намного меньше секунды - я вместо цикла:
SET @i = 0
SET @AllRecords = ''
WHILE @i < 2000 BEGIN
	SET @AllRecords = @AllRecords + @Record
	SET @i += 1          	
END
Написал оператор:
SET @AllRecords = '<DS>' + REPLICATE(@Record, 2000) + '</DS>'

И всё вместе меньше секунды выполнялось:
--Скрипт с формированием XML и запросами:
--Формируем XML
PRINT CONVERT(VARCHAR(20), GETDATE(), 109)
DECLARE @Field NVARCHAR(max), @Record NVARCHAR(max), @AllRecords NVARCHAR(max), @XML XML, @i INT

SET @i = 1
SET @Record = ''
WHILE @i <= 20 BEGIN
	SET @Record = @Record + '<Fileld' + CAST(@i AS NVARCHAR(2)) + '>SampleTextSampleTextSampleTextSampleTextSampleTextSampleTextSampleTextSampleText</Fileld' + CAST(@i AS NVARCHAR(2)) + '>'
	SET @i += 1          	
END
SET @Record = '<Table1>' + @Record + '</Table1>'

--SET @i = 0
--SET @AllRecords = ''
--WHILE @i < 2000 BEGIN
--	SET @AllRecords = @AllRecords + @Record
--	SET @i += 1          	
--END

SET @AllRecords = '<DS>' + REPLICATE(@Record, 2000) + '</DS>'

SET @XML = @AllRecords

PRINT CONVERT(VARCHAR(20), GETDATE(), 109)
declare @hdoc int
exec sp_xml_preparedocument @hdoc output, @XML
select @hdoc, LEN(@AllRecords) as [len]
PRINT CONVERT(VARCHAR(20), GETDATE(), 109)

INSERT INTO Table1 (Fileld1, Fileld2, Fileld3, Fileld4, Fileld5, Fileld6, Fileld7, Fileld8, Fileld9, Fileld10, Fileld11, Fileld12, Fileld13, Fileld14, Fileld15, Fileld16, Fileld17, Fileld18, Fileld19, Fileld20) 
SELECT    Fileld1, Fileld2, Fileld3, Fileld4, Fileld5, Fileld6, Fileld7, Fileld8, Fileld9, Fileld10, Fileld11, Fileld12, Fileld13, Fileld14, Fileld15, Fileld16, Fileld17, Fileld18, Fileld19, Fileld20
FROM       OPENXML (@hdoc, '/DS/Table1', 2)
WITH (
	Fileld1       nvarchar(100)       'Fileld1',
	Fileld2       nvarchar(100)       'Fileld2',
	Fileld3       nvarchar(100)       'Fileld3',
	Fileld4       nvarchar(100)       'Fileld4',
	Fileld5       nvarchar(100)       'Fileld5',
	Fileld6       nvarchar(100)       'Fileld6',
	Fileld7       nvarchar(100)       'Fileld7',
	Fileld8       nvarchar(100)       'Fileld8',
	Fileld9       nvarchar(100)       'Fileld9',
	Fileld10       nvarchar(100)       'Fileld10',
	Fileld11       nvarchar(100)       'Fileld11',
	Fileld12       nvarchar(100)       'Fileld12',
	Fileld13       nvarchar(100)       'Fileld13',
	Fileld14       nvarchar(100)       'Fileld14',
	Fileld15       nvarchar(100)       'Fileld15',
	Fileld16       nvarchar(100)       'Fileld16',
	Fileld17       nvarchar(100)       'Fileld17',
	Fileld18       nvarchar(100)       'Fileld18',
	Fileld19       nvarchar(100)       'Fileld19',
	Fileld20       nvarchar(100)       'Fileld20'
)

PRINT CONVERT(VARCHAR(20), GETDATE(), 109)
exec sp_xml_removedocument @hdoc
PRINT CONVERT(VARCHAR(20), GETDATE(), 109)
17 июн 09, 14:39    [7310625]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
alex407
Member

Откуда: Москва
Сообщений: 63
daw
оба варианта ~3 секунды.


Странно, я на трех разных компах пробовал, в том числе и на том, на котором как и у тебя не стоит SP1. Во всех случаях первый запрос работал катастрофически медленно. Никакие перегрузки не помогают.

Попробовал на другом инстансе одного из этих компов и о чудо, первый запрос отработал за 3 секунды. Версия сервера такая же. Странно все это.

alexeyvg. Да с REPLICATE оно пошустрее будет :-)

С OPENXML на всех серверах работает быстро, около секунды.
17 июн 09, 15:32    [7311035]     Ответить | Цитировать Сообщить модератору
 Re: XML. Скорость вставки.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Странно, но либо оба жутко тормозят (9.00.3159.00) либо оба летают (9.00.4035.00).
17 июн 09, 15:41    [7311116]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить