Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Оптимизация скрипта для тригера  [new]
medicina70
Member

Откуда:
Сообщений: 6
Приветствую!
Необходима помощь в оптимизации скрипта. Используется в тригере на insert на таблице MEDECINS.

Declare
  @ZARPLATA_ID int,
  @ZARPLATA_SERV_ID int,
  @ZARPLATA_MEDECINS_ID int,
  @COUNT_SERV_ID int

set @COUNT_SERV_ID = (select count(*) from FM_SERV where FM_SERV_ID not in 
		     (select Z.FM_SERV_ID from ZARPLATA Z where Z.MEDECINS_ID =(select i.MEDECINS_ID from inserted i)))
set @ZARPLATA_MEDECINS_ID = (select i.MEDECINS_ID from inserted i)
		
while @COUNT_SERV_ID <> 0
	begin
		set @ZARPLATA_ID = (select max(ZARPLATA_ID)+1 from ZARPLATA)
		set @ZARPLATA_SERV_ID = (select top 1 FM_SERV_ID from FM_SERV where FM_SERV_ID not in 
				        (select Z.FM_SERV_ID from ZARPLATA Z where Z.MEDECINS_ID =(select i.MEDECINS_ID from inserted i)))

		insert into ZARPLATA (ZARPLATA_ID,MEDECINS_ID,FM_SERV_ID) values (@ZARPLATA_ID,@ZARPLATA_MEDECINS_ID,@ZARPLATA_SERV_ID)
		set @COUNT_SERV_ID = @COUNT_SERV_ID - 1 
	end

ZARPLATA - таблица содержит записи связью (Врач-усулга-процент выручки)
FM_SERV - справочник услуг, прайс
MEDECINS - справочник врачей.

логика такая, этот скрипт я запихал в тригер на создание записи в табл MEDECINS. т.е. когда я создаю нового врача, нажимаю сохранить, запускается этот скрипт. он по задумке создает записи в табл ZARPLATA, туда подставляет id врача (MEDECINS_ID), id услуги (FM_SERV_ID). и далее цикл повторяется пока не закончатся усулги для которых не существует подобной связи с врачом.

получается прайс содержит 2500тыс усулг, строк в табл.
при создании врача, должно создатся 2500 строк в табл ZARPLATA для этого врача.

скрипт работает, но для создания 100 записей в таблицу ZARPLATA требуется >1мин. и я понимаю чем дальше, тем дольше он будет отрабатывать(((
Что можно подправить, написать правильнее чтобы не столь длительное время он отрабатывал?!
9 июл 14, 06:06    [16278791]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
aleks2
Guest
Ужос-ужос-ужос...

insert into ZARPLATA ( ZARPLATA_ID
                     , MEDECINS_ID
                     , FM_SERV_ID
                     ) 
   select row_count() over(order by f.FM_SERV_ID) + (select max(ZARPLATA_ID) from ZARPLATA)
        , i.MEDECINS_ID
        , f.FM_SERV_ID 
     from FM_SERV f cross join (select MEDECINS_ID from inserted) i
     where not exists( select * from ZARPLATA z where z.MEDECINS_ID = i.MEDECINS_ID and z.FM_SERV_ID = f.FM_SERV_ID)
9 июл 14, 06:30    [16278796]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
aleks2
Guest
Ну и... откройте для себя IDENTITY.
9 июл 14, 06:32    [16278800]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
medicina70
Member

Откуда:
Сообщений: 6
aleks2
insert into ZARPLATA ( ZARPLATA_ID
                     , MEDECINS_ID
                     , FM_SERV_ID
                     ) 
   select row_count() over(order by f.FM_SERV_ID) + (select max(ZARPLATA_ID) from ZARPLATA)
        , i.MEDECINS_ID
        , f.FM_SERV_ID 
     from FM_SERV f cross join (select MEDECINS_ID from inserted) i
     where not exists( select * from ZARPLATA z where z.MEDECINS_ID = i.MEDECINS_ID and z.FM_SERV_ID = f.FM_SERV_ID)


Incorrect syntax near the keyword 'where'. в строке where not exists( ..
9 июл 14, 07:17    [16278823]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
aleks2
Guest
medicina70
aleks2
insert into ZARPLATA ( ZARPLATA_ID
                     , MEDECINS_ID
                     , FM_SERV_ID
                     ) 
   select row_count() over(order by f.FM_SERV_ID) + (select max(ZARPLATA_ID) from ZARPLATA)
        , i.MEDECINS_ID
        , f.FM_SERV_ID 
     from FM_SERV f cross join (select MEDECINS_ID from inserted) i
     where not exists( select * from ZARPLATA z where z.MEDECINS_ID = i.MEDECINS_ID and z.FM_SERV_ID = f.FM_SERV_ID)


Incorrect syntax near the keyword 'where'. в строке where not exists( ..

Эээ, там тока одна ошибка row_count() надо заменить на row_number().

Версию SQL огласите.
9 июл 14, 07:31    [16278841]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
medicina70
Member

Откуда:
Сообщений: 6
aleks2,
Спасибо за подмогу, все работает.
9 июл 14, 07:58    [16278866]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Лучше уберите бизнес-логику из триггера в процедуру. Не мешайте другим работать :)
9 июл 14, 13:00    [16280549]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Владислав Колосов
Лучше уберите бизнес-логику из триггера в процедуру. Не мешайте другим работать :)
Т.е. другие обязательно должны будут работать через эту процедуру, иначе это не реализация Б-Л, а полная хрень. Учитывая, что триггер тоже по сути процедура, то в чем принципиальная разница?

ЗЫ: Триггеры не "зло", а обычный инструмент, которым нужно уметь пользоваться.
9 июл 14, 13:08    [16280614]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Да, процедуры по сути, но "плохие" с точки зрения затягивания транзакции. Я уж не говорю о развитой бизнес-логике, которую наблюдал в триггерах.
9 июл 14, 13:45    [16280909]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Владислав Колосов
Да, процедуры по сути, но "плохие" с точки зрения затягивания транзакции.

Что больше затянет транзакцию и почему?
- триггер;
- процедура, написанная для обработки одной строки, вызываемая в цикле;
- процедура, написанная для обработки множества строк.

Б-Л на то и Б-Л, чтобы выполняться всегда. Триггер защищает от случайных манипуляций с данными мимо Б-Л.
9 июл 14, 13:55    [16280992]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
invm,

"Триггер защищает!" Именно, а не сортирует, создаёт, удаляет и бегает в "Магнит". В то время, как остальные курят, пока он вернется с продуктами. Задержка даже в 100 мсек при вставке может быть критичной... Я уже проходил это. Триггер, наполненный сложной бизнес-логикой, работает медленно всегда, а вот процедура, которая производит вставку, обращается к таблице лишь на время вставки данных. Процедура может долго запрягать, но быстро ехать, триггер будет ехать медленно при любой скорости запрягания.
9 июл 14, 17:55    [16282809]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
Владислав Колосов
Триггер, наполненный сложной бизнес-логикой, работает медленно всегда, а вот процедура, которая производит вставку, обращается к таблице лишь на время вставки данных.

Ну да, процедура работает быстро, ибо б-л не наполнена. А кто же тогда б-л реализует? Сервер приложений?

Если у вас одни и те же действия в процедуре и триггере приводят к существенной деградации производительности последнего, значит вы просто не умеете их (триггеры) готовить.
9 июл 14, 18:29    [16282997]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация скрипта для тригера  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Поддерживаю тезис о том, что триггер это инструмент, которым нужно уметь пользоваться, как, собственно, и любым другим.

Только опыт использования у каждого свой. Затевать спор о том, что лучше – это, имхо, холивар – каждый будет убежден в своей правоте, потому, что свой опыт ближе к сердцу, чем любые доводы оппонента.

Лично мой опыт – использовать триггеры только в самых необходимых случаях, в целом стараться обойтись без них. Наверное, это
зависит от архитектуры системы, я имел дело только с системами, в которых нельзя было сделать что-то «мимо» Б-Л, если только ты не разработчик. ИМХО, если можно сделать «мимо», это следствие неудачного проектирования системы в целом (не только БД). Кончено, хорошо рассуждать о проектировании в теории, но на практике бывает всякое, так что, почему бы и не триггеры, если по-другому уже никак.

Защищаться же триггерами от разработчиков – бесполезно. Для такой защиты есть права доступа. Единственное, если только разработчик полез в БД, запустил некий скрипт, не подумав, а триггер дал отбой – тогда да. Хотя в большинстве с эти справляются констрейнты. Оправдывает ли наличие триггера защиту от случайных непреднамеренных действий разработчика по порче целостности БД – опыт каждого, конечно, но имхо, нет.

Есть хорошая книга Алекса Кузнецова Defensive Database Programming - доступна бесплатно в электронном виде у РедГейта, всем рекоммендую, там как раз рассматривается этот вопрос.

Лично я не люблю триггеры за:
  • неочевидность – иногда трудно понять что произошло, пока не вспомнишь про триггер, хотя если это обычная практика в системе, этой проблемы нет, наверное.
  • особенности обработки ошибок, особенно если задействована репликация.
  • их неправильно «готовят» - часто ты не один разработчик, а многие не в курсе best practices по их написанию, взять хотя бы многочисленные вопросы/скрипты (постояльцы форума помнят сколько их), где люди предполагают обработку по одной записи, и много чего еще
  • неочевидные вещи, связанные с их особенностями – никогда лично с таким не сталкивался (наверное, потому что у нас почти нет триггеров), а-ля проблемы при снэпшот изоляции, нагрузка на темпдб, сплиты страниц при селектах, может, что-то еще.

    Но возвращаясь к первой мысли, это просто инструмент, часто нужный и полезный.
    Если человек умеет им правильно и к месту пользоваться – то вперед.
  • 9 июл 14, 18:47    [16283073]     Ответить | Цитировать Сообщить модератору
     Re: Оптимизация скрипта для тригера  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9406
    SomewhereSomehow
    Защищаться же триггерами от разработчиков – бесполезно.
    Если есть требование "нельзя работать мимо Б-Л", рядовой разработчик не должен иметь возможность непреднамеренно обойти Б-Л, забыв вызвать нужную процедуру или вызвав ее не в том месте.
    SomewhereSomehow
    особенно если задействована репликация.
    Не могли бы пояснить, что имеется в виду?
    9 июл 14, 19:37    [16283224]     Ответить | Цитировать Сообщить модератору
     Re: Оптимизация скрипта для тригера  [new]
    gandjustas
    Member

    Откуда:
    Сообщений: 857
    Блог
    medicina70
    Приветствую!

    получается прайс содержит 2500тыс усулг, строк в табл.
    при создании врача, должно создатся 2500 строк в табл ZARPLATA для этого врача.


    Объясните пожалуйста нафига это делать?
    У вас в таблице ZARPLATA получается всегда декартово произведение таблиц врачей и услуг. И вам надо создать как минимум два триггера (один на врачей, другой на услуги), чтобы эту ситуацию поддерживать. Почему бы не сделать тоже самое "декартово произведение" в момент выборки? Тем более, я уверен, у вас обязательно будет фильтр или по врачу или по услуге.

    Даже если таблица ZARPLATA хранит разные проценты для разных услуг-врачей, то вы все равно не задаете эти параметры в триггере и у вас записывается дефолтное значение. Значит где-то есть интерфейс, который позволяет это значение поменять, вот и сделайте так чтоб этот интерфейс писал строки в таблицу ZARPLATA если процент отличается от того, что по-умолчанию.
    9 июл 14, 20:21    [16283324]     Ответить | Цитировать Сообщить модератору
     Re: Оптимизация скрипта для тригера  [new]
    SomewhereSomehow
    Member

    Откуда: Moscow
    Сообщений: 2480
    Блог
    invm
    Если есть требование "нельзя работать мимо Б-Л", рядовой разработчик не должен иметь возможность непреднамеренно обойти Б-Л, забыв вызвать нужную процедуру или вызвав ее не в том месте.

    Разработчик - разрабатывает эту самую бизнес логику. Не понимаю, зачем ему ее обходить? Зачем ему вызывать процедуры? (отладку не берем, это отдельная тема). Может быть у вас какие-то другие требования к разработчикам - что ж, это может быть.

    invm
    Не могли бы пояснить, что имеется в виду?

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

    В целом.
    Моя мысль в том, что главное, не устраивать зоопарк.

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

    Если ты сам руководитель, решаешь что и как использовать, но задаешься подобным вопросом - значит тебя преждевременно назначили руководителем. Это бывает сплошь и рядом, не повод же отказываться =) В таком случае, нужно изучить все плюсы и минусы - и принять решение! А когда ты уйдешь на другой проект/компанию и т.д. Твои последователи будут на форуме задавать такие вопросы =)
    9 июл 14, 20:31    [16283351]     Ответить | Цитировать Сообщить модератору
     Re: Оптимизация скрипта для тригера  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 47001
    SomewhereSomehow
    Разработчик - разрабатывает эту самую бизнес логику.
    Разработчики то и дело меняются.
    Всё достаётся новым разработчикам, зачастую без внятной документации и пояснений.
    Такова реальная жизнь!
    Немудрено, не разобравшись в чужом коде или неправильно его интерпретируя,
    не пользоваться обязательными процедурами или наоборот, использовать лишние.
    10 июл 14, 10:10    [16284890]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить