Планы процедур и производительность Планы процедур

У процедур, как таковых, нет "планов" выполнения, аналогичных планам запросов. То есть, процедуры, это всего-лишь процедурный код (PSQL), который в том числе позволяет выполнять и запросы. Однако, если мы выполним запрос с процедурой (как select, так и execute), первоначально выполнится операция prepare, по которой сервер выдаст "план запроса" (многие интерактивные инструменты позволяют просматривать такие планы).

Например, если мы выполним запрос к процедуре TEST (первая в разделе Селективные процедуры)

select * from test

мы получим план

PLAN (EMPLOYEE NATURAL)

но этот план на самом деле принадлежит запросу

select last_name, first_name from employee

который находится внутри процедуры.

Если же в нашей процедуре есть несколько запросов, например это процедура DEPT_BUDGET из БД примеров employee, то при выполнении запроса

select * from DEPT_BUDGET('aaa')

мы получим план

PLAN (DEPARTMENT INDEX (RDB$PRIMARY5))(DEPARTMENT INDEX (RDB$FOREIGN6))(DEPARTMENT INDEX (RDB$FOREIGN6))

неприятность в том, что здесь три отдельных плана склеились в один - запросов в процедуре три: вывод "суммарного" плана процедуры может содержать и отдельные строки PLAN для каждого запроса

1. SELECT budget FROM department WHERE dept_no = :dno

PLAN (DEPARTMENT INDEX (RDB$PRIMARY5))

2. SELECT count(budget) FROM department WHERE head_dept = :dno

PLAN (DEPARTMENT INDEX (RDB$FOREIGN6))

3. SELECT dept_no FROM department WHERE head_dept = :dno

PLAN (DEPARTMENT INDEX (RDB$FOREIGN6))

кроме того, в процедуре есть условие

IF (cnt = 0) THEN SUSPEND;

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

Поэтому, проверять производительности запросов, входящих в процедуру, нужно отдельно, т.е. выполняя только эти запросы.

Производительность

Из изложенного выше (в Планы процедур) понятно, что на "производительность процедур" влияют только запросы, которые присутствуют в этих процедурах (при этом мы оставим вопросы производительности циклов, обработки ошибок в процедурах и т.д.).

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

Дело в том, что когда мы выполняем запрос, в программе или инструменте разработчика, чаще всего происходит так:

• выполняется запрос

• сервер передает порцию данных

• клиент принимает порцию данных от сервера, выводит в "грид", и на этом этапе у сервера больше ничего не просит

• самый типичный пример такого запроса - select * from table - где в таблице несколько миллионов записей. Такой запрос "выполнится" практически мгновенно, и мы увидим первую порцию данных, которая попала к нам на экран.

Но если мы "в гриде" нажмем Ctrl-End, т.е. дадим команду считать все записи с сервера, то дальнейшее будет происходить уже не так быстро.

Процедуры же, редко пишутся с той целью, чтобы просто через себя протаскивать данные из таблиц, без изменений. Обычно это какая-то обработка, и даже если запрос не содержит group by, order by или других "замедляющих" выполнение запроса операций, то в процедуре будут перебраны все записи, которые выдал бы запрос. То есть, это примерно эквивалентно упомянутому выше нажатию Ctrl-End.

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

create or alter procedure SUM_SALARY

returns (s numeric(15,2))

as

declare variable I numeric(15,2);

begin

s=0; i=0;

for select salary from employee

into :i

do

begin

s=:s + :i;

end

suspend;

end

В процедуре перебираются все записи таблицы employee, и на выход передается только конечный результат - "одна запись" в виде суммы всех зарплат.

А это значит, что для оценки производительности запроса, включенного в процедуру таким образом, при тестировании запроса нам нужно было бы не просто выполнить этот запрос, а выполнить его с операцией FetchAll (выбрать все записи). И тогда счетчики производительности инструмента (или получаемые от сервера) выдадут уже совершенно другой результат.

Собственно, производительность запросов вне процедур или в процедурах (или в триггерах) ничем не отличается, т.к. во всех случаях для одного запроса выполняется один и тот же скомпилированный BLR-код (binary language representation).