iBase.ru Forum

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

Оптимизация процедуры

http://ibaseforum.ru/viewtopic.php?f=4&t=76

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

Оптимизация процедуры

Добавлено: 05 ноя 2004, 14:43
kdv
Здравствуйте! Помогите, плиз, с медленной ХП.
Заранее извините за длинный пост.

FB 1.5.1

Имеем вьюху (5000 записей). В Person ~2000 записей.

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

CREATE VIEW VW$ANSWER(
    ID_PERSON,
    ID_ACTION,
    ID_TOWN)
AS
SELECT A.ID_PERSON
     , A.ID_ACTION
     , P.ID_TOWN
  FROM ANSWER A LEFT JOIN PERSON P ON A.ID_PERSON = P.ID_PERSON
;

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

CREATE PROCEDURE QRY$PARTICIPANT (
    ID_ACTION INTEGER,
    INPUT_DATE DATE)
RETURNS (
    ID_PERSON INTEGER,
    ID_TOWN INTEGER,
    SEX VARCHAR(1),
    AGE INTEGER)
AS
BEGIN
  /* Список лиц участвующих в акции на определенную дату */

  FOR
  SELECT P.ID_PERSON
       , P.ID_TOWN
       , P.SEX
       , (CURRENT_DATE - P.DOB) / 365
    FROM PERSON P
   WHERE EXISTS(SELECT A.ID_PERSON
                  FROM ANSWER A
                 WHERE A.ID_ACTION = :ID_ACTION
                   AND A.DATE_RECEPTION <= :INPUT_DATE
                   AND A.ID_PERSON = P.ID_PERSON)
    INTO :ID_PERSON
       , :ID_TOWN
       , :SEX
       , :AGE

  DO BEGIN
    SUSPEND;
  END
END
А вот собственно и ХП, которая тормозит:

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

BEGIN
  /* Отчет в разрезе возростных категорий */
  FOR
    SELECT A.ID_TOWN
         , COUNT(*)
      FROM VW$ANSWER A
     WHERE A.ID_ACTION = :ID_ACTION
     GROUP BY 1
      INTO :ID_TOWN
         , :COUPON
  DO
  BEGIN

    SELECT T.REGION_NAME
         , T.DISTRICT_NAME
         , T.NAME
      FROM VW$TOWN T
     WHERE T.ID_TOWN = :ID_TOWN
      INTO :REGION
         , :DISTRICT
         , :TOWN;

    SELECT 0, 0, 0, 0, 0, 0, 0
      FROM RDB$DATABASE
      INTO :MEN, :AGE18_23, :AGE24_30, :AGE31_40, :AGE41_50, :AGE51_60, :AGE60_;

    SELECT COUNT(*)
      FROM QRY$PARTICIPANT(:ID_ACTION, :INPUT_DATE)
     WHERE SEX = 'м'
       AND ID_TOWN = :ID_TOWN
      INTO :MEN;

    FOR SELECT
           CASE
             WHEN (AGE BETWEEN 18 AND 23) THEN '1'
             WHEN (AGE BETWEEN 24 AND 30) THEN '2'
             WHEN (AGE BETWEEN 31 AND 40) THEN '3'
             WHEN (AGE BETWEEN 41 AND 50) THEN '4'
             WHEN (AGE BETWEEN 51 AND 60) THEN '5'
             WHEN (AGE > 60) THEN '6'
             ELSE 0
           END
         , COUNT(*)
      FROM QRY$PARTICIPANT(:ID_ACTION, :INPUT_DATE)
     WHERE ID_TOWN = :ID_TOWN
     GROUP BY 1
      INTO :TYPE_
         , :CNT_TYPE
    DO BEGIN
      IF (:TYPE_ = '1') THEN AGE18_23 = :CNT_TYPE;
      IF (:TYPE_ = '2') THEN AGE24_30 = :CNT_TYPE;
      IF (:TYPE_ = '3') THEN AGE31_40 = :CNT_TYPE;
      IF (:TYPE_ = '4') THEN AGE41_50 = :CNT_TYPE;
      IF (:TYPE_ = '5') THEN AGE51_60 = :CNT_TYPE;
      IF (:TYPE_ = '6') THEN AGE60_ = :CNT_TYPE;
    END

    CNT_PERSON = AGE18_23 + AGE24_30 + AGE31_40 + AGE41_50 + AGE51_60 + AGE60_;

    WOMEN = :CNT_PERSON - :MEN;

    SUSPEND;

  END
END

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

------ Performance info ------
Prepare time = 0ms
Execute time = 6s 250ms
Avg fetch time = 208,33 ms
Current memory = 4 215 804
Max memory = 4 535 828
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 4
Fetches from cache = 7 693 353

PLAN SORT (JOIN (A A INDEX (FK_ACTION__ANSWER), A P INDEX (PK_PERSON)))
SORT ()(RDB$DATABASE NATURAL)
JOIN (JOIN (JOIN (T T INDEX (PK_TOWN),T D INDEX (PK_DISTRICT)),
T R INDEX (PK_REGION)),T TT INDEX (PK_TOWN_TYPE))
Спасибо.

Добавлено: 05 ноя 2004, 15:18
sag
Привет,
попробуй в хп QRY$PARTICIPANT избавиться от экзиста:

select distinct P.ID_PERSON
, P.ID_TOWN
, P.SEX
, (CURRENT_DATE - P.DOB) / 365
from PERSON P, ANSWER A
where A.ID_ACTION = :ID_ACTION
AND A.DATE_RECEPTION <= :INPUT_DATE
AND A.ID_PERSON = P.ID_PERSON

Я бы вообще отказался от использования этой хп, а в процедуре
"Отчет в разрезе возростных категорий" все делал обычными запросами.

Добавлено: 05 ноя 2004, 15:46
kdv
в QRY$PARTICIPANT избавился от экзиста, стало хуже:

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

------ Performance info ------
Prepare time = 0ms
Execute time = 15s 531ms
Avg fetch time = 517,70 ms
Current memory = 4 392 760
Max memory = 4 544 056
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 46
Fetches from cache = 16 812 554

Добавлено: 05 ноя 2004, 16:12
sag
как избавился то?
Еще раз посмотрел на твою результирующую процедуру.
Каждый раз выборка из хп QRY$PARTICIPANT идет с последующим
where ID_TOWN = :ID_TOWN, поэтому имеет смысл добавить это как параметр в процедуру.

теперь по запросам. какие планы и время дают запросы
select distinct P.ID_PERSON
, P.ID_TOWN
, P.SEX
, (CURRENT_DATE - P.DOB) / 365
from PERSON P, ANSWER A
where A.ID_ACTION = :ID_ACTION
AND A.DATE_RECEPTION <= :INPUT_DATE
AND A.ID_PERSON = P.ID_PERSON
AND P.ID_TOWN = :INPUT_ID_TOWN

и

SELECT P.ID_PERSON
, P.ID_TOWN
, P.SEX
, (CURRENT_DATE - P.DOB) / 365
FROM PERSON P
WHERE EXISTS(SELECT A.ID_PERSON
FROM ANSWER A
WHERE A.ID_ACTION = :ID_ACTION
AND A.DATE_RECEPTION <= :INPUT_DATE
AND A.ID_PERSON = P.ID_PERSON)
and AND P.ID_TOWN = :INPUT_ID_TOWN
?

Добавлено: 10 ноя 2004, 09:49
Лысый
sag писал(а):как избавился то?
До сих пор не избавился :(
sag писал(а): Каждый раз выборка из хп QRY$PARTICIPANT идет с последующим
where ID_TOWN = :ID_TOWN, поэтому имеет смысл добавить это как параметр в процедуру.
Эта процедура используется и в других местах, поэтому я и не сделал парамметр...
sag писал(а): теперь по запросам. какие планы и время дают запросы
select distinct P.ID_PERSON
, P.ID_TOWN
, P.SEX
, (CURRENT_DATE - P.DOB) / 365
from PERSON P, ANSWER A
where A.ID_ACTION = :ID_ACTION
AND A.DATE_RECEPTION <= :INPUT_DATE
AND A.ID_PERSON = P.ID_PERSON
AND P.ID_TOWN = :INPUT_ID_TOWN

и

SELECT P.ID_PERSON
, P.ID_TOWN
, P.SEX
, (CURRENT_DATE - P.DOB) / 365
FROM PERSON P
WHERE EXISTS(SELECT A.ID_PERSON
FROM ANSWER A
WHERE A.ID_ACTION = :ID_ACTION
AND A.DATE_RECEPTION <= :INPUT_DATE
AND A.ID_PERSON = P.ID_PERSON)
and AND P.ID_TOWN = :INPUT_ID_TOWN
?
Первый:

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

Plan
PLAN SORT (JOIN (P INDEX (FK_TOWN__PERSON),A INDEX (FK_PERSON__ANSWER)))

Adapted Plan
PLAN SORT (JOIN (P INDEX (FK_TOWN__PERSON),A INDEX (FK_PERSON__ANSWER)))

------ Performance info ------
Prepare time = 0ms
Execute time = 94ms
Avg fetch time = 3,76 ms
Current memory = 2 643 460
Max memory = 2 794 428
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 20 201
Второй (явно результативнее):

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

Plan
PLAN (P INDEX (FK_TOWN__PERSON))
PLAN (A INDEX (FK_PERSON__ANSWER))

Adapted Plan
PLAN (P INDEX (FK_TOWN__PERSON)) PLAN (A INDEX (FK_PERSON__ANSWER))

------ Performance info ------
Prepare time = 0ms
Execute time = 15ms
Avg fetch time = 0,83 ms
Current memory = 2 681 060
Max memory = 2 831 904
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 6
Fetches from cache = 14 618

Добавлено: 10 ноя 2004, 11:12
sag
> > Каждый раз выборка из хп QRY$PARTICIPANT идет с
> > последующим where ID_TOWN = :ID_TOWN, поэтому
> > имеет смысл добавить это как параметр в процедуру.
> Эта процедура используется и в других местах,
> поэтому я и не сделал парамметр...

Так и не используй ее вообще (делай все обычными селектами) или варгань другую хп.
А так у тебя смотри что получается: цикл по городам, в теле цикла
идет двойной селект по этой процедуре, которая каждый раз
идет по всей выборке PERSON&ANSWER и каждый раз возвращает всю эту выборку, и только на результат этой выборки накладывается условие.

> Первый:
> PLAN SORT (JOIN (P INDEX (FK_TOWN__PERSON),A INDEX
> (FK_PERSON__ANSWER)))
> Execute time = 94ms
> Второй (явно результативнее):
> PLAN (P INDEX (FK_TOWN__PERSON))
> PLAN (A INDEX (FK_PERSON__ANSWER))
> Execute time = 15ms

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

Добавлено: 10 ноя 2004, 12:28
Лысый
sag писал(а):> > Каждый раз выборка из хп QRY$PARTICIPANT идет с
> > последующим where ID_TOWN = :ID_TOWN, поэтому
> > имеет смысл добавить это как параметр в процедуру.
> Эта процедура используется и в других местах,
> поэтому я и не сделал парамметр...

Так и не используй ее вообще (делай все обычными селектами) или варгань другую хп.
Сварганил другую ХП. Итоговый результат:

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

------ Performance info ------
Prepare time = 0ms
Execute time = 390ms
Avg fetch time = 13,00 ms
Current memory = 3 365 176
Max memory = 3 633 844
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 3
Fetches from cache = 109 132
Большое спасибо, Sag!
Часовой пояс: UTC+03:00
Страница 1 из 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/