Запрос на вставку из xml по разному работает при разном уровне совместимости баз 90 и 100

uaggster

Дата: 31.01.2013 22:43:16

Коллеги, помогите разобраться с таким вопросом:

Имеется 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 и всё.

Это бред или баг?
... и чей :)

Гость333

Дата: 01.02.2013 08:47:27

uaggster
Это бред или баг? ... и чей :)

Это баг Microsoft: https://connect.microsoft.com/SQLServer/feedback/details/562092/an-insert-statement-using-xml-nodes-is-very-very-very-slow-in-sql2008-sp1

Ennor Tiegael

Дата: 01.02.2013 08:49:36

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

Попробуйте оба варианта, возможно один из них решит проблему. XML же в 2005 только появился, так что в каждой версии его активно перелопачивают. Вполне возможно, что в вашем билде где-то внесли баг.

uaggster

Дата: 01.02.2013 08:59:55

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

Ennor Tiegael

1. К сожалению, "мопед не мой, я только разместил объявление". Через OPENXML попытаюсь переписать
2. Ennor Tiegael, пожалуйста, помогите поправить синтаксис на "без использования промежуточной переменной". В мозгах еще нет соответствующего перелома, не могу сообразить, как это сделать! :-)

uaggster

Дата: 01.02.2013 09:00:21

Гость333, понял, спасибо!

Ennor Tiegael

Дата: 01.02.2013 09:16:40

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;

И не пренебрегайте алиасами. Самому же потом проще разбираться будет.

uaggster

Дата: 01.02.2013 09:34:44

Ennor Tiegael, большое спасибо!
Запрос - переделал. Радикально, правда, производительность не выросла. :-)
Но прирост по ошущениям - есть.

Сон Веры Павловны

Дата: 01.02.2013 09:47:40

OPENXML на больших объемах работает намного быстрее XQuery, я сам не раз это замечал. И в ХП лучше использовать именно его.

Cygapb-007

Дата: 01.02.2013 10:30:59

XML только осваиваю:)
вот что получилось:

+ локальные данные

--	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

"По ощущениям" - почти мгновенно:)

Сон Веры Павловны

Дата: 01.02.2013 13:19:14

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 миллисекунды.