CftClub.ru
Клуб специалистов ЦФТ-Банк

Поиск документов по дате проводки
На страницу 1, 2  След.
 
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle
Предыдущая тема :: Следующая тема  
Автор Сообщение
ssa774
Профи


Вступление в Клуб: 30.11.2007
СообщениеПт Фев 15, 2008 16:53   Поиск документов по дате проводки Ответить с цитатой
Полезность: 5
Не знаю, стоило ли создавать новую тему, пусть решит модератор...
Сделала поиск по форуму, ничего такого не нашла.
Думаю, всем приходилось отбирать документы по дате проводки. В исходниках ЦФТ часто используется такой код:
Код:

for d in ::[MAIN_DOCUM]
where
d%state = 'PROV'
and  d.[DATE_PROV] >= P_DATE
and  d.[DATE_PROV] < P_DATE+1
loop
 .........
end loop;


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

Код:

P_DATE_TMP date;
begin
P_DATE_TMP := P_DATE+1;
for d in ::[MAIN_DOCUM]
where
 d%state = 'PROV'
and  d.[DATE_PROV] >= P_DATE
and  d.[DATE_PROV] < P_DATE_TMP
loop
  null;
end loop;
end;


Для нашего банка:
Код1 - обработка документов Головного банка за неделю идет около 15 мин.
Код2 - меньше 3-х минут.


Последний раз редактировалось: ssa774 (Сб Фев 16, 2008 08:02), всего редактировалось 1 раз
Alex2019
Профи


Вступление в Клуб: 02.07.2007
СообщениеПт Фев 15, 2008 19:25    Ответить с цитатой
Полезность: Нет оценки
Подтверждаю. Мы этот прием тоже использовали с похожим результатом.
Еще очень ресурсоемко использование TRUNC() внутри циклов, и вообще, все, что можно вычислить заранее, по возможности лучше считать вне цикла.
dnk_dz
Эксперт


Вступление в Клуб: 19.09.2007
СообщениеПн Фев 18, 2008 08:22    Ответить с цитатой
Полезность: 1
Посмотрите план выполнения запроса. Скорее всего, в
первом случае используется индекс по состоянию документа (STATE_ID).
Во втором случае - индекс по дате проводки.
Исходя из поставленной задачи, лучше использовать индекс по дате проводки.
Для явного указания индекса используется прагма
Код:
pragma hint('INDEX(A1 IDX_Z#MAIN_DOCUM_DATE_PROV)');

Где A1 алиас таблицы Z#MAIN_DOCUM в запросе. Сама прагма пишется непосредственно перед запросом.

По поводу trunc(). Вообще, использования функций в условиях выборки в запросе нежелательно, т.к. в этом случае индексы не используются.
ssa774
Профи


Вступление в Клуб: 30.11.2007
СообщениеПн Фев 18, 2008 09:24    Ответить с цитатой
Полезность: Нет оценки
dnk_dz пишет:
Посмотрите план выполнения запроса.


Даже если явно подвязать индекс по дате проводки, второй код будет работать раз в пять быстрее Smile

Кстати, нельзя посмотреть план запроса для такого кода
Код:

for doc in [MAIN_DOCUM] where (doc.[DATE_PROV] >=P_DATE and doc.[DATE_PROV] < P_DATE+1)
      and doc.FILIAL = P_FILIAL
      and doc%state = 'PROV'


ругается на P_DATE+1 - "ожидалась DATE, получили NUMBER"
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеПн Фев 18, 2008 09:44    Ответить с цитатой
Полезность: 3
Индекс по состоянию документов полезно отключать например следующим способом:
Код:
d%state||'' = 'PROV'

Тогда на 100% можно быть уверенным, что неоптимальный индекс не будет задействоан.
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеПн Фев 18, 2008 09:46    Ответить с цитатой
Полезность: Нет оценки
ssa774 пишет:
Кстати, нельзя посмотреть план запроса для такого кода
Код:

for doc in [MAIN_DOCUM] where (doc.[DATE_PROV] >=P_DATE and doc.[DATE_PROV] < P_DATE+1)
      and doc.FILIAL = P_FILIAL
      and doc%state = 'PROV'


ругается на P_DATE+1 - "ожидалась DATE, получили NUMBER"

Можно скопировать sql-запрос для просмотра плана и применить функцию to_date к P_DATE+1. Думаю, что план запроса от этого не изменится.
dnk_dz
Эксперт


Вступление в Клуб: 19.09.2007
СообщениеПн Фев 18, 2008 10:09    Ответить с цитатой
Полезность: 4
ssa774 пишет:

Даже если явно подвязать индекс по дате проводки, второй код будет работать раз в пять быстрее Smile

Вы удивитесь, но это не так Very Happy

Попробуйте выполнить такой код:
Код:
   t0 := utils.get_time;
   for d in ::[MAIN_DOCUM] where d%state = 'PROV' and d.[DATE_PROV] >= P_DATE and d.[DATE_PROV] < P_DATE + 1
   loop
      null;
   end loop;
   t1 := utils.get_time;
   debug_pipe('Время='||to_char(t1 - t0), 0);

   t0 := utils.get_time;
   pragma hint('INDEX(A1 IDX_Z#MAIN_DOCUM_DATE_PROV)');
   for d in ::[MAIN_DOCUM] where d%state = 'PROV' and d.[DATE_PROV] >= P_DATE and d.[DATE_PROV] < P_DATE + 1
   loop
      null;
   end loop;
   t1 := utils.get_time;
   debug_pipe('Время='||to_char(t1 - t0), 0);

   P_DATE2 := P_DATE + 1;
   t0 := utils.get_time;
   for d in ::[MAIN_DOCUM] where d%state = 'PROV' and d.[DATE_PROV] >= P_DATE and d.[DATE_PROV] < P_DATE2
   loop
      null;
   end loop;
   t1 := utils.get_time;
   debug_pipe('Время='||to_char(t1 - t0), 0);
   


Первый запрос (неоптимальный), время выполнения ~ 320 сек.
Второй запрос (явное указание индекса), время выполнения ~ 0.61 сек
Третий запрос (предварительно вычисленная переменная), время выполнения ~ 0.61 сек

Насколько я понимаю, Oracle вычисляет значение переменной на этапе компиляции запроса, а не в момент исполнения запроса, поэтому вариант 2 и вариант 3 не отличаются.
ssa774
Профи


Вступление в Клуб: 30.11.2007
СообщениеПн Фев 18, 2008 13:34    Ответить с цитатой
Полезность: Нет оценки
dnk_dz пишет:
Вы удивитесь, но это не так Very Happy

Ну, да...да. Но это же мне нужно было полезть в табличку и посмотреть, как индекс называется... Smile
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеПн Фев 18, 2008 13:40    Ответить с цитатой
Полезность: Нет оценки
ssa774 пишет:
dnk_dz пишет:
Вы удивитесь, но это не так Very Happy

Ну, да...да. Но это же мне нужно было полезть в табличку и посмотреть, как индекс называется... Smile

Чтобы не лазить в табличку, можно использовать правило, что, если в запросе есть условие на поле таблицы, по которому использование индекса не оптимально, то надо с этим полем сделать действие, не изменяющее его. Например +0, ||'' и др.
А по состоянию документа PROV выборку строить действительно не оптимально, т.к. в этом состоянии находится подавляющая часть документов. Вот если речь идет о некоем промежуточном состоянии, то индекс по этому полю будет работать очень даже замечательно.
dnk_dz
Эксперт


Вступление в Клуб: 19.09.2007
СообщениеПн Фев 18, 2008 14:08    Ответить с цитатой
Полезность: Нет оценки
ssa774 пишет:
dnk_dz пишет:
Вы удивитесь, но это не так Very Happy

Ну, да...да. Но это же мне нужно было полезть в табличку и посмотреть, как индекс называется... Smile

В таблицу лезть не нужно. В Администраторе словаря данных откройте свойства ТБП. Там есть закладка "Индексы", где перечислены все индексы таблицы и для каждого индекса приведен список индексированных полей.

timochev пишет:

Чтобы не лазить в табличку, можно использовать правило, что, если в запросе есть условие на поле таблицы, по которому использование индекса не оптимально, то надо с этим полем сделать действие, не изменяющее его. Например +0, ||'' и др.


А если нужного индекса нет? Все равно, нужно посмотреть список индексов. Думаю, это будет правильней... А то приходиться лазить в дистрбутивные операции и отключать "оптимизирующие условия" выборок, которые написали программисты ЦФТ, не знающие, что такое индексы... Crying or Very sad
ssa774
Профи


Вступление в Клуб: 30.11.2007
СообщениеПн Фев 18, 2008 14:14    Ответить с цитатой
Полезность: Нет оценки
Цитата:
Насколько я понимаю, Oracle вычисляет значение переменной на этапе компиляции запроса, а не в момент исполнения запроса, поэтому вариант 2 и вариант 3 не отличаются.


А все равно 3-й вариант быстрее, если увеличить период выборки. Чем больше период, тем медленнее работает вариант2. Например, за месяц на целую минуту медленнее... Проверила в toad. 3-й быстрее. Вот! Very Happy
timochev
Эксперт


Вступление в Клуб: 02.07.2007
СообщениеПн Фев 18, 2008 14:14    Ответить с цитатой
Полезность: Нет оценки
dnk_dz пишет:
А если нужного индекса нет? Все равно, нужно посмотреть список индексов. Думаю, это будет правильней...

Тонкие места в запросах как правило уже заранее известны. Поэтому в голове лежит определенный набор "опасных" индексов, от которых не только польза, но и вред. Wink
r00st
Эксперт


Вступление в Клуб: 14.09.2007
СообщениеПн Фев 18, 2008 14:33    Ответить с цитатой
Полезность: 2
Цитата:
А то приходиться лазить в дистрбутивные операции и отключать "оптимизирующие условия" выборок, которые написали программисты ЦФТ, не знающие, что такое индексы...

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


Вступление в Клуб: 25.06.2007
СообщениеПн Фев 18, 2008 14:59    Ответить с цитатой
Полезность: Нет оценки
r00st пишет:
Если статистика собирается регулярно, этого делать как раз-таки не нужно. Оптимизатор сам корректно выстроит план запроса, имея информацию количестве уникальных записей индексов.
В этом случае хинт может повредить, принудительно используя указанный индекс взамен более оптимального.


+1

Цитата:
Oracle не рекомендует использовать оптимизацию на правилах, не гарантирует, что она будет поддерживаться в последующих версиях СУБД Oracle и советует использовать стоимостную оптимизацию во всех разрабатываемых приложениях.


http://www.cftclub.ru/viewtopic.php?t=4
_________________
Homo homini
dnk_dz
Эксперт


Вступление в Клуб: 19.09.2007
СообщениеПн Фев 18, 2008 15:10    Ответить с цитатой
Полезность: Нет оценки
ssa774 пишет:
Цитата:
Насколько я понимаю, Oracle вычисляет значение переменной на этапе компиляции запроса, а не в момент исполнения запроса, поэтому вариант 2 и вариант 3 не отличаются.


А все равно 3-й вариант быстрее, если увеличить период выборки. Чем больше период, тем медленнее работает вариант2. Например, за месяц на целую минуту медленнее... Проверила в toad. 3-й быстрее. Вот! Very Happy


Скорее всего, сказался эффект кэширования данных ораклом. Попробуйте несколько раз подряд, один и второй запрос выполнить из одной сессии.

Я сделал несколько раз подряд запуск кода, приведенного выше, сделав выборку документов за месяц.
Оба запроса выполняются за одинаковое время: ~11.8 сек.
Показать сообщения:   
Ответить на тему    Клуб специалистов ЦФТ-Банк (IBSO) -> Разработка в PL/PLUS. Оптимизация запросов Oracle Часовой пояс: GMT + 3
На страницу 1, 2  След.
Страница 1 из 2

 
Перейти:  
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Рейтинг@Mail.ru