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

[Лысый]

Привет всем!
FB 1.5.1
Помогите оптимизировать процедуру...

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

CREATE PROCEDURE REP$AGE_DIAGRAM (
    ID_ACTION INTEGER)
RETURNS (
    AGE SMALLINT,
    MEN INTEGER,
    WOMEN INTEGER,
    MEN_EMB INTEGER,
    WOMEN_EMB INTEGER)
AS
BEGIN
  /* Процедура для диаграммы по возрастам в разрезе мужчин и женщин */

  FOR
    SELECT DISTINCT (CURRENT_DATE - P.DOB) / 365
      FROM PERSON P
     WHERE EXISTS(SELECT A.ID_PERSON
                      FROM ANSWER A
                     WHERE A.ID_ACTION = :ID_ACTION
                       AND P.ID_PERSON = A.ID_PERSON)
     ORDER BY 1
      INTO :AGE
  DO BEGIN

    SELECT COUNT(*)
      FROM PERSON P
     WHERE P.SEX = 'м'
       AND EXISTS(SELECT A.ID_PERSON
                    FROM ANSWER A
                   WHERE A.ID_ACTION = :ID_ACTION
                     AND P.ID_PERSON = A.ID_PERSON)
       AND (CURRENT_DATE - P.DOB) / 365 = :AGE
      INTO :MEN;

    SELECT SUM(A.EMBEDDED_CNT)
      FROM PERSON P LEFT JOIN ANSWER A ON P.ID_PERSON = A.ID_PERSON
     WHERE P.SEX = 'м'
       AND A.ID_ACTION = :ID_ACTION
       AND (CURRENT_DATE - P.DOB) / 365 = :AGE
      INTO :MEN_EMB;

    SELECT COUNT(*)
      FROM PERSON P
     WHERE P.SEX = 'ж'
       AND EXISTS(SELECT A.ID_PERSON
                    FROM ANSWER A
                   WHERE A.ID_ACTION = :ID_ACTION
                     AND P.ID_PERSON = A.ID_PERSON)
       AND (CURRENT_DATE - P.DOB) / 365 = :AGE
      INTO :WOMEN;

    SELECT SUM(A.EMBEDDED_CNT)
      FROM PERSON P LEFT JOIN ANSWER A ON P.ID_PERSON = A.ID_PERSON
     WHERE P.SEX = 'ж'
       AND A.ID_ACTION = :ID_ACTION
       AND (CURRENT_DATE - P.DOB) / 365 = :AGE
      INTO :WOMEN_EMB;

    SUSPEND;
  END
END

План ужасный:

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

PLAN SORT (SORT ((P NATURAL)))JOIN (P NATURAL,A INDEX (FK_PERSON__ANSWER))(P NATURAL)(A INDEX (FK_PERSON__ANSWER))JOIN (P NATURAL,A INDEX (FK_PERSON__ANSWER))(P NATURAL)(A INDEX (FK_PERSON__ANSWER))(A INDEX (FK_PERSON__ANSWER))

------ Performance info ------
Prepare time = 0ms
Execute time = 24s 812ms
Avg fetch time = 775,38 ms
Current memory = 4 744 112
Max memory = 5 487 496
Memory buffers = 2 048
Reads from disk to cache = 1
Writes from cache to disk = 34
Fetches from cache = 11 853 020

В Person ~20 тыс. записей, в Answer ~50 тыс. записей. Понимаю что тормоза от моей криворукости, но что то я лучше придумать не могу...

[Лысый]

Не индексных чтений по Person - 5 597 610. Вот такая фигня...

[dimitr]

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

DO BEGIN

    SELECT
        COUNT(CASE WHEN P.SEX = 'м' THEN 1 ELSE 0 END),
        COUNT(CASE WHEN P.SEX = 'ж' THEN 1 ELSE 0 END)
    FROM PERSON P
    WHERE EXISTS(SELECT A.ID_PERSON
                    FROM ANSWER A
                   WHERE A.ID_ACTION = :ID_ACTION
                     AND P.ID_PERSON = A.ID_PERSON)
        AND (CURRENT_DATE - P.DOB) / 365 = :AGE
    INTO :MEN, :WOMEN;

    SELECT
        SUM(CASE WHEN P.SEX = 'м' THEN A.EMBEDDED_CNT ELSE 0 END),
        SUM(CASE WHEN P.SEX = 'ж' THEN A.EMBEDDED_CNT ELSE 0 END)
    FROM PERSON P
        LEFT JOIN ANSWER A ON P.ID_PERSON = A.ID_PERSON
    WHERE A.ID_ACTION = :ID_ACTION
        AND (CURRENT_DATE - P.DOB) / 365 = :AGE
    INTO :MEN_EMB, WOMEN_EMB;

    SUSPEND;
END

Возможно, стоит эти два запроса вообще объединить в один.

[sag]

1. Попробуй заменить
SELECT DISTINCT (CURRENT_DATE - P.DOB) / 365
FROM PERSON P
WHERE EXISTS(SELECT A.ID_PERSON
FROM ANSWER A
WHERE A.ID_ACTION = :ID_ACTION
AND P.ID_PERSON = A.ID_PERSON)
ORDER BY 1

на

SELECT DISTINCT (CURRENT_DATE - P.DOB) / 365
FROM PERSON P, ANSWER A
WHERE A.ID_ACTION = :ID_ACTION
AND P.ID_PERSON = A.ID_PERSON
ORDER BY 1

2. Попарные запросы
SELECT COUNT(*)
FROM PERSON P
WHERE P.SEX = 'м'
AND EXISTS(SELECT A.ID_PERSON
FROM ANSWER A
WHERE A.ID_ACTION = :ID_ACTION
AND P.ID_PERSON = A.ID_PERSON)
AND (CURRENT_DATE - P.DOB) / 365 = :AGE
INTO :MEN;

и

SELECT COUNT(*)
FROM PERSON P
WHERE P.SEX = 'ж'
AND EXISTS(SELECT A.ID_PERSON
FROM ANSWER A
WHERE A.ID_ACTION = :ID_ACTION
AND P.ID_PERSON = A.ID_PERSON)
AND (CURRENT_DATE - P.DOB) / 365 = :AGE
INTO :WOMEN;

объедини в один, а :MEN и :WOMEN определяй через CASE.
Также через CASE объедини попарные запросы, возвращающие
:MEN_EMB и :WOMEN_EMB.

[sag]

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

[Лысый]

Сделал так:

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

CREATE PROCEDURE REP$AGE_DIAGRAM (
    ID_ACTION INTEGER)
RETURNS (
    AGE SMALLINT,
    MEN INTEGER,
    WOMEN INTEGER,
    MEN_EMB INTEGER,
    WOMEN_EMB INTEGER)
AS
BEGIN
  /* Процедура для диаграммы по возрастам в разрезе мужчин и женщин */

  FOR
    SELECT DISTINCT (CURRENT_DATE - P.DOB) / 365
      FROM PERSON P
     WHERE EXISTS(SELECT A.ID_PERSON
                      FROM ANSWER A
                     WHERE A.ID_ACTION = :ID_ACTION
                       AND P.ID_PERSON = A.ID_PERSON)
     ORDER BY 1
      INTO :AGE
  DO BEGIN

    SELECT SUM(CASE WHEN P.SEX = 'м' THEN 1 ELSE 0 END)
         , SUM(CASE WHEN P.SEX = 'ж' THEN 1 ELSE 0 END)
      FROM PERSON P
     WHERE EXISTS(SELECT A.ID_PERSON
                    FROM ANSWER A 
                   WHERE A.ID_ACTION = :ID_ACTION 
                     AND P.ID_PERSON = A.ID_PERSON)
       AND (CURRENT_DATE - P.DOB) / 365 = :AGE
      INTO :MEN
         , :WOMEN;

    SELECT SUM(CASE WHEN P.SEX = 'м' THEN A.EMBEDDED_CNT ELSE 0 END)
         , SUM(CASE WHEN P.SEX = 'ж' THEN A.EMBEDDED_CNT ELSE 0 END)
      FROM PERSON P LEFT JOIN ANSWER A ON P.ID_PERSON = A.ID_PERSON
     WHERE A.ID_ACTION = :ID_ACTION
       AND (CURRENT_DATE - P.DOB) / 365 = :AGE
      INTO :MEN_EMB
         , :WOMEN_EMB;

    SUSPEND; 

  END
END

Результат:

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

------ Performance info ------
Prepare time = 0ms
Execute time = 12s 813ms
Avg fetch time = 400,41 ms
Current memory = 4 496 400
Max memory = 5 487 496
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 22
Fetches from cache = 6 150 899

Не индексных чтений: 2 817 785.

Замена внешнего запроса на неявное связывание работает чуть дольше..

>dimitr
count с case я так понял описка?

В общем результат лучше в 2 раза

[kdv]

ты забыл про совет distinct попробовать переписать.

[Лысый]

ты забыл про совет distinct попробовать переписать.

Не забыл попробовал:

Замена внешнего запроса на неявное связывание работает чуть дольше..

[sag]

> Не забыл попробовал
странно, замена натурального перебора таблицы и выполнения подзапроса на каждой записи (хотя и выполняющегося по индексу)
на, казалось бы, более правильный вариант дает замедление. Хм.

Ну тогда запробуй вот такой вот так:
SELECT (CURRENT_DATE - P.DOB) / 365 AGE,
SUM(CASE WHEN P.SEX = 'м' THEN A.EMBEDDED_CNT ELSE 0 END) MEN_EMB,
SUM(CASE WHEN P.SEX = 'ж' THEN A.EMBEDDED_CNT ELSE 0 END) WOMEN_EMB,
COUNT(DISTINCT (CASE WHEN (P.SEX = 'м') THEN P.ID_PERSON ELSE NULL END) MEN,
COUNT(DISTINCT (CASE WHEN (P.SEX = 'ж') THEN P.ID_PERSON ELSE NULL END) WOMEN
FROM PERSON P, ANSWER A
WHERE A.ID_ACTION = :ID_ACTION
AND P.ID_PERSON = A.ID_PERSON
GROUP BY 1

[Лысый]

Попробовал! Супер!

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

Plan
PLAN SORT (JOIN (A INDEX (FK_ACTION__ANSWER),P INDEX (PK_PERSON)))

Adapted Plan
PLAN SORT (JOIN (A INDEX (FK_ACTION__ANSWER),P INDEX (PK_PERSON)))

------ Performance info ------
Prepare time = 0ms
Execute time = 1s 593ms
Avg fetch time = 63,72 ms
Current memory = 4 328 232
Max memory = 5 487 496
Memory buffers = 2 048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 373 439

Не индексных чтений нет вообще.
Спасибо огромное!

[sag]

> Не индексных чтений нет вообще.
> Спасибо огромное!

Да не за что. Практикуйся в сиквэле, будет тебе щастье. От себя лично, спасибо огромное выражаю dimitr-у и ко за case, group by number, и прочие полезные вещи. Спасибо!
Эх, вот только в пробке простоял два часа и мои симпатишные сотрудницы поразбежались все.

[Лысый]

Практикуюсь
Разъясните мне следующую конструкцию:

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

COUNT(DISTINCT (CASE WHEN (P.SEX = 'м') THEN P.ID_PERSON ELSE NULL END)

Я так понимаю, если CASE возвращает null, тогда COUNT его не считает... А вот наличие DISTINCT в COUNT для меня новость. Его во всех агрегированных функциях м. использовать?

Эх, вот только в пробке простоял два часа и мои симпатишные сотрудницы поразбежались все.

Ну, это с их стороны не простительно

[sag]

> COUNT(DISTINCT (CASE WHEN (P.SEX = 'м')
> THEN P.ID_PERSON ELSE NULL END)
> Я так понимаю, если CASE возвращает null, тогда COUNT его не считает...

да, count ведет себя именно таким макаром.

> А вот наличие DISTINCT в COUNT для меня новость.
> Его во всех агрегированных функциях м. использовать?

Стандарт позволяет использовать "квантификатор" distinct
в агрегатных функциях (count, max, min, sum, avg). Однако
практически полезным мне представляется его сочетание только
с count. В сочетании с max или min distinct вообще не влияет
на результат, возвращаемый функцией; для сочленения distinct-а с
sum или avg мне не удается придумать случая, когда оно оказалось
бы полезным.

[Лысый]

Ок, спасибо!