Запрос выбора Xml xpath работает медленно

Моя XML-структура:

<Items>
  <Item>
    <guid>FC550573-7171-997F-752D-8D65590CBFD6</guid>
    <Objects>
       <Object>
         <type>0</type>
         <guid>E10D9DA9-2C8D-8024-2F07-DF21395811BF</guid>
       </Object>
       <Object>
         <type>0</type>
         <guid>D8338400-35C7-781E-A039-C0FDDF80714A</guid>
       </Object>
    </Objects>
  </Item>
</Items>

При заполнении таблицы объектов:

CREATE TABLE [dbo].[Objects](
    [item_guid] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [type] [int] NOT NULL,
    [guid] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

Использование запроса:

INSERT INTO [dbname].[dbo].[Objects]
           ([item_guid]
           ,[type]
           ,[guid])
SELECT
 X.source.query('../../guid').value('.','VARCHAR(36)') as item_guid,
 X.source.query('type').value('.','INT') as type,
 X.source.query('guid').value('.','VARCHAR(36)') as guid
FROM(
Select xmldata from XmlFiles where fullpath=@fp
) AS T(x)
CROSS APPLY x.nodes('Items/Item/Objects/Object') As X(source)

Эта строка делает запрос ОЧЕНЬ медленным:

X.source.query('../../guid').value('.','VARCHAR(36)') as item_guid

Какой здесь правильный подход?


xml sql sql-server tsql sqlxml
person Mark Robbins    schedule 08.11.2012    source источник
comment
Ну, во-первых, вам не нужно X.source.query('type').value('.','INT') - было бы намного проще написать это как X.source.value('(type)[1]', 'INT') и сэкономить хотя бы одну операцию XQuery для каждого столбца, который вы оцениваете....   -  person marc_s    schedule 08.11.2012
comment
И ПОЧЕМУ вы храните GUID как тип столбца varchar(36)? Лучше всего было бы использовать UNIQUEIDENTIFIER — родной тип SQL Server Guid. Или, если вы не можете по какой-либо причине, то по крайней мере используйте char(36), так как это всегда будет ровно 36 символов - часть var совершенно не нужна (и создает только накладные расходы...)   -  person marc_s    schedule 08.11.2012
comment
Спасибо за вашу помощь, ПОЧЕМУ это то, что я идиот SQL, и как идиот я пытаюсь избежать неизвестных ошибок/ограничений, которые могут возникнуть с другими типами столбцов и с использованием того, с чем я знаком, пока Я создал прототип этого хака :) Кстати, у меня есть поле бесконечной длины в xml, помещает ли varchar(MAX) ВСЕ его в столбец TEXT?   -  person Mark Robbins    schedule 08.11.2012

Ответы (3)


arrow_upward
8
arrow_downward

Использование /text() для получения значения полезно для производительности нетипизированного XML. Также может быть плохо использовать родительскую ось ../.. (как предложил @marc_s).

Вот версия с дополнительным перекрестным применением и /text() для получения значений.

Попробуй это:

select T2.N.value('(guid/text())[1]', 'uniqueidentifier') as item_guid,
       T3.N.value('(type/text())[1]', 'int') as type,
       T3.N.value('(guid/text())[1]', 'uniqueidentifier') as guid
from (SELECT xmldata FROM dbo.XmlFiles WHERE fullpath = @fp) as T1(N)
  cross apply T1.N.nodes('Items/Item') as T2(N)
  cross apply T2.N.nodes('Objects/Object') as T3(N)

Вы должны быть судьей, какой запрос является самым быстрым для вас.

person Mikael Eriksson    schedule 08.11.2012
comment
Большое вам спасибо, text() значительно улучшил его, а затем перекрестное применение сделало даже больше, чем text() (у меня также была иерархия, поэтому у меня было много ../../, пытающихся поместить его в одну таблицу). У меня был огромный XML, и я получил его от (5 минут до 3 секунд) - person formatc; 22.03.2014
comment
Вау, text() потрясающий. В нашем случае это привело к огромным изменениям (с пары минут до менее секунды). Потрясающий совет! - person Ivan Peevski; 05.12.2014

arrow_upward
3
arrow_downward

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

OPTION (OPTIMIZE FOR (@testXml = NULL))

Если вы хотите проверить это самостоятельно, вот короткий тестовый сценарий, который я запускал. Просто посмотрите на предполагаемую стоимость поддерева между ними.

declare @testXml xml set @testXml = '<filters><filter name="test name" type="GREATERTHAN">1</filter><filter name="CLAIMID" type="GREATERTHAN">1</filter></filters>'


select x.value('@name','nvarchar(100) ') filtername, 
x.value('.','nvarchar(200)')filtervalue, 
x.value('@type','nvarchar(50) ') filtertype 
from @testXml.nodes('/filters/filter') as ref(x)
--vs...
select x.value('@name','nvarchar(100) ') filtername,  
x.value('.','nvarchar(200)')filtervalue,  
x.value('@type','nvarchar(50) ') filtertype 
from @testXml.nodes('/filters/filter') as ref(x) 
OPTION (OPTIMIZE FOR (@testXml = NULL))
person Alex Cottner    schedule 29.05.2015
comment
Еще одно небольшое обновление. SQL 2016/17, похоже, изменил функциональность этих подсказок запроса. Использование: ОПТИМИЗИРОВАТЬ ДЛЯ НЕИЗВЕСТНОГО - person Alex Cottner; 29.01.2020

arrow_upward
1
arrow_downward

Попробуй это,

Мы создадим переменную временной таблицы для хранения этих значений xml и вставки в соответствующую таблицу Objects.

//..Xml value to temp variable
Declare @x xml ='<Items><Item><guid>FC550573-7171-997F-752D-8D65590CBFD6</guid><Objects><Object>
                 <type>0</type><guid>E10D9DA9-2C8D-8024-2F07-DF21395811BF</guid></Object><Object>
                 <type>0</type><guid>D8338400-35C7-781E-A039-C0FDDF80714A</guid></Object></Objects>
                 </Item></Items>';

Declare @Temp_Tbl table (RowId int identity, item_guid nvarchar(36), [type] int, [guid] nvarchar(36));

Insert into @Temp_Tbl SELECT @x.value('(/Items/Item/guid)[1]', 'nvarchar(36)'),
   Cont.value('(type)[1]', 'int'),  Cont.value('(guid)[1]', 'nvarchar(36)')                                                                                     
   FROM @x.nodes('/Items/Item/Objects/Object') AS Obj(Cont);

INSERT INTO [dbo].[Objects] Select item_guid,[type],[guid] from @Temp_Tbl;
person Manikandan Sethuraju    schedule 08.11.2012