Оптимизация запроса

← →
Megabyte © (2007-02-28 13:19) [0]

Тема: Ремонт телефонов.
Телефон проходит различные стадии(состояния) ремонта,например: приемка, входной контроль, ремонт, склад, выходной контроль, отгрузка.
Есть таблица событий перехода на другое состояние(Repair_progress), вида:
прогресс_ИД(progress_id), состояние_ИД(repair_state_id), идентификатор одного ремонта(repair_id), время_перехода_на_состояние(progress_timestamp), т.д.
Порядка 400 000 записей.
Мне потребовалось расчитывать, сколько времени аппарат находился на данном состоянии, т.е. время перехода на следующее состояние-время перехода на данное состояние или Repair_progress2.progress_timestamp - Repair_progress1.progress_timestamp.
Полностью запрос: select S.repair_state_title, AVG(R.progress_timestamp - P.progress_timestamp) from repair_progress P join repair_progress R on R.repair_id = P.repair_id and R.repair_progress_id > P.repair_progress_id join repair_state S on P.repair_state_id = S.repair_state_id where (P.is_delete > 0) and (R.is_delete > 0) and (R.repair_progress_id between 126347 and 143432) and (P.repair_state_id = :param) and (R.repair_progress_id = ( select min(Pr.repair_progress_id) from repair_progress Pr where (Pr.is_delete > 0) and (Pr.repair_id = P.repair_id) and (Pr.repair_progress_id > P.repair_progress_id) )) group by S.repair_state_title, P.repair_progress_id, R.repair_state_id
Пояснения:
1) (R.repair_progress_id between 126347 and 143432) - просто задаю период, для которого вычислить среднее время. Хотел этим сократить выборку...
2) (R.repair_progress_id = (
select min(Pr.repair_progress_id)
from repair_progress Pr
where (Pr.is_delete > 0) and
(Pr.repair_id = P.repair_id) and (Pr.repair_progress_id > P.repair_progress_id) )) - этот подзапрос выбирает минимальный переход, т.к., допустим, для состояния "приемка" по условию "R.repair_id = P.repair_id
and R.repair_progress_id > P.repair_progress_id" попадут и выходной контроль, и ремонт, и выходной контроль, и т.д., а нужно только следующий ближайший переход, в данном случае "выходной контроль".
Явно указать следующее состояние невозможно, т.к. схема на самом деле ветвится, допустим:
с входного котроля аппарат может пойти в ремонт, а может на склад(для ожидания запчасти)! Да и подзапрос ссылается на врешний запрос...

Из-за этого подзапроса все выполняется крайне медленно.
Какие посоветуете методы оптимизации?

В идеале мне надо как-то в условии указывать, что разница между 2-мя временами/ИД должна быть минимальной для условий "R.repair_id = P.repair_id
and R.repair_progress_id > P.repair_progress_id", чтобы избавиться от подзапроса.

← →
Desdechado © (2007-02-28 13:24) [1]

ХП не пробовал?

← →
Gin2 (2007-02-28 13:31) [2]

> Мне потребовалось расчитывать, сколько времени аппарат находился
> на данном состоянии,

> AVG(R.progress_timestamp - P.progress_timestamp)

как-то не стыкуется... может sum?

> (P.is_delete > 0) and (R.is_delete > 0) and

почему два раза?

← →
Gin2 (2007-02-28 13:32) [3]

> почему два раза?

сорри недоглядел

← →
Sergey13 © (2007-02-28 13:38) [4]

> [0] Megabyte © (28.02.07 13:19)

Похоже никак - подзапрос отрабатывает для каждой записи - отсюда и тормоза. Можно попробовать добавить поле время_ухода_с_состояния, которое заполнять в тригере на вставку новой записи. Тогда все бы упростилось.

← →
Megabyte © (2007-02-28 13:55) [5]

> Desdechado © (28.02.07 13:24) [1]

ХП не пробовал?

Итак в ХП похожий запрос(там только поля в select другие).

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

Да я и сам давно понял, что это упростило бы выборку. Но надо собрать некоторую статистику по существующим записям, а в 400 000 записях проставлять новое поле замучаешься...

← →
Gin2 (2007-02-28 14:04) [6]

> а в 400 000 записях проставлять новое поле замучаешься..
> .
>

курсор

← →
Sergey13 © (2007-02-28 14:10) [7]

> а в 400 000 записях проставлять новое поле замучаешься...

Ну так не вручную же в гриде тыкать. 8-)

← →
Megabyte © (2007-02-28 14:45) [8]

> Sergey13 © (28.02.07 14:10) [7]

> а в 400 000 записях проставлять новое поле замучаешься...

Ну так не вручную же в гриде тыкать. 8-)

:)
Ну это:
1) даже если я сделаю запрос, то он по идее повесит нах базу, т.к. по сути, чтобы получить нужное значение поля, надо воспользоваться старым алгоритмом.
2) Мне никто не даст изменять структуру старых таблиц, я не главный программист.

← →
Sergey13 © (2007-02-28 15:17) [9]

> [8] Megabyte © (28.02.07 14:45)
Ты хочешь что бы тебя пожалели? 8-)

Подготовь запрос, чтоб гарантировано работал, останься вечером, когда все уйдут и запусти. Пол лимона не думаю, что бы считало бесконечно долго. Исходный то запрос сколько по времени идет?

← →
Desdechado © (2007-02-28 15:27) [10]

> Итак в ХП похожий запрос(там только поля в select другие).
Я имел ввиду раскрутить сложный запрос на FOR SELECT с внутренним простым запросом. Вдруг быстрее получится.

← →
Megabyte © (2007-02-28 16:13) [11]

Ладно, уговорил начальника пойти по другому пути :)
Есть таблица векторов: vector_id, src_state_id = dest_state_id
Буду ее использовать, раньше начальник не хотел, чтобы я к ней привязывался. Просто она кривая страшно и недоработанная. Не все вектора проставлены. Сначала ее придется доработать.

з.ы. Главное, убедился, что я не тупой и всё, что можно для оптимизации данного запроса, сделал %)

← →
Виталий Панасенко © (2007-02-28 16:15) [12]

А так в ХП или EXECUTE BLOCK ?
for
select min(Pr.repair_progress_id)
from repair_progress Pr
where (Pr.is_delete > 0) and
(Pr.repair_id = P.repair_id) and (Pr.repair_progress_id > P.repair_progress_id)
into :repair_progress_id
do
begin

select S.repair_state_title, AVG(R.progress_timestamp - P.progress_timestamp)
from repair_progress P join repair_progress R on R.repair_id = P.repair_id
and R.repair_progress_id > P.repair_progress_id
join repair_state S on P.repair_state_id = S.repair_state_id
where (P.is_delete > 0) and (R.is_delete > 0) and
(R.repair_progress_id between 126347 and 143432) and
(P.repair_state_id = :param) and (R.repair_progress_id = :repair_progress_id)
group by S.repair_state_title, P.repair_progress_id, R.repair_state_id;

suspend;
end

← →
Виталий Панасенко © (2007-02-28 16:17) [13]

Извиняюсь, забыл в последнем запросе указать переменные, в которые возвращаются значения...

← →
Виталий Панасенко © (2007-02-28 16:20) [14]

А, опять сорри. Не досмотрел вот это
> from repair_progress P join repair_progress R

← →
Sergey13 © (2007-02-28 16:22) [15]

> [11] Megabyte © (28.02.07 16:13)
> з.ы. Главное, убедился, что я не тупой и всё, что можно
> для оптимизации данного запроса, сделал %)
Кстати не факт. Ты про индексы например вообще вроде ничего не написал. И плана не привел. Возможно удастся оптимизировать подзапрос, а с ним весь запрос.

← →
Danilka © (2007-03-01 10:55) [16]

План покажи. У меня было несколько достаточно простых запросов на которых оптимизатор FB лажался - вместо индексов использовал фулскан.

← →
Megabyte © (2007-03-01 14:12) [17]

Эмм, да не. План вот такой был:
"Plan
PLAN (PR ORDER PK_REPAIR_PROGRESS INDEX (FK_REPAIR_PROGRESS_REPAIR))
PLAN SORT (JOIN (R INDEX (PK_REPAIR_PROGRESS), P INDEX (FK_REPAIR_PROGRESS_REPAIR), S INDEX (PK_REPAIR_STATE)))

Adapted Plan
PLAN (PR ORDER PK_REPAIR_PROGRESS INDEX (FK_REPAIR_PROGRESS_REPAIR)) PLAN SORT (JOIN (R INDEX (PK_REPAIR_PROGRESS), P INDEX (FK_REPAIR_PROGRESS_REPAIR), S INDEX (PK_REPAIR_STATE)))"

Насчет индексов: все поля связи, используемые в запросе, являются ключами(первичными и/или вторичными).

p.s. Пошел уже по другому пути, правда не идеально отображающему статистику, но и статистика идеальное положение не должна имхо отображать :) только примерное

← →
Sergey13 © (2007-03-01 14:20) [18]

> [17] Megabyte © (01.03.07 14:12)

> Насчет индексов: все поля связи, используемые в запросе,
> являются ключами(первичными и/или вторичными).

Для подобных запросов это не есть показатель оптимальности. Гораздо лучше для оптимзатора (по крайней мере для ораклового 8-) было бы, если бы ВСЕ данные брались из индекса, т.е. что-бы чтения таблиц ввобще не было.

← →
evvcom © (2007-03-01 15:33) [19]

> [4] Sergey13 © (28.02.07 13:38)
> Похоже никак - подзапрос отрабатывает для каждой записи
> - отсюда и тормоза.

Надо бы убрать этот select min из where и написать нормальный join. Эх, FB не знаю, но точно знаю, что нет в нем аналитических функций, как в Оракле. В нем есть хотя бы конструкция аналогичная with subquery as (select ..) select ..?
Или другой вариант. Добавь в repair_progress ссылку на предыдущую запись, тогда поиск минимального можно будет заменить на банальный join. Insert усложнится, но не намного.

← →
evvcom © (2007-03-01 15:36) [20]

> [8] Megabyte © (28.02.07 14:45)
> Мне никто не даст изменять структуру старых таблиц

А... пропустил.

> я не главный программист

так пора им становиться! :)

← →
svs_sergo © (2007-03-01 16:59) [21]

Для MSSQL прокатил бы следующий запрос, может подойдет.

select P.progress_id, P.repair_id, P.repair_state_id, P.progress_timestamp,
(select top 1 progress_timestamp from repair_progress where progress_id = P.progress_id
and progress_timestamp > P.progress_timestamp) as progess_timeout
from repair_progress P

можно сразу в функцию расчета длинны периода

select P.progress_id, P.repair_id, P.repair_state_id,
DATEDIFF(hour, P.progress_timestamp,
(select top 1 progress_timestamp from repair_progress where progress_id = p.progress_id and progress_timestamp > P.progress_timestamp)) as progess_Hour
from repair_progress P

А для оптимизации я бы закинул бы первый запрос в отдельную таблицу INTO или INSERT и работал бы с ней.

← →
Gin2 (2007-03-01 17:21) [22]

> select top 1 progress_timestamp from repair_progress where
> progress_id = P.progress_id
> and progress_timestamp > P.progress_timestamp)

это ты зря. там min надо

← →
Gin2 (2007-03-01 17:22) [23]

я создал себе тестовый пример на MS SQL - 100 тыс. записей отработали за 1 сек.

← →
svs_sergo © (2007-03-01 17:31) [24]

ага точно забыл order by progress_timestamp и теперь min ненадо!

select P.progress_id, P.repair_id, P.repair_state_id, P.progress_timestamp,
(select top 1 progress_timestamp from repair_progress where progress_id = P.progress_id
and progress_timestamp > P.progress_timestamp order by progress_timestamp) as progess_timeout
from repair_progress P

← →
Gin2 (2007-03-01 17:39) [25]

> ага точно забыл order by progress_timestamp и теперь min
> ненадо!
>

не надо, а скорость?

← →
evvcom © (2007-03-02 09:01) [26]

> [24] svs_sergo © (01.03.07 17:31)

min ищет всего одну строку, хотя и перебирает все, а order by должен отсортировать все, а потом тебе все равно нужна только одна. Столько ненужной работы заставляешь сервак делать. Думаешь это правильно?

Это самый простой и читаемый запрос для решения этой задачи. Через JOIN необходимо трехэтажное вложение городить. Скажем, если это будет делаться для ежемесячного отчета раз в месяц, я бы оставил этот вариант.
order by, кстати, сортирует не всю базу, а только выборку на текущий progress_id.

Если речь идет о многократной ежедневной задаче, то необходимо дополнить структуру таблицы repair_progress новым полем progess_timeout (и может даже полем "продолжимельность"), чтобы она отвечала новым требованиям.

← →
Gin2 (2007-03-02 16:50) [28]

> order by, кстати, сортирует не всю базу, а только выборку
> на текущий progress_id.

а смысл?

← →
Jan (2007-03-02 16:54) [29]

> Если речь идет о многократной ежедневной задаче, то необходимо
> дополнить структуру таблицы repair_progress новым полем
> progess_timeout (и может даже полем "продолжимельность"),
> чтобы она отвечала новым требованиям.

сложно сопровождать. проще parent_process_id

> а смысл?

В цикле отрабатывается запрос:

select top 1 progress_timestamp
from repair_progress
where progress_id = P.progress_id
and progress_timestamp > P.progress_timestamp
order by progress_timestamp

Т.е. сортировка производится по выборке для конкретного progress_id.
Я показал как можно отказаться от агрегатной функции MIN, которая автоматически требует использования JOIN и группировки в подзапросе.

Идея в том, чтобы в выборку впихнуть поле со значением следующей записи. Есть ли в этом смысл, решать тебе.

Jan, абсолютно с тобой согласен!

← →
Gin2 (2007-03-02 17:30) [32]

> Я показал как можно отказаться от агрегатной функции MIN,
> которая автоматически требует использования JOIN и группировки
> в подзапросе.

с чего бы это?

← →
Gin2 (2007-03-02 17:32) [33]

select min(progress_timestamp) from repair_progress where progress_id = P.progress_id and progress_timestamp > P.progress_timestamp
Отработает как надо, заюзает индекс если надо будет, групировки нет и джойна тоже.

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

← →
Gin2 (2007-03-02 17:49) [35]

потому как в ФБ запросы будут все-равно разложены на курсоры

> select min(progress_timestamp)
> from repair_progress
> where progress_id = P.progress_id
> and progress_timestamp > P.progress_timestamp
>
> Отработает как надо, заюзает индекс если надо будет, групировки
> нет и джойна тоже.
>

Gin2, а где тут основная суть вопроса - расчитать период между progress_timestamp разных записей.

> evvcom © (01.03.07 15:36) [20]

> [8] Megabyte © (28.02.07 14:45)
> я не главный программист

так пора им становиться! :)

Ну это, пока молодой, только обучаюсь. :)
Но я работаю один. У нас с тем главным прогером область обязанностей разная.

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

Оптимизация запроса Найти похожие ветки