Ручная оптимизация в действии

Программирование‎ > ‎Interbase/Firebird‎ > ‎

(Маленькая поучительная история)

Довольно часто SQL запрос, вывернутый "наизнанку" позволяет повысить быстродействие на 2-3 порядка (т.е. в тысячу раз!)

Понадобилось мне как-то добавить к таблице новое поле и заполнить его суммами по другому полю. Все бы ничего, да только в таблицах 1.5 млн. записей. К счастью, я начал эксперименты на таблицах с 1000 записей и вовремя обнаружил засаду.

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


update ApplyJournal a1
set saled=(select sum(Amount) from ApplyJournal a2
  where a1.Doc_num=a2.Orig_doc and a1.Doc_Npp=a2.Orig_Doc_npp
    and a1.WareNum=a2.WareNum)
where a1.Doc_type=1

На таблице с 1000 записей этот запрос выполнялся несколько минут . Вскрытие показало, что внутренний select выполняется с планом NATURAL, хотя по (a2.Orig_doc, a2.Orig_Doc_npp) существует индекс. Заставить этот индекс работать мне не удалось. После некоторых размышлений к условиям внутреннего запроса было добавлено and a2.Doc_type=-1, для которого тоже существовал индекс. И этот индекс заработал! (Похоже, сервер принципиально не берет индекс для полей, задействованных в корреляции внешней и внутренней таблиц.) Ситуацию это несколько улучшило, но не сильно: тест на большой таблице показал, что поиск одного значения занимает 4.5 секунды! Помноженные на миллион записей это получается... гм, гм...

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


CREATE PROCEDURE TMP AS
declare variable ID integer;
declare variable OD_ integer;
declare variable ON_ integer;
declare variable WN integer;
declare variable SS double precision;
begin
  for select ID, Doc_num, Doc_Npp, WareNum from ApplyJournal where Doc_type=1
    into :ID, :OD_, :ON_, :WN do begin
      select sum(Amount) from ApplyJournal
      where Orig_doc=:OD_ and Orig_Doc_npp=:ON_
        and WareNum=:WN and Doc_type=-1
        PLAN (ApplyJournal INDEX (AJ_ORIGDOC)) into :SS;
      if (:SS is null) then SS=0;
      update ApplyJournal set Orig_saled=:SS where ID=:ID;
    end
end

На маленькой таблице этот запрос отработал за 9 секунд . Сделав копию большой таблицы, я запустил на ней эту SP для пробы, прождал полчаса и ушел спать. Ночью мне пришла в голову другая умная мысль: миллион обновлений делать все равно придется, а вот миллиона селектов можно избежать, заменив их одним, с группировкой (который, по опыту, выполняется не намного дольше одного запроса с суммированием по всем записям). В результате получилась SP с логикой, обратной исходной:


CREATE PROCEDURE TMP AS
declare variable OD_ integer;
declare variable ON_ integer;
declare variable WN integer;
declare variable SS double precision;
begin
  for select Orig_doc, Orig_Doc_npp, WareNum, sum(Amount) from ApplyJournal
    where Doc_type=-1 group by Orig_doc, Orig_Doc_npp, WareNum
    into :OD_, :ON_, :WN, :SS do
      update ApplyJournal set Orig_saled=:SS
      where Doc_type=1 and Doc_num=:OD_ and Doc_Npp=:ON_ and WareNum+0=:WN
        and (Orig_saled is null or Orig_saled<>:SS);
end

Полностью уверенный в успехе, я сразу пустил ее на большой таблице и получил результат уже через 2 минуты!

Успехов!

Comments