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

← →
zorik © (2008-06-18 14:43) [0]

Помогите оптимизировать View. Суть такова. Есть 2 основные таблицы + справочники. Отношение один ко многим. В реальности в 80-90% случаев получается отношение один к одному, в остальных - 10-20% - одной записи MEZWELLO соответствуют 2-5 записей MEZO

Структура таблици MEZWELLO
CREATE TABLE MEZWELLO ( ID_MEZWELLO INTEGER NOT NULL, DATE_MEZWELLO TIMESTAMP NOT NULL, ID_WELL INTEGER NOT NULL ); ALTER TABLE MEZWELLO ADD CONSTRAINT PK_MEZWELLO PRIMARY KEY (ID_MEZWELLO); ALTER TABLE MEZWELLO ADD CONSTRAINT FK_MEZWELLO_WELL FOREIGN KEY (ID_WELL) REFERENCES WELL (ID_WELL);

Структура таблици MEZO
CREATE TABLE MEZO ( ID_MEZO INTEGER NOT NULL, ID_WELL INTEGER NOT NULL, QO_M DOUBLE PRECISION DEFAULT 0 NOT NULL, ID_MEZWELLO INTEGER ); ALTER TABLE MEZO ADD CONSTRAINT PK_MEZO PRIMARY KEY (ID_MEZO); ALTER TABLE MEZO ADD CONSTRAINT FK_MEZO_WELL FOREIGN KEY (ID_WELL) REFERENCES WELL (ID_WELL);

Структура View
CREATE VIEW EXP_LIST_WELL_W ( NAME_WELL, ZDATE, QO_M, ) AS select well.name_well, mezwello.date_mezwello zdate, sum(mezo.qo_m) qo_m, from mezwello left join mezwello on (mezwello.id_mezwello = mezo.id_mezwello) left join well on (mezwello.id_well = well.id_well) group by mezwello.date_mezwello, mezwello.id_well, mezo.id_mezwello, well.name_well, well.id_well, well.sortname_well, order by mezwello.date_mezwello, well.sortname_well

← →
Sergey13 © (2008-06-18 15:04) [1]

А зачем группировать по 6-и полям, если во вьюхе всего 2 неагрегатных поля?

← →
zorik © (2008-06-18 15:18) [2]

изменил
CREATE VIEW EXP_LIST_WELL_W2 ( NAME_WELL, ZDATE, QO_M ) AS select well.name_well, mezwello.date_mezwello zdate, sum(mezo.qo_m) qo_m from mezwello inner join mezo on (mezwello.id_mezwello = mezo.id_mezwello) inner join well on (mezwello.id_well = well.id_well) group by mezwello.date_mezwello, well.name_well, well.sortname_well order by mezwello.date_mezwello, well.sortname_well

хотелось бы еще быстрее. На самом деле там 10 сум и поле sortname_well вычислительное и использует функцию из UDF

← →
Sergey13 © (2008-06-18 15:49) [3]

> [2] zorik © (18.06.08 15:18)

> изменил
а это зачем?
> group by
> mezwello.date_mezwello,
> well.name_well,
> well.sortname_well
если в результате останется только

> select
> well.name_well,
> mezwello.date_mezwello zdate,
> sum(mezo.qo_m) qo_m

> хотелось бы еще быстрее.

На сколько конкретно? Сколько сейчас? Каков объем таблиц? Что за "функцию из UDF"?

← →
zorik © (2008-06-18 15:54) [4]

Ругается без єтого. Мне при переходе на Firebird 2.0 много запросов пришлось переписать. В 1.5 наверно прошло бы

← →
Правильный-Вася (2008-06-18 15:54) [5]

не вижу метаданных WELL, используемой во вьюхе

← →
Sergey13 © (2008-06-18 15:57) [6]

> [4] zorik © (18.06.08 15:54)
> Ругается без єтого

Я так понял непечатно ругается?

← →
zorik © (2008-06-18 16:01) [7]

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause).

← →
Правильный-Вася (2008-06-18 16:01) [8]

вот интересно, в первом варианте left [outer] join, а во втором inner join

это че, фиолетово?

← →
zorik © (2008-06-18 16:03) [9]

таблица WELL
CREATE TABLE WELL ( ID_WELL INTEGER NOT NULL, ID_FIELD INTEGER NOT NULL, DESCRIPTION VARCHAR(200), NAME_WELL VARCHAR(20) NOT NULL, UID_WELL VARCHAR(20), OLDID_WELL VARCHAR(10), SORTNAME_WELL COMPUTED BY (sortname(name_well)) ); ALTER TABLE WELL ADD CONSTRAINT PK_WELL PRIMARY KEY (ID_WELL); ALTER TABLE WELL ADD CONSTRAINT FK_WELL_FIELD FOREIGN KEY (ID_FIELD) REFERENCES FIELD (ID_FIELD);

← →
Sergey13 © (2008-06-18 16:05) [10]

> [7] zorik © (18.06.08 16:01)

А так?
CREATE VIEW EXP_LIST_WELL_W2 ( NAME_WELL, ZDATE, QO_M ) AS select well.name_well, mezwello.date_mezwello zdate, sum(mezo.qo_m) qo_m from mezwello inner join mezo on (mezwello.id_mezwello = mezo.id_mezwello) inner join well on (mezwello.id_well = well.id_well) group by mezwello.date_mezwello, well.name_well, order by mezwello.date_mezwello, well.name_well

← →
Sergey13 © (2008-06-18 16:06) [11]

+ надо ли вообще order by во вьюху совать?

← →
Правильный-Вася (2008-06-18 16:06) [12]

> Invalid expression in the ORDER BY clause (not contained
> in either an aggregate function or the GROUP BY clause).

нафига сортировать по полю, которого нет в выборке?
да и сортированная вьюха - фигня какая-то, если понадобится сортировать выборку из нее по-другому, то сервер будет дважды делать бесполезную работу

← →
zorik © (2008-06-18 16:35) [13]

> Правильный-Вася (18.06.08 16:06) [12]

согласен, забираю сортировку

← →
zorik © (2008-06-18 16:40) [14]

> Правильный-Вася (18.06.08 16:01) [8]
> вот интересно, в первом варианте left [outer] join, а во
> втором inner joinэто че, фиолетово?

с left я протупил

← →
zorik © (2008-06-18 16:47) [15]

> Sergey13 © (18.06.08 16:05) [10]

сортировать надо именно по sortname, а не по name. Когда-то на эту тему был длинный спор, не хочу его поднимать. sortname - стринговое поле, которое формирується в udf на основе поля name

На данный момент ситуация такая:
1. забрал сортировку
2. добавил sortname в вьюху и соответственно поле попало в group by

Результаты:
тестовая вьюшка: Execute: 2 s 781 ms Fetch: 2 s 781 ms

реальная: Execute: 3 s 16 ms Fetch: 2 s 953 ms

← →
Sergey13 © (2008-06-18 16:51) [16]

> [15] zorik © (18.06.08 16:47)
> сортировать надо именно по sortname

> 1. забрал сортировку

Как понимать тебя, Саид? (с) т.Сухов
8-)

← →
Правильный-Вася (2008-06-18 16:53) [17]

индекс по калькулируемому полю есть?

Если уж это sortname такое важное, то почему бы не сделать его НОРМАЛЬНЫМ полем и следить за его изменением с помощью тригеров.
ИМХО всяко шустрее должно быть.

← →
Правильный-Вася (2008-06-18 16:57) [19]

> Как понимать тебя, Саид? (с) т.Сухов

имхо, Абдулла

> Как понимать тебя, Саид? (с) т.Сухов8-)

CREATE VIEW EXP_LIST_WELL_W2 ( SORTNAME_WELL, NAME_WELL, ZDATE, QO_M ) AS select well.sortname_well, well.name_well, mezwello.date_mezwello zdate, sum(mezo.qo_m) qo_m from mezo inner join mezwello on (mezo.id_mezwello = mezwello.id_mezwello) inner join well on (well.id_well = mezwello.id_well) group by well.sortname_well, well.name_well, mezwello.date_mezwello

> Sergey13 © (18.06.08 16:54) [18]
> Если уж это sortname такое важное, то почему бы не сделать
> его НОРМАЛЬНЫМ полем и следить за его изменением с помощью
> тригеров.ИМХО всяко шустрее должно быть.

Интересно можно ли такое реализовать - тригер срабатывает на добавление или изменение и используя функцию из UDF заполняет дополнительное поле. И не надо его будет пересчитывать каждый раз. Добавления проходят крайне редко, да и в таблице WELL всего около 1500 записей

Реализовал тригер, как задумал в [21]:
CREATE TRIGGER TR_SETSORTNAME_BI FOR WELL ACTIVE BEFORE INSERT OR UPDATE POSITION 0 AS BEGIN new.sortname_well=sortname(new.name_well); END ;

Дальше буду сам остальные погрешности исправлять.
Спасибо всем!

← →
Правильный-Вася (2008-06-18 17:48) [23]

план запроса у вьюхи посмотри
станет ясно направление движения

> Правильный-Вася (18.06.08 17:48) [23]

еще не дорос до планов. надо изучать

добавил индекс для поля sortname, раньше когда оно было вычислительным. это сделать нельзя было

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