есть запрос (суммирование операций по клиентам с получением остатка в рублях по курсам на день операций)
Код: Выделить всё
select sum(case when tr.currency_id >1 then
(tr.amount * (select first 1 rates.buy from rate rates where rates.ratetype=0 and
(rates.currency1=tr.currency_id)and(rates.currency2=1)and
(rates.ratedate=(select max(rates2.ratedate)from rate rates2 where
rates2.ratedate<=39546 and (rates2.ratetype=0) and (rates2.currency1=tr.currency_id) and
(rates2.currency2=1))))) else tr.amount end),
tr.sender_lastname, tr.sender_firstname, tr.sender_middlename,
count(case when tr.currency_id >1 then
(tr.amount * (select first 1 rates.buy from rate rates where rates.ratetype=0 and
(rates.currency1=tr.currency_id)and(rates.currency2=1)and
(rates.ratedate=(select max(rates2.ratedate)from rate rates2 where
rates2.ratedate<=39546 and (rates2.ratetype=0) and (rates2.currency1=tr.currency_id) and
(rates2.currency2=1))))) else tr.amount end)
from transfer tr
inner join currency curr
on (tr.currency_id=curr.currency_id)
where transfer_date = 39546
group by tr.sender_lastname, tr.sender_firstname, tr.sender_middlename
- Plan
PLAN (RATES2 INDEX (RATE_IDX1))
PLAN (RATES INDEX (RATE_IDX2))
PLAN (RATES2 INDEX (RATE_IDX1))
PLAN (RATES INDEX (RATE_IDX2))
PLAN SORT (JOIN (TR INDEX (IDX_TRANSFER_DATE),CURR INDEX (RDB$PRIMARY3)))
Adapted Plan
PLAN (RATES2 INDEX (RATE_IDX1)) PLAN (RATES INDEX (RATE_IDX2)) PLAN (RATES2 INDEX (RATE_IDX1)) PLAN (RATES INDEX (RATE_IDX2)) PLAN SORT (JOIN (TR INDEX (IDX_TRANSFER_DATE),CURR INDEX (INTEG_102)))
------ Performance info ------
Prepare time = 16ms
Execute time = 609ms
Avg fetch time = 38,06 ms
Current memory = 3'562'028
Max memory = 3'846'972
Memory buffers = 4'096
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 550'154
Код: Выделить всё
,
count(case when tr.currency_id >1 then
(tr.amount * (select first 1 rates.buy from rate rates where rates.ratetype=0 and
(rates.currency1=tr.currency_id)and(rates.currency2=1)and
(rates.ratedate=(select max(rates2.ratedate)from rate rates2 where
rates2.ratedate<=39546 and (rates2.ratetype=0) and (rates2.currency1=tr.currency_id) and
(rates2.currency2=1))))) else tr.amount end)
FB 1.5.4
как можно оптимизировать данный запрос?