Мастера DELPHI (документация, исходники, скачать)

Основная («Начинающим»)/ Базы / WinAPI / Компоненты / Сети / Media / Игры / Corba и COM / KOL / FreePascal / .Net / Прочее / RSDN.ORG

« предыдущая ветвь | форум | следующая ветвь »

Оптимизация запроса [D7, Firebird 2.0]

Страницы: < 1 2
Megabyte © (2007-02-28 13:19:00)
Тема: Ремонт телефонов.
Телефон проходит различные стадии(состояния) ремонта,например: приемка, входной контроль, ремонт, склад, выходной контроль, отгрузка.
Есть таблица событий перехода на другое состояние(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", чтобы избавиться от подзапроса.

Megabyte (2007-03-01 14:12:00) [21]
Эмм, да не. План вот такой был:
"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. Пошел уже по другому пути, правда не идеально отображающему статистику, но и статистика идеальное положение не должна имхо отображать :) только примерное

Danilka (2007-03-01 10:55:00) [22]
План покажи. У меня было несколько достаточно простых запросов на которых оптимизатор FB лажался - вместо индексов использовал фулскан.

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

Виталий Панасенко (2007-02-28 16:20:00) [24]
А, опять сорри. Не досмотрел вот это
> from repair_progress P join repair_progress R

Виталий Панасенко (2007-02-28 16:17:00) [25]
Извиняюсь, забыл в последнем запросе указать переменные, в которые возвращаются значения...

Виталий Панасенко (2007-02-28 16:15:00) [26]
А так в ХП или 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

Megabyte (2007-02-28 16:13:00) [27]
Ладно, уговорил начальника пойти по другому пути :)
Есть таблица векторов: vector_id, src_state_id = dest_state_id
Буду ее использовать, раньше начальник не хотел, чтобы я к ней привязывался. Просто она кривая страшно и недоработанная. Не все вектора проставлены. Сначала ее придется доработать.

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

Desdechado (2007-02-28 15:27:00) [28]
> Итак в ХП похожий запрос(там только поля в select другие).
Я имел ввиду раскрутить сложный запрос на FOR SELECT с внутренним простым запросом. Вдруг быстрее получится.

Sergey13 (2007-02-28 15:17:00) [29]
> [8] Megabyte © (28.02.07 14:45)
Ты хочешь что бы тебя пожалели? 8-)

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

Megabyte (2007-02-28 14:45:00) [30]

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

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

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

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

Sergey13 (2007-02-28 14:10:00) [31]
> а в 400 000 записях проставлять новое поле замучаешься...

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

Gin2 (2007-02-28 14:04:00) [32]

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

курсор

Megabyte (2007-02-28 13:55:00) [33]

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

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

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

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

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

Sergey13 (2007-02-28 13:38:00) [34]
> [0] Megabyte © (28.02.07 13:19)

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

Gin2 (2007-02-28 13:32:00) [35]

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

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

Gin2 (2007-02-28 13:31:00) [36]

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

> AVG(R.progress_timestamp - P.progress_timestamp)

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

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

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

Desdechado (2007-02-28 13:24:00) [37]
ХП не пробовал?

Страницы: < 1 2

Наверх

	« предыдущая ветвь \| форум \| следующая ветвь »