оптимизация использования триггера

Запросы, планы, оптимизация запросов, ...

Модераторы: kdv, CyberMax

Ответить
Trech
Сообщения: 3
Зарегистрирован: 29 апр 2009, 11:29

оптимизация использования триггера

Сообщение Trech » 29 апр 2009, 12:08

Добрый день.

Опыта в разработке баз данных у меня не очень много, но есть желание прокачать это умении );

Попробую как можно доступнее объяснить свою задачу (использую Firebird 2.1.2).

Таблица ячейка (cell)
id_cell (pk)
cell_param1
cell_param2

Таблица элемент_типа_1 (element1)
id_element (pk)
element_param1
element_param2

Таблица элемент_типа_2 (element2)
id_element (pk)
element_param1
element_param2

Таблица вхождения элемента типа 1 в ячейку (сell_element1)
id
id_cell (fk)
id_element (fk)
count

Таблица вхождения элемента типа 2 в ячейку (cell_element2)
id
id_cell (fk)
id_element (fk)
count

********************************************************************
Таблиц с типа элементов может быть много (соответственно таблиц вхождения элементов в таблицу будет столько же).

Запись cell_param# в таблице cell рассчитывается как:

cell_param1 = параметр элемента (element_param1) * на количество (count)
cell_param2 = параметр элемента (element_param2) * на количество (count)

При создании новой ячейки значения полей cell_param1 и cell_param2 равны 0, а при добавлении в любую из таблиц «вхождения элемента типа # в ячейку» нового значения через триггер пересчитываю значение cell_param1 для ячейки cell_id в соответствии с указанным количеством элемента id_element.

Так же через триггер организован пересчет значения cell_param при изменении количества элементов (update в таблице cell_ellement# для element_id) и при удалении записи.

***********************************************************************
Триггер для обработки данных после вставки новой записи в таблицу cell_element1 выглядит так:

begin

update cell set

cell_param1 =
( cell_param1 +
((select element_param1 from element1 where element_id = new.element_id)
* new."COUNT")
),
cell_param2 =
( cell_param2 +
((select element_param2 from element1 where element_id = new.element_id)
* new."COUNT")
)

where cell_id= NEW.cell_id;

end

Соответсвенно для таблицы cell_element2 код будет идентичным, измениться только «from element1» на «from element2»

***********************************************************************

Допустим, что таблиц с типами элементов у нас 10, тогда надо для 10 таблиц cell_element[1-10] создать почти одинаковый триггер, а если надо что то будет изменить, то придется перебрать все таблицы.

Есть ли красивое решение данной проблемы? Например, создать универсальный триггер, который будет принимать название таблицы из которой его вызвали и генерировать запрос с нужным параметром запроса «from element#»?

***********************************************************************

Код, который выполняется в триггере не сильно нагружает базу? (2 вложенных запроса, а их может быть и больше с увеличением числа вычисляемых полей).

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: оптимизация использования триггера

Сообщение Dimitry Sibiryakov » 29 апр 2009, 14:00

"Не очень много" в твоём случае надо читать как "практически нет". Схема ужасна. Триггер работать будет, но медленно. При большом потоке изменений в составе ячейки - всё сдохнет на взаимоблокировках.

Твоя туча таблиц сворачивается до трёх - ячейки, элементы (имеющие поле "тип") и связь между ними. Если количество элементов на ячейку оценивается менее чем четырёхзначным числом - выкинуть нафиг параметры у ячейки и считать их на лету (возможно - создать вьюху). Соответственно - выкинуть триггеры. Это даст 100-процентную оптимизацию их использования.

Trech
Сообщения: 3
Зарегистрирован: 29 апр 2009, 11:29

Re: оптимизация использования триггера

Сообщение Trech » 29 апр 2009, 15:46

Спасибо за критику.
А схема не ужасна, она просто развернута ))

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

А по поводу пересчета на лету... ячейка может входить в состав блоков. Для блоков надо будет так же рассчитать те же параметры, только с учетов всех ячеек входящих в нее.

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

То есть «на лету» считать параметры для ячеек, потом для блока, а потом для изделия?

А зачем по сто раз пересчитывать ячейку, если она входит в несколько блоков одного изделия? Не проще 1 раз при ее создании все для нее посчитать и забыть про нее?
Чем не оптимизация?

Trech
Сообщения: 3
Зарегистрирован: 29 апр 2009, 11:29

Re: оптимизация использования триггера

Сообщение Trech » 29 апр 2009, 21:42

Думаю, что стоит уточнить еще один момент: по той структуре, которую я привел действительно получается дикая вещь.
На самом деле структура таблиц элементов (да и структура таблицы ячейки) содержат больше полей. Причем для каждого элемента присуще свои индивидуальные характеристики, а к некоторым элементам цепляются дополнительные справочники. Это еще одна причина, почему структура так располнена. Единственное общее для каждых типов элементов - это ряд общих параметров, на которых и строятся вычисления.

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

Dimitry Sibiryakov
Заслуженный разработчик
Сообщения: 1436
Зарегистрирован: 15 сен 2005, 09:05

Re: оптимизация использования триггера

Сообщение Dimitry Sibiryakov » 30 апр 2009, 12:25

Это не "вычисляемое поле", это "хранимый агрегат" вокруг которого уже гора сломанных копий. В поиск.

Я у себя от них избавился, отчего исчезли и пара неприятных проблем вроде распухания БД.

Ответить