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

Откуда:
Сообщений: 1069
Коллеги, помогите разобраться с таким вопросом:

Имеется xml-файл примерно такой структуры:
<?xml version="1.0" encoding="Windows-1251"?>
<PERS_LIST>
<ZGLV>
<VERSION>2.0</VERSION>
<DATA>2013-01-17</DATA>
<FILENAME>OUTDESC_XXX</FILENAME>
</ZGLV>
<PERS>
<ID_PAC>ХХХХХХ</ID_PAC>
<FAM>ПЕТРОВА</FAM>
<IM>ЛЮДМИЛА</IM>
<OT>ВАСИЛЬЕВНА</OT>
<W>2</W>
<DR>1982-01-07</DR>
<FAM_P></FAM_P>
<IM_P></IM_P>
<OT_P></OT_P>
<W_P></W_P>
<DR_P></DR_P>
<MR></MR>
<DOCTYPE>01</DOCTYPE>
<DOCSER>11 10</DOCSER>
<DOCNUM>333553</DOCNUM>
<SNILS>001-520-146 31</SNILS>
<OKATOG>10230</OKATOG>
<OKATOP></OKATOP>
<COMENTP></COMENTP>
</PERS>
</PERS_LIST>


Имеется сервер:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

На сервере имеется хранимка, которая в частности делает вот что:
	declare @x as xml
	Select @x=XmlData from dbo.Files where CntFile=@CntFile

	insert into dbo.PERS (CntFile, ID_PAC, FAM, IM, OT, W, DR, FAM_P, 
	IM_P, OT_P, W_P, DR_P, MR, DOCTYPE, DOCSER, DOCNUM, SNILS, OKATOG, OKATOP, COMENTP)
	select @CntFile,   
	  n.value('(./ID_PAC)[1]', 'nvarchar(36)') ID_PAC,
	  n.value('(./FAM)[1]', 'nvarchar(40)') FAM,
	  n.value('(./IM)[1]', 'nvarchar(40)') IM,
	  n.value('(./OT)[1]', 'nvarchar(40)') OT,
	  n.value('(./W)[1]', 'int') W,
	  n.value('(./DR)[1]', 'datetime') DR,
	  Nullif(n.value('(./FAM_P)[1]', 'nvarchar(40)'),'') FAM_P,
	  Nullif(n.value('(./IM_P)[1]', 'nvarchar(40)'),'') IM_P,
	  Nullif(n.value('(./OT_P)[1]', 'nvarchar(40)'),'') OT_P,
	  Nullif(n.value('(./W_P)[1]', 'int'),'') W_P,
	  Nullif(n.value('(./DR_P)[1]', 'datetime'),'') DR_P,
	  Nullif(n.value('(./MR)[1]', 'nvarchar(100)'),'') MR,
	  Nullif(n.value('(./DOCTYPE)[1]', 'nvarchar(2)'),'') DOCTYPE,
	  Nullif(n.value('(./DOCSER)[1]', 'nvarchar(10)'),'') DOCSER,
	  Nullif(n.value('(./DOCNUM)[1]', 'nvarchar(20)'),'') DOCNUM,
	  Nullif(n.value('(./SNILS)[1]', 'nvarchar(14)'),'') SNILS,
	  n.value('(./OKATOG)[1]', 'nvarchar(11)') OKATOG,
	  Nullif(n.value('(./OKATOP)[1]', 'nvarchar(11)'),'') OKATOP,
	  Nullif(n.value('(./COMENTZ)[1]', 'nvarchar(250)'),'') COMENTZ
		from @x.nodes('PERS_LIST/PERS') t(n)


xml-файлик - довольно большой, около 50 мб, порядка 20 000 записей, хранится в соответствующем xml поле другой таблицы. По этому полю даже есть xml индекс.
Собственно запрос, без insert into dbo.PERS, отрабатывает за 10-15 секунд. А вот в таком виде - вешается неопределенно (больше 7 минут ждать - терпение вышло), грузит полностью одно ядро из 8 возможных. В профайлере - ни локов, ни ошибок. Только невнятное "Сканирование начато", и тишина.

Но! Когда у БД указал уровень совместимости - 2005 (90) - хранимка отработала "мгновенно", секунд за 15.
Уровень совместимости пришло в голову указать, т.к. имеется аналогичная база на том же сервере, с данными, в которой всё работает.
Та, в которой твориться безобразие, создана из ее частичного скрипта.

Как такое может быть вообще?

На целевой таблице индексов, кроме кластерного по автоинкрементному полю - нет. Есть внешний ключ On Delete и всё.

Это бред или баг?
... и чей :)
31 янв 13, 22:43    [13859590]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Гость333
Member

Откуда:
Сообщений: 3683
uaggster
Это бред или баг?
... и чей :)

Это баг Microsoft: https://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1
1 фев 13, 08:47    [13860147]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
1. Множество обращений к xml-методам - очень неэффективно. Я понимаю, что это задалбывает, но такие вещи лучше через OPENXML переписывать.
2. Если у вас в таблице на этом поле есть XML-индекс, то зачем вы копируете содержимое в переменную? Он же не используется при этом.

Попробуйте оба варианта, возможно один из них решит проблему. XML же в 2005 только появился, так что в каждой версии его активно перелопачивают. Вполне возможно, что в вашем билде где-то внесли баг.
1 фев 13, 08:49    [13860150]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
uaggster
Member

Откуда:
Сообщений: 1069
Ennor Tiegael
1. Множество обращений к xml-методам - очень неэффективно. Я понимаю, что это задалбывает, но такие вещи лучше через OPENXML переписывать.
2. Если у вас в таблице на этом поле есть XML-индекс, то зачем вы копируете содержимое в переменную? Он же не используется при этом.

1. К сожалению, "мопед не мой, я только разместил объявление". Через OPENXML попытаюсь переписать
2. Ennor Tiegael, пожалуйста, помогите поправить синтаксис на "без использования промежуточной переменной". В мозгах еще нет соответствующего перелома, не могу сообразить, как это сделать! :-)
1 фев 13, 08:59    [13860173]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
uaggster
Member

Откуда:
Сообщений: 1069
Гость333, понял, спасибо!
1 фев 13, 09:00    [13860175]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3422
uaggster,

Скелет запроса:
insert into dbo.Pers (...)
select f.CntFile,
  t.n.value('(./ID_PAC)[1]', 'nvarchar(36)') ID_PAC,
  ...
from dbo.Files f
  cross apply f.Xmldata.nodes('PERS_LIST/PERS') t(n)
where f.CntFile=@CntFile;

И не пренебрегайте алиасами. Самому же потом проще разбираться будет.
1 фев 13, 09:16    [13860205]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
uaggster
Member

Откуда:
Сообщений: 1069
Ennor Tiegael, большое спасибо!
Запрос - переделал. Радикально, правда, производительность не выросла. :-)
Но прирост по ошущениям - есть.
1 фев 13, 09:34    [13860284]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
OPENXML на больших объемах работает намного быстрее XQuery, я сам не раз это замечал. И в ХП лучше использовать именно его.
1 фев 13, 09:47    [13860329]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
XML только осваиваю:)
вот что получилось:
+ локальные данные
if OBJECT_ID('tempdb..#pers','U') is not null drop table #pers
create table #PERS (
	CntFile int, ID_PAC nvarchar(36), 
	FAM nvarchar(40), IM nvarchar(40), OT nvarchar(40), W int, DR datetime, 
	FAM_P nvarchar(40), IM_P nvarchar(40), OT_P nvarchar(40), W_P int, DR_P datetime, MR nvarchar(100), 
	DOCTYPE nvarchar(2), DOCSER nvarchar(10), DOCNUM nvarchar(20), 
	SNILS nvarchar(14), OKATOG nvarchar(11), OKATOP nvarchar(11), COMENTP nvarchar(250))
--declare @x as xml=N'<?xml version="1.0" encoding="Windows-1251"?>
--Сообщение 9402, уровень 16, состояние 1, строка 9
--Синтаксический анализ XML: строка 1, символ 46, невозможно переключить кодировку

-- https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=839167&msg=10435349
declare @x as xml=N'
<PERS_LIST>
<ZGLV>
<VERSION>2.0</VERSION>
<DATA>2013-01-17</DATA>
<FILENAME>OUTDESC_XXX</FILENAME>
</ZGLV>
<PERS>
<ID_PAC>ХХХХХХ</ID_PAC>
<FAM>ПЕТРОВА</FAM>
<IM>ЛЮДМИЛА</IM>
<OT>ВАСИЛЬЕВНА</OT>
<W>2</W>
<DR>1982-01-07</DR>
<FAM_P></FAM_P>
<IM_P></IM_P>
<OT_P></OT_P>
<W_P></W_P>
<DR_P></DR_P>
<MR></MR>
<DOCTYPE>01</DOCTYPE>
<DOCSER>11 10</DOCSER>
<DOCNUM>333553</DOCNUM>
<SNILS>001-520-146 31</SNILS>
<OKATOG>10230</OKATOG>
<OKATOP></OKATOP>
<COMENTP></COMENTP>
</PERS>
</PERS_LIST>'
--	Select @x=XmlData from dbo.Files where CntFile=@CntFile
DECLARE @DocHandle int
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @X
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT    *
FROM      OPENXML (@DocHandle, 'PERS_LIST/PERS',2)
           WITH #PERS
EXEC sp_xml_removedocument @DocHandle
"По ощущениям" - почти мгновенно:)
1 фев 13, 10:30    [13860474]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
Cygapb-007
вот что получилось:
"По ощущениям" - почти мгновенно:)

На таком объеме и XQuery будет "почти мгновенно".
Данные:
create table xml_test (xdata xml);
insert into xml_test
  select v.xdata from (
    select * from sys.all_objects for xml path, root, type
  ) v(xdata)

(в sys.all_objects ~400 000 записей).
Тест 1:
declare
  @d datetime;
set @d=GETDATE();
select
  t.n.value('./name[1]', 'nvarchar(128)') name,
  t.n.value('./object_id[1]', 'int') object_id,
  t.n.value('./principal_id[1]', 'int') principal_id,
  t.n.value('./schema_id[1]', 'int') schema_id,
  t.n.value('./parent_object_id[1]', 'int') parent_object_id,
  t.n.value('./type[1]', 'char(2)') type,
  t.n.value('./type_desc[1]', 'nvarchar(60)') type_desc,
  t.n.value('./create_date[1]', 'datetime') create_date,
  t.n.value('./modify_date[1]', 'datetime') modify_date,
  t.n.value('./is_ms_shipped[1]', 'bit') is_ms_shipped,
  t.n.value('./is_published[1]', 'bit') is_published,
  t.n.value('./is_schema_published[1]', 'bit') is_schema_published
into #t_temp
from dbo.xml_test x cross apply x.xdata.nodes('/root/row') t(n)
select DATEDIFF(MS, @d, GETDATE());

время отработки - 124760 миллисекунд.
Тест 2:
declare
  @d datetime,
  @x xml,
  @h int;
select @x=xdata from xml_test;
set @d=GETDATE();
exec sp_xml_preparedocument @h out, @x;
select * into #t_temp from openxml(@h, '/root/row', 2) with(
  name nvarchar(128),
  object_id int,
  principal_id int,
  schema_id int,
  parent_object_id int,
  type char(2),
  type_desc nvarchar(60),
  create_date datetime,
  modify_date datetime,
  is_ms_shipped bit,
  is_published bit,
  is_schema_published bit
);
exec sp_xml_removedocument @h;
select DATEDIFF(MS, @d, GETDATE());

время отработки - 52083 миллисекунды.
1 фев 13, 13:19    [13861547]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
P.S. Тестировал на локальном сервере:
select @@version

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4270.0 (Intel X86) 
Nov 30 2012 17:15:09
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
1 фев 13, 13:22    [13861571]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Сон Веры Павловны
в sys.all_objects ~400 000 записей

Офигеть. А что там, если не секрет?
1 фев 13, 13:25    [13861600]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
daw
Member

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

а так, если?
declare
  @d datetime;
set @d=GETDATE();
select
  t.n.value('(name/text())[1]', 'nvarchar(128)') name,
  t.n.value('(object_id/text())[1]', 'int') object_id,
  t.n.value('(principal_id/text())[1]', 'int') principal_id,
  t.n.value('(schema_id/text())[1]', 'int') schema_id,
  t.n.value('(parent_object_id/text())[1]', 'int') parent_object_id,
  t.n.value('(type/text())[1]', 'char(2)') type,
  t.n.value('(type_desc/text())[1]', 'nvarchar(60)') type_desc,
  t.n.value('(create_date/text())[1]', 'datetime') create_date,
  t.n.value('(modify_date/text())[1]', 'datetime') modify_date,
  t.n.value('(is_ms_shipped/text())[1]', 'bit') is_ms_shipped,
  t.n.value('(is_published/text())[1]', 'bit') is_published,
  t.n.value('(is_schema_published/text())[1]', 'bit') is_schema_published
into #t_temp
from xml_test x cross apply x.xdata.nodes('/root/row') t(n)
select DATEDIFF(MS, @d, GETDATE());
1 фев 13, 13:46    [13861786]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
uaggster
Member

Откуда:
Сообщений: 1069
Cygapb-007, Ну, вообще то в этом xml-е тегов <PERS> ожидается 10-100 тыс.
Запрос в оригинальном варианте отрабатывает за ~15-60 c, с учетом выдачи результата на экран, в правильном варианте, предложенным Ennor Tiegael - за 8-20 с. Но подробнее, в профайлере не смотрел.
В принципе, с OPENXML тоже надо попробовать, но не критично. Это оффлайновая обработка, главное, чтобы за ночь успевала :)
1 фев 13, 13:53    [13861836]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6202
daw
а так, если?

Разница небольшая и во времени, и в самом запросе - немного измененный XPath, на вытаскивание данных по которому и приходятся основные затраты.
Да, кстати: в атрибутной нотации (for xml raw) время разбора через XQuery меньше примерно на 20 секунд. Думаю, вряд ли такое число можно списать на локальные флюктуации системы.

> Офигеть. А что там, если не секрет?
Так ить. ERP-базёнка.
1 фев 13, 13:59    [13861883]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
uaggster
Member

Откуда:
Сообщений: 1069
Мне вот только интересно, почему процесс только одно ядро жрёт! Сервер тестовый, не нагружен совсем, ядер 8, памяти - 32 Гб. 4 только занято.
У сервера в свойствах максимальная степень параллелизма - 4, стоимостный порог - 5.

Так вообще должно быть?
1 фев 13, 14:00    [13861889]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
daw
Member

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

> Разница небольшая и во времени...

эмм... у чего с чем?
у меня получается
~ 52000мс - openquery
~ 36000мс - t.n.value('(name/text())[1]', 'nvarchar(128)') name
~ 94000мс - исходный t.n.value('./name[1]', 'nvarchar(128)') name

а уж если оставить в xml только, например, name и object_id, то openquery стабильно проигрывает тому варианту, что я привел: в 2,5 раза примерно.

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
1 фев 13, 15:03    [13862334]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
да, на тех же ~400 000 игрался. я, правда, cross join-ом sys.all_objects самой с собой такое количество получил, но, полагаю это без разницы.
1 фев 13, 15:06    [13862359]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Гость333
Member

Откуда:
Сообщений: 3683
daw
~ 52000мс - openquery

Можно взглянуть на код этого openquery?
1 фев 13, 15:07    [13862366]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> Можно взглянуть на код этого openquery?

дичайше извиняюсь, что ввел в заблуждение!

~ 52000мс - t.n.value('(name/text())[1]', 'nvarchar(128)') name
~ 36000мс - openquery
~ 94000мс - исходный t.n.value('./name[1]', 'nvarchar(128)') name

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

код точно тот, что привел "Сон Веры Павловны"

declare
  @d datetime,
  @x xml,
  @h int;
select @x=xdata from xml_test;
set @d=GETDATE();
exec sp_xml_preparedocument @h out, @x;
select * into #t_temp from openxml(@h, '/root/row', 2) with(
  name nvarchar(128),
  object_id int,
  principal_id int,
  schema_id int,
  parent_object_id int,
  type char(2),
  type_desc nvarchar(60),
  create_date datetime,
  modify_date datetime,
  is_ms_shipped bit,
  is_published bit,
  is_schema_published bit
);
exec sp_xml_removedocument @h;
1 фев 13, 15:19    [13862433]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
Гость333
Member

Откуда:
Сообщений: 3683
daw,

А, понял, вы openxml имели в виду.
1 фев 13, 15:22    [13862447]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
> А, понял, вы openxml имели в виду.

ах ты ж еж... да и второй раз повторил. да-да, конечно же, openxml.
1 фев 13, 15:27    [13862489]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить