iBase.ru Forum

Форум по InterBase, Firebird и Yaffil
http://www.ibaseforum.ru/

Оптимизировать запрос

http://www.ibaseforum.ru/viewtopic.php?f=24&t=4372

Страница 1 из 2

Оптимизировать запрос

Добавлено: 10 апр 2008, 15:49
andycat
Здравствуйте!
есть запрос (суммирование операций по клиентам с получением остатка в рублях по курсам на день операций)

Код: Выделить всё

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

как можно оптимизировать данный запрос?

Re: Оптимизировать запрос

Добавлено: 10 апр 2008, 16:11
WildSery
andycat писал(а):если я соотвественно уберу часть

Код: Выделить всё

 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)
то выполняется примерно в два раза быстрее.
...
как можно оптимизировать данный запрос?
Ну так и убери.
Что за бред считается в этом подзапросе?
Отличие от count(tr.amount) только там, где "select first 1 rates.buy from ..." вернёт NULL
Почему "select first" без сортировки? Любой что ли берём?
Зачем "join currency", если нигде поля из этой таблицы не используются?

Добавлено: 10 апр 2008, 16:14
andycat
считается сумма операций по клиенту за период, а в "бред" считается их же количество (операций) за период

> Почему "select first" без сортировки? Любой что ли берём?
берется курс валюты к рубля по максимальной дате но не больше даты отчета, она там всего одна

Добавлено: 10 апр 2008, 16:21
andycat
> Зачем "join currency", если нигде поля из этой таблицы не используются?

забыл убрать (в реальном приложении его нет), это я до этого вытаскивал наименование валюты операции

Добавлено: 10 апр 2008, 16:28
WildSery
andycat писал(а):считается сумма операций по клиенту за период, а в "бред" считается их же количество (операций) за период
Значит, count(tr.amount) будет иметь идентичное значение.
andycat писал(а):> Почему "select first" без сортировки? Любой что ли берём?
берется курс валюты к рубля по максимальной дате но не больше даты отчета, она там всего одна
Если ты пишешь FIRST 1, значит, ты предполагаешь, что иногда может быть больше 1 значения, нет?

Добавлено: 10 апр 2008, 16:34
andycat
>Значит, count(tr.amount) будет иметь идентичное значение
sum (код) выдает сумму операций в период
count (тот же код) выдает их количество
сервер суда по всему (код) рассчитывает два раза, в этом то и вопрос как сделать быстрее

>Если ты пишешь FIRST 1, значит, ты предполагаешь, что иногда может быть больше 1 значения, нет?

согласен - не прав - убрал

Добавлено: 10 апр 2008, 16:38
WildSery
andycat писал(а):>Значит, count(tr.amount) будет иметь идентичное значение
sum (код) выдает сумму операций в период
count (тот же код) выдает их количество
сервер суда по всему (код) рассчитывает два раза, в этом то и вопрос как сделать быстрее
Как я уже сказал, отличие между count(tr.amount) и count(то_что_у_тебя_написано) будет только в том случае, если подзапрос курса может вернуть NULL. Он может вернуть нул?

Добавлено: 10 апр 2008, 16:41
andycat
WildSery писал(а):
andycat писал(а):>Значит, count(tr.amount) будет иметь идентичное значение
sum (код) выдает сумму операций в период
count (тот же код) выдает их количество
сервер суда по всему (код) рассчитывает два раза, в этом то и вопрос как сделать быстрее
Как я уже сказал, отличие между count(tr.amount) и count(то_что_у_тебя_написано) будет только в том случае, если подзапрос курса может вернуть NULL. Он может вернуть нул?
нет

Добавлено: 10 апр 2008, 16:53
andycat
я наверно че го то не понимаю....

сервер вытаскивает n-ное количество записей:

Код: Выделить всё

case when tr.currency_id >1 then
 (tr.amount * (select 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
мне надо по ним подсчитать в отдельных столбцах сумму и количество этих записей.

как написать запрос что-бы не дублировать выше приведенную часть запроса?

Добавлено: 10 апр 2008, 16:59
WildSery
Да, похоже, кто-то из нас не понимает.
Для чего тебе нужно доставать курс валюты и умножать на него, чтобы посчитать кол-во записей?

Добавлено: 10 апр 2008, 17:02
WildSery
Я сегодня добрый - вот тебе моё решение.
С типами данных определишься сам. По скорости должна переплюнуть твой запрос как минимум на порядок.

Код: Выделить всё

create procedure Report1 (aDate date)
returns (sender_lastname varchar(), sender_firstname varchar(), sender_middlename varchar(),
         aSum numeric(9,2), aCount int)
as
  declare variable currency_id int;
  declare variable cur_id int;
  declare variable amount numeric(9,2);
  declare variable valuta numeric(9,2);
  declare variable cnt int;
begin
  cur_id = -1; valuta = 1;

  for select tr.sender_lastname, tr.sender_firstname, tr.sender_middlename, tr.currency_id, sum(tr.amount), count(tr.amount)
    from transfer tr
    where tr.transfer_date = :aDate
    group by 1, 2, 3, 4
    order by tr.currency_id
    into sender_lastname, sender_firstname, sender_middlename, currency_id, amount, cnt
  do begin
    if (cur_id != currency_id) then begin
      if (cur_id != -1) then suspend;

      aSum = 0; aCount = 0; cur_id = currency_id;

      if (cur_id > 1) then
        select first 1 buy
          from rate
          where ratetype = 0 and ratedate <= :aDate and currency2 = 1 and currency1 = :cur_id
          order by ratedate desc
          into valuta;
    end
    aSum = aSum + amount * valuta;
    aCount = aCount + cnt;
  end

  if (cur_id != -1) then suspend;
end
ЗЫ: Если обоснуешь, зачем тебе именно запросом, может быть, я тебе помогу.

Добавлено: 10 апр 2008, 17:06
andycat
WildSery писал(а):Да, похоже, кто-то из нас не понимает.
Для чего тебе нужно доставать курс валюты и умножать на него, чтобы посчитать кол-во записей?
курс валюты ЦБ на день операции умноженное на сумму самой операции - получаю ОДНУ строку, а вот уже эти строки я суммирую и получаю их количество, т.е.

SUM вот этого:

Код: Выделить всё

case when tr.currency_id >1 then
 (tr.amount * (select 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
получает сумму операций, а
COUNT этого же:

Код: Выделить всё

case when tr.currency_id >1 then
 (tr.amount * (select 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
дает их количество

Добавлено: 10 апр 2008, 17:11
andycat
> ЗЫ: Если обоснуешь, зачем тебе именно запросом, может быть, я тебе помогу.

Именно запросом мне нужно только из спортивного интереса :)
отчет разовый - за месяц выполняется меньше минуты, соотвественно целесообразности его сильно убыстрять нету.
Интересовал вопрос как это можно сделать без хранимой процедуры :)

спасибо за внимание :)

Добавлено: 10 апр 2008, 17:14
WildSery
andycat писал(а):курс валюты ЦБ на день операции умноженное на сумму самой операции - получаю ОДНУ строку, а вот уже эти строки я суммирую и получаю их количество, т.е.
Ну. И объясни мне наконец, в чём разница, буду я считать count(1, 1, 1, 2, 10) = 5 или count(1*24, 1*36, 1*24, 2*24, 10*36) = 5 ?
В свой запрос (полный) просто добавь ещё одно поле count(tr.amount) или вообще count(*), т.к. подразумеваю, что там нулов тоже нет, и в результате найди строки, где количество будет отличаться от того, что сосчитал ты.

Добавлено: 10 апр 2008, 17:20
WildSery
andycat писал(а):Именно запросом мне нужно только из спортивного интереса :)
Кстати, нормальный аргумент. Понравилось.
Потому лови. Конечно, медленнее, чем процедура, но всёж-таки.

Код: Выделить всё

select tr.sender_lastname, tr.sender_firstname, tr.sender_middlename,
       sum(tr.amount * case
                         when tr.currency_id > 1
                         then (select first 1 buy
                                 from rate
                                 where ratetype = 0 and ratedate <= :aDate and currency2 = 1 and currency1 = tr.currency_id
                                 order by ratedate desc)
                         else 1
                       end) aSum,
       count(tr.amount) aCount
  from transfer tr
  where tr.transfer_date = 39546
  group by 1, 2, 3

Добавлено: 10 апр 2008, 17:23
andycat
WildSery писал(а):
andycat писал(а):курс валюты ЦБ на день операции умноженное на сумму самой операции - получаю ОДНУ строку, а вот уже эти строки я суммирую и получаю их количество, т.е.
Ну. И объясни мне наконец, в чём разница, буду я считать count(1, 1, 1, 2, 10) = 5 или count(1*24, 1*36, 1*24, 2*24, 10*36) = 5 ?
В свой запрос (полный) просто добавь ещё одно поле count(tr.amount) или вообще count(*), т.к. подразумеваю, что там нулов тоже нет, и в результате найди строки, где количество будет отличаться от того, что сосчитал ты.
попробую объяснить:
у клиента может быть за период отчета операции на разные суммы по разным валютам и с курсами валют соотвественно на разный день, а я должен получить рублевый эквивалент (сумму по клиенту за весь период и количество операций по клиенту соотвественно) точный по курсам ЦБ на день совершения операции.
т.е. предложенный вами вариант

Код: Выделить всё

  for select tr.sender_lastname, tr.sender_firstname, tr.sender_middlename, tr.currency_id, sum(tr.amount), count(tr.amount) 
    from transfer tr 
    where tr.transfer_date = :aDate 
    group by 1, 2, 3, 4 
    order by tr.currency_id 
.............................
не пойдет по причине группировки по валютам.

В принципе можно будет результат хранимки сгруппировать еще раз по клиентам и тогда получиться то что надо.

Добавлено: 10 апр 2008, 17:27
andycat
WildSery писал(а):
andycat писал(а):Именно запросом мне нужно только из спортивного интереса :)
Кстати, нормальный аргумент. Понравилось.
Потому лови. Конечно, медленнее, чем процедура, но всёж-таки.

Код: Выделить всё

select tr.sender_lastname, tr.sender_firstname, tr.sender_middlename,
       sum(tr.amount * case
                         when tr.currency_id > 1
                         then (select first 1 buy
                                 from rate
                                 where ratetype = 0 and ratedate <= :aDate and currency2 = 1 and currency1 = tr.currency_id
                                 order by ratedate desc)
                         else 1
                       end) aSum,
       count(tr.amount) aCount
  from transfer tr
  where tr.transfer_date = 39546
  group by 1, 2, 3
работает и быстрее - спасибо

Добавлено: 10 апр 2008, 17:30
WildSery
andycat писал(а):не пойдет по причине группировки по валютам.
Писал из головы, потому про клиентов забыл.

Добавлено: 10 апр 2008, 17:32
andycat
я понял в чем мой ошибка была....
итоговый вариант - выдающий корректные данные:

Код: Выделить всё

select sum(case when tr.currency_id >1 then
 (tr.amount * (select 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(tr.amount)
from transfer tr
where transfer_date = 39546
group by tr.sender_lastname, tr.sender_firstname, tr.sender_middlename
работает почти в два раза быстрее изначального
спасибо

Добавлено: 10 апр 2008, 17:35
WildSery
Вот так надо:

Код: Выделить всё

create procedure Report1 (aDate date)
returns (sender_lastname varchar(), sender_firstname varchar(), sender_middlename varchar(),
         aSum numeric(9,2), aCount int)
as
  declare variable currency_id int;
  declare variable cur_id int;
  declare variable amount numeric(9,2);
  declare variable valuta numeric(9,2);
begin
  cur_id = -1; valuta = 1;

  for select tr.sender_lastname, tr.sender_firstname, tr.sender_middlename, tr.currency_id, sum(tr.amount), count(tr.amount)
    from transfer tr
    where tr.transfer_date = :aDate
    group by 1, 2, 3, 4
    order by tr.currency_id
    into sender_lastname, sender_firstname, sender_middlename, currency_id, amount, aCount
  do begin
    if (cur_id != currency_id) then begin
      cur_id = currency_id;

      if (cur_id > 1) then
        select first 1 buy
          from rate
          where ratetype = 0 and ratedate <= :aDate and currency2 = 1 and currency1 = :cur_id
          order by ratedate desc
          into valuta;
    end

    aSum = amount * valuta;
    suspend;
  end
end;
и вызывать так:

Код: Выделить всё

select sender_lastname, sender_firstname, sender_middlename, sum(aSum), sum(aCount)
  from Report1 (:aDate)
  group by 1, 2, 3
Часовой пояс: UTC+03:00
Страница 1 из 2
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/