Мастера 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-05 15:34:00) [1]

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

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

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

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

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

svs_sergo (2007-03-05 08:31:00) [2]

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

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

Gin2 (2007-03-02 17:49:00) [3]
потому как в ФБ запросы будут все-равно разложены на курсоры

atruhin (2007-03-02 17:42:00) [4]
Была подобная задача, оптимизировать запрос до приемлемой скорости не смог, помогла ХП.
В ней получал полное декартовое произведение таблиц, с сортировкой по дате, и пробегал по набору
оставляя нужные, таким образом избавился от подзапроса.
Как ни странно, но работает на порядок быстрее.

Gin2 (2007-03-02 17:32:00) [5]
select min(progress_timestamp) from repair_progress where progress_id = P.progress_id and progress_timestamp > P.progress_timestamp
Отработает как надо, заюзает индекс если надо будет, групировки нет и джойна тоже.

Gin2 (2007-03-02 17:30:00) [6]

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

с чего бы это?

svs_sergo (2007-03-02 17:29:00) [7]
Jan, абсолютно с тобой согласен!

svs_sergo (2007-03-02 17:26:00) [8]

> а смысл?

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

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 (2007-03-02 16:54:00) [9]

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

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

Gin2 (2007-03-02 16:50:00) [10]

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

а смысл?

svs_sergo (2007-03-02 14:03:00) [11]
Это самый простой и читаемый запрос для решения этой задачи. Через JOIN необходимо трехэтажное вложение городить. Скажем, если это будет делаться для ежемесячного отчета раз в месяц, я бы оставил этот вариант.
order by, кстати, сортирует не всю базу, а только выборку на текущий progress_id.

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

evvcom (2007-03-02 09:01:00) [12]
> [24] svs_sergo © (01.03.07 17:31)

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

Gin2 (2007-03-01 17:39:00) [13]

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

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

svs_sergo (2007-03-01 17:31:00) [14]
ага точно забыл 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:22:00) [15]
я создал себе тестовый пример на MS SQL - 100 тыс. записей отработали за 1 сек.

Gin2 (2007-03-01 17:21:00) [16]

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

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

svs_sergo (2007-03-01 16:59:00) [17]
Для 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 и работал бы с ней.

evvcom (2007-03-01 15:36:00) [18]
> [8] Megabyte © (28.02.07 14:45)
> Мне никто не даст изменять структуру старых таблиц

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

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

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

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

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

Sergey13 (2007-03-01 14:20:00) [20]
> [17] Megabyte © (01.03.07 14:12)

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

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

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

Наверх

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