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

Откуда: СПб
Сообщений: 244
SQL 2008

Стоит задача удовлетворить потребность из ресурсов в жестко заданном порядке. Т.е., если потребность превышает первый ресурс, то полностью задействуем его, уменьшаем потребность на задействованное значение и переходим ко второму ресурсу.

declare @Volume int
select @Volume=80 -- потребность

declare @Tab table (Number int, Quantity int) -- ресурсы

insert into @Tab values (1,8)
insert into @Tab values (2,50)
insert into @Tab values (3,40)
insert into @Tab values (4,20)

В результате ожидаем получить

Number Quantity NewQuantity
1 8 8
2 50 50
3 40 22
4 20 0/NULL -- не принципиально

Ситуации, когда потребность полностью удовлетворяется первым ресурсом (@Volume=5) или превышает совокупный ресурс (@Volume=200), вполне допустимы.

Эти вычисления будут многократно повторяться, т.е., по сути, являются ядром расчета, поэтому очень хочется максимально умять их по времени. Количество ресурсов - до 200.
22 окт 09, 08:39    [7821390]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
я
Guest
Похоже на задачку с расчетом нарастающего итога, вроде в факе даже статья есть.
22 окт 09, 10:14    [7821843]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
я
Guest
ссылка - https://www.sql.ru/faq/faq_topic.aspx?fid=125
22 окт 09, 10:15    [7821853]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
Поле "автор" д
Guest
select a.*,CASE WHEN @Volume >sum(b.Quantity) THEN a.Quantity 
		WHEN @Volume >sum(b.Quantity)- a.Quantity  THEN @Volume-sum(b.Quantity) + a.Quantity END 
From @Tab a 
left join @Tab b
on a.Number	>= b.Number
group by a.Number,a.Quantity
order by a.Number
22 окт 09, 10:23    [7821897]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
remi_
Member

Откуда: СПб
Сообщений: 244
Поле "автор" д
select a.*,CASE WHEN @Volume >sum(b.Quantity) THEN a.Quantity 
		WHEN @Volume >sum(b.Quantity)- a.Quantity  THEN @Volume-sum(b.Quantity) + a.Quantity END 
From @Tab a 
left join @Tab b
on a.Number	>= b.Number
group by a.Number,a.Quantity
order by a.Number


Ага, уже чуть получше моего кода

with asdf as (
select Number, Quantity, NewQuantity=case when Quantity > @Volume then @Volume else Quantity end,
Volume=case when Quantity > @Volume then 0 else @Volume-Quantity end
from @Tab where Number=1
UNION ALL
select t.Number, t.Quantity, NewQuantity=case when t.Quantity > a.Volume then a.Volume else t.Quantity end,
Volume=case when t.Quantity > a.Volume then 0 else a.Volume-t.Quantity end
from @Tab t
join asdf a on t.Number=a.Number+1
)
select * from asdf

План у Гостя проще, но его условие a.Number >= b.Number приводит к одному Scan и одному Seek по кластерному индексу (подразумевается declare @Tab table (Number int PRIMARY KEY, Quantity int)). У меня 2 раза Seek. Хотя, возможно, на таких объемах это и не очень принципиально.
Выигрыш у Гостя по времени по оценке (Estimated subtree cost) примерно 5%.

Подождем еще.
22 окт 09, 11:56    [7822756]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
я
Guest
Я б такой вариант предложил (на больших объемах он точно лучше, до 200 записей не знаю):

declare @Volume int
select @Volume=80 -- потребность
declare @rest int
declare @newquantity int
set @rest=0
set @newquantity=0

create table #Tab (Number int, Quantity int, NewQuantity int) -- ресурсы

insert into #Tab values (1,8,0)
insert into #Tab values (2,50,0)
insert into #Tab values (3,40,0)
insert into #Tab values (4,20,0)

create unique clustered index ix_pk on #tab(number)

update #tab
set 
@rest=case when @volume>quantity then quantity else @volume end,
@volume=case when @volume>quantity then @volume-quantity else 0 end,
newquantity=@rest

drop table #tab

22 окт 09, 13:31    [7823774]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
remi_
Member

Откуда: СПб
Сообщений: 244
Хм. Немного растерялся. С одной стороны подкупает своей лаконичностью и примитивным планом, с другой - оценка по Estimated subtree cost в два раза больше.
Насчет больших объемов - не согласен. Представьте 100 тыс. записей, первые две из которых покрывают потребность. Ваш код сделает update всей таблицы, мой возьмет только 2 первых записи.

В любом случае, сердечное мерси.
22 окт 09, 14:01    [7824061]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
remi_
Member

Откуда: СПб
Сообщений: 244
я
update #tab
set 
@rest=case when @volume>quantity then quantity else @volume end,
@volume=case when @volume>quantity then @volume-quantity else 0 end,
newquantity=@rest


Погонял на различных примерах, проникся.
Создается впечатление, что быстрее уже не будет. Ну просто некуда :-)
А может местные гуру меня поправят?

В любом случае, еще раз сердечное мерси.
22 окт 09, 14:29    [7824326]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
я
Guest
В защиту update могу сказать следующее - у меня с использованием такого подхода расчитывается эффективная процентная ставка. Примерно по миллиону договоров (в среднем 20 плановых гашений на каждый) расчет идет около часа.
23 окт 09, 11:37    [7828442]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Если не ошибаюсь, в этом UPDATE важен порядок?
remi_
потребность из ресурсов в жестко заданном порядке
И как же он, интересно, обеспечивается?
23 окт 09, 11:50    [7828567]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
Если не ошибаюсь, в этом UPDATE важен порядок?
remi_
потребность из ресурсов в жестко заданном порядке
И как же он, интересно, обеспечивается?
remi_, грубо говоря, Вам надо рекордно быстро или гарантированно правильно?
23 окт 09, 11:52    [7828585]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
aleks2
Guest
Гарантировано
declare @Volume int
select @Volume=80 -- потребность

declare @Tab table (Number int, Quantity int) -- ресурсы

insert into @Tab values (1,8)
insert into @Tab values (2,50)
insert into @Tab values (3,40)
insert into @Tab values (4,20)

;with res (n, q, nq, v)
as
(
select 0 N, 0 q, 0 nq, @Volume v
union all
select T.Number N, T.Quantity q
   , CASE WHEN T.Quantity>r.v THEN Quantity-r.v ELSE 0 END nq
   , CASE WHEN T.Quantity<r.v THEN r.v-Quantity ELSE 0 END v 
FROM res r inner join @Tab T ON T.Number=r.n+1
)
select * FROM  res
23 окт 09, 13:09    [7829345]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
aleks2
Guest
Ну и по-старинке, но гарантированно

select TT.Number, TT.Quantity
, CASE WHEN TT.Number<X.Number THEN 0 WHEN TT.Number=X.Number THEN X.Quantity ELSE TT.Quantity END nq
FROM @Tab TT CROSS JOIN 
(select TOP 1 T.Number, sum(T1.Quantity)-@Volume Quantity
from @Tab T INNER JOIN @Tab T1 ON T.Number>=T1.Number
group by T.Number
HAVING sum(T1.Quantity)>=@Volume
ORDER BY T.Number ASC) X
23 окт 09, 13:26    [7829475]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
я
Guest
Порядок гарантируется вот так:
create unique clustered index ix_pk on #tab(number)

Правда, справедливости ради, отмечу, что если создать индекс до вставки, то порядок в таблице действительно не гарантируется.
23 окт 09, 13:37    [7829540]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
aleks2
Guest
я
Порядок гарантируется вот так:
create unique clustered index ix_pk on #tab(number)

Правда, справедливости ради, отмечу, что если создать индекс до вставки, то порядок в таблице действительно не гарантируется.


Врать нехорошо. В документации НИГДЕ не сказано, что UPDATE имеет какой-либо порядок обработки => после очередного усовершенствования оптимизатора можно поиметь бальшой сюрпрайзе.
23 окт 09, 13:46    [7829600]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
я
Порядок гарантируется вот так:
create unique clustered index ix_pk on #tab(number)

Правда, справедливости ради, отмечу, что если создать индекс до вставки, то порядок в таблице действительно не гарантируется.
Сейчас это будет работать почти всегда.
Но не всегда!
Нет никакой гарантии.
23 окт 09, 13:50    [7829629]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
я
Guest
Да, в документации действительно ничего не написано про порядок UPDATE, но обрабатывать табличку черезстрочно или в рандомном порядке, имхо, менее оптимально, хотя MS могут и не такое отчудить. Лично я готов пойти на осознанный риск, т.к. для моих задач гарантированные методы дают слишком удручающие результаты.
23 окт 09, 14:18    [7829855]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
iljy
Member

Откуда:
Сообщений: 8711
я
Да, в документации действительно ничего не написано про порядок UPDATE, но обрабатывать табличку черезстрочно или в рандомном порядке, имхо, менее оптимально, хотя MS могут и не такое отчудить. Лично я готов пойти на осознанный риск, т.к. для моих задач гарантированные методы дают слишком удручающие результаты.



Index Scan вполне может идти как ORDERED:FALSE, т.е. просто в порядке расположения страниц индекса в куче. Где-то мне даже статья с примерами на эту тему тут попадалась. И это делается не из вредности, а токмо в порядке оптимизации работы с дисковой подсистемой;)
23 окт 09, 14:39    [7830058]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизируем запрос  [new]
Павел Малежик
Member

Откуда: Киев
Сообщений: 6
может гарантировать порядок хинтом?
update #t1 set ... from #t1 with (index(...))
25 окт 09, 22:26    [7836166]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить