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

Откуда:
Сообщений: 165
 		Привет!

Помогите оптимизировать запрос. С количеством HotelID равным 2000 запрос #1 выполняется 24 секунды, попробовал сделать по другому ( запрос #2) - с тем же плохим результатом...
А по заданию HotelID может быть и вовсе 300 000 штук для одного RewardID.

Как можно улучшить его?

Вот такой запрос:
+
declare @xml xml

select @xml = '
<Root>
    <CouponHotelRestriction SequenceIndex="1">
        <CouponHotel RewardID="123" TPID="1" RestrictionTypeID="1">
            <Hotel HotelID="123" />
            <Hotel HotelID="124" />        
            <Hotel HotelID="125" />
        </CouponHotel>
        <CouponHotel RewardID="67" TPID="2" RestrictionTypeID="1">
            <Hotel HotelID="555" />
            <Hotel HotelID="777" />
        </CouponHotel>
    </CouponHotelRestriction>
    <CouponHotelRestriction SequenceIndex="2">
        <CouponHotel RewardID="678" TPID="3" RestrictionTypeID="1">
            <Hotel HotelID="999" />
        </CouponHotel>
    </CouponHotelRestriction>
</Root>
'
-- first variant
select  ROW_NUMBER() over (partition by hotel.value('../../@SequenceIndex', 'int') order by hotel.value('../../@SequenceIndex', 'int') ) as SequenceRowID,
        hotel.value('../../@SequenceIndex', 'int') as SequenceIndex,
        hotel.value('../@RewardID', 'int') as RewardID,
        hotel.value('../@TPID', 'int') as TPID,
        hotel.value('../@RestrictionTypeID', 'smallint') as RestrictionTypeID,
        hotel.value('@HotelID', 'int') as HotelID
from    @Xml.nodes('/Root/CouponHotelRestriction/CouponHotel/Hotel') col(hotel)

-- second variant
select  ROW_NUMBER() over (partition by hotel.value('../../@SequenceIndex', 'int') order by hotel.value('../../@SequenceIndex', 'int') ) as SequenceRowID,
        Reward.value('@SequenceIndex', 'int') as SequenceIndex,
        hotel.value('../@RewardID', 'int') as RewardID,
        hotel.value('../@TPID', 'int') as TPID,
        hotel.value('../@RestrictionTypeID', 'smallint') as RestrictionTypeID,
        hotel.value('@HotelID', 'int') as HotelID
from    @Xml.nodes('/Root/CouponHotelRestriction') col(Reward)
CROSS APPLY 
        Reward.nodes('CouponHotel/Hotel') tab(Hotel)


Запускается все на SQL 2008 R2,

Спасибо.
23 ноя 12, 19:21    [13521533]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
 		Никак не улучшить. Хотите быстро, не храните данные в xml.
23 ноя 12, 19:35    [13521556]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
whitebeast
Member

Откуда:
Сообщений: 165
 		Гавриленко Сергей Алексеевич,
тут просто или хмл или 30 000 раз запускать процедуру. Имхо, хмл здесь лучше...
23 ноя 12, 19:40    [13521570]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31785
whitebeast
тут просто или хмл или 30 000 раз запускать процедуру. Имхо, хмл здесь лучше...
Имхо, один раз запустить процедуру без XML ещё лучше...
23 ноя 12, 20:20    [13521704]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
whitebeast
Имхо, хмл здесь лучше...
Ну и наслаждайтесь вашим  хмл-ем...
23 ноя 12, 21:10    [13521838]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
dalex1973
Member

Откуда: Польша
Сообщений: 287
 		1. text()/data()!
2. Привязать к XSD(Schema collections)
2. Если из таблицы считываете, то XML индексы(главный+PATH)
3. Если не помогает, то - CLR (пример
23 ноя 12, 23:37    [13522195]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
Гость333
Member

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

-- Здесь инициализация переменной @xml --

declare @id int;
exec sp_xml_preparedocument @id output, @xml

select row_number() over(partition by SequenceIndex order by SequenceIndex) as SequenceRowID,
        SequenceIndex,
        RewardID,
        TPID,
        RestrictionTypeID,
        HotelID
from openxml(@id, '/Root/CouponHotelRestriction/CouponHotel/Hotel')
with (SequenceRowID int '../../@SequenceIndex', SequenceIndex int '../../@SequenceIndex',
  RewardID int '../@RewardID', TPID int '../@TPID',
  RestrictionTypeID smallint '../@RestrictionTypeID', HotelID int '@HotelID');

exec sp_xml_removedocument @id


Запрос с двумя тысячами HotelID отработал мгновенно, с 300 тысячами — крутился около минуты.
Можно узнать, почему нельзя хранить данные нормально, а не извращаться с xml?
25 ноя 12, 14:17    [13525279]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
whitebeast
Member

Откуда:
Сообщений: 165
 		dalex1973, Гость333, спасибо за дельные советы. Буду пробовать.
Нужно это все для того, чтобы обрабатывать некий файл, в котором будут лежать HotelID. Затем все эти ID одним махом нужно вставить в базу.
Можно было бы сделать через обычный текстовый файл, но тогда этот файл должен был бы лежать на сервере. А так делать нельзя. Или извращаться с пермишенами. Что тоже не есть гуд.
26 ноя 12, 12:50    [13528810]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса работающего с XML  [new]
whitebeast
Member

Откуда:
Сообщений: 165
 
select  
        Reward.value('(@RewardID)[1]', 'int') as RewardID,
        Hotel.value ('(@HotelID)[1]', 'int') as HotelID,
        Reward.value('(@RestrictionTypeID)[1]', 'smallint') as RestrictionTypeID,
        Reward.value('(@TPID)[1]', 'int') as TPID
from    @pHotelXml.nodes('/Batch/CouponHotel') col(Reward)
CROSS APPLY 
        Reward.nodes('Hotel') tab(Hotel)

30000 - 3 секунды. То, что надо.
27 ноя 12, 15:47    [13537391]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить