[firebase-br] Performance de store procedure no FB 2.1

[Arlei Ferreira Farnetani Junior]

Pessoal, me digam uma coisa, é normal
num pentium dual core de 2GB de RAM,
uma store procedure igual a abaixo
demorar 14 segundos pra abrir? Tem como melhorar isto,
utilizo o Firebird 2.1 (ultima versao)

To achando mto lento...

To executando ela assim:
select * from retorna_estoque_media_anual(1,2008)


Abaixo a analise:

Query Time:

 Prepare....15ms
 Execute 10,828 sec
 Avg Fetch Time 13,97 ms

Memory:

 Current: 18314540
 Max:     69798476
 Buffers: 2048

Operations

 Reads: 145176
 Writes: 0
 Fetches: 2740578

Table Name:   IR:  NIR:
RDB$FIELDS   35  0
RDB$RELATION_FIELDS  38  0
RDB$RELATIONS   2  0
RDB$FORMATS   1  0
RDB$PROCEDURES   0  104
RDB$PROCEDURE_PARAMETERS 18  0
CFOP_VINCULO   147.072  0
ORCAMENTOS   214.020  0
ORCAMENTOS_ITENS  254.577  0
PRODUTOS   0  24.071



Abaixo a Procedure:


CREATE PROCEDURE RETORNA_ESTOQUE_MEDIA_ANUAL(
  VIS_FILIAL INTEGER,
  VANO INTEGER)
RETURNS(
  CODIGO INTEGER,
  DESCRICAO VARCHAR(50) CHARACTER SET ISO8859_1,
  JAN NUMERIC(18, 3),
  FEV NUMERIC(18, 3),
  MAR NUMERIC(18, 3),
  ABR NUMERIC(18, 3),
  MAI NUMERIC(18, 3),
  JUN NUMERIC(18, 3),
  JUL NUMERIC(18, 3),
  AGO NUMERIC(18, 3),
  SETEMBRO NUMERIC(18, 3),
  OUT NUMERIC(18, 3),
  NOV NUMERIC(18, 3),
  DEZ NUMERIC(18, 3),
  TOTAL NUMERIC(18, 3),
  OPERACAO VARCHAR(1) CHARACTER SET ISO8859_1)
AS
begin
  TOTAL=0;

for

select
coalesce(sum(
case
  when extract(month from o.data) = 1 then (oi.qtde)
end),0) as qtde_jan,

coalesce(
sum(
case
  when extract(month from o.data) = 2 then (oi.qtde)
end),0) as qtde_fev,

coalesce(
sum(
case
  when extract(month from o.data) = 3 then (oi.qtde)
end),0) as qtde_mar,

coalesce(
sum(
case
  when extract(month from o.data) = 4 then (oi.qtde)
end),0) as qtde_abr,

coalesce(
sum(
case
  when extract(month from o.data) = 5 then (oi.qtde)
end),0) as qtde_mai,

coalesce(
sum(
case
  when extract(month from o.data) = 6 then (oi.qtde)
end),0) as qtde_jun,

coalesce(
sum(
case
  when extract(month from o.data) = 7 then (oi.qtde)
end),0) as qtde_jul,

coalesce(
sum(
case
  when extract(month from o.data) = 8 then (oi.qtde)
end),0) as qtde_ago,

coalesce(
sum(
case
  when extract(month from o.data) = 9 then (oi.qtde)
end),0) as qtde_set,

coalesce(
sum(
case
  when extract(month from o.data) = 10 then (oi.qtde)
end),0) as qtde_out,

coalesce(
sum(
case
  when extract(month from o.data) = 11 then (oi.qtde)
end),0) as qtde_nov,

coalesce(
sum(
case
  when extract(month from o.data) = 12 then (oi.qtde)
end),0) as qtde_dez,
sum(oi.qtde) as qtde,

prod.id_produto as codigo,
prod.descricao,
cv.tipo_operacao

from

produtos prod
left outer join
orcamentos_itens oi
on oi.codigo=prod.id_produto
left outer join
orcamentos o
on o.id_orcamento=oi.is_orcamento
left outer join
cfop_vinculo cv
on cv.id_cfopvinculo=o.is_cfopvinculo
where 1=1
and (extract(year from o.data)=:vano)
/* and cv.id_cfopvinculo=:vis_cfopvinculo */
and ((cv.tipo_operacao='S') or (cv.tipo_operacao='SI') or 
(cv.tipo_operacao='SG') or (cv.tipo_operacao='SA'))
and not o.status='C'
and o.is_filial=:vis_filial

group by prod.id_produto, prod.descricao, cv.tipo_operacao
order by cv.tipo_operacao
into: jan, :fev, :mar, :abr, :mai, :jun, :jul, :ago, :setembro, :out, :nov, 
:dez, :total,
:codigo, :descricao, :operacao
do

  suspend;
end