[firebase-br] set plan???

Arlei Ferreira Farnetani Junior junior em farsoft.com.br
Sáb Ago 16 10:17:35 -03 2008


Na verdade é uma store procedure de apuração de
itens vendidos totalizando mes a mes no ano todo para
apuração da média de necessidade de compra (estoque mínimo).

Nesta store procedure eu posso especificar um tipo (MATERIAL ou MEDICAMENTO)
e ainda especificar quais são realmente os meses relevantes: de jan a dez ou 
de março até julho
por exemplo...

Segue abaixo os links do gráfico de desempenho...to achando mto demorado 
15segundos...
pois não são tantos registros assim.

www.farsoft.com.br/firebird/tela01.jpg
www.farsoft.com.br/firebird/tela02.jpg


select * from retorna_estoque_media_anual(1,2008,'MATERIAL',5,12);


Olha o plano utilizado pelo FB 2.1

Plan:
------------------------------------------------
PLAN SORT (SORT (JOIN (CV INDEX (CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3, 
CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3), O INDEX (FK_ORCAMENTOS_7), OI INDEX 
(FK_ORCAMENTOS_ITENS_1), PROD INDEX (PK_PRODUTOS), GP INDEX 
(PK_GRUPOS_PRODUTOS), M INDEX (PK_MARCAS))))SORT (SORT (JOIN (CV INDEX 
(CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3, 
CFOP_VINCULO_IDX3), O INDEX (FK_ORCAMENTOS_7), OI INDEX 
(FK_ORCAMENTOS_ITENS_1), PROD INDEX (PK_PRODUTOS), GP INDEX 
(PK_GRUPOS_PRODUTOS), M INDEX (PK_MARCAS))))


Segue abaixo a store procedure

CREATE OR ALTER PROCEDURE RETORNA_ESTOQUE_MEDIA_ANUAL (
    vis_filial integer,
    vano integer,
    vtipo varchar(11),
    vmesInicial integer,
    vmesFinal integer)
returns (
    codigo integer,
    descricao varchar(50),
    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),
    tipo varchar(11),
    marca varchar(10),
    grupo varchar(30))
as
declare variable tipoTratado varchar(11);
declare variable queryDinamica varchar(100);
begin
  tipoTratado='';

  if (:vtipo='MEDICAMENTO') then
     tipoTratado='M';
  if (:vtipo='PRODUTO') then
     tipoTratado='P';
  if (:vtipo='MATERIAL') then
     tipoTratado='H';
  if (:vtipo='SERVICOS') then
     tipoTratado='S';
  if (:vtipo='TERCEIROS') then
     tipoTratado='T';

  TOTAL=0;

  if (tipoTratado='') then
  begin

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,
case
  when prod.tipo='M' then 'MEDICAMENTO'
  when prod.tipo='P' then 'PRODUTO'
  when prod.tipo='H' then 'MATERIAL'
  when prod.tipo='S' then 'SERVICOS'
  when prod.tipo='T' then 'TERCEIROS'
  else 'OUTROS'
end as tipo,
m.marca,
gp.grupoproduto



from

orcamentos o ,cfop_vinculo cv, produtos prod, orcamentos_itens oi, marcas m, 
grupos_produtos gp
where
cv.id_cfopvinculo=o.is_cfopvinculo
and (extract(year from o.data)=:vano)
and (extract(month from o.data)>=:vmesinicial)
and (extract(month from o.data)<=:vmesfinal)
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
and o.id_orcamento=oi.is_orcamento
and oi.codigo=prod.id_produto
and m.id_marca=prod.is_marca
and gp.id_grupoproduto=prod.is_grupoproduto
and prod.ativo='S'


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

  suspend;
  end
else
  begin
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,
case
  when prod.tipo='M' then 'MEDICAMENTO'
  when prod.tipo='P' then 'PRODUTO'
  when prod.tipo='H' then 'MATERIAL'
  when prod.tipo='S' then 'SERVICOS'
  when prod.tipo='T' then 'TERCEIROS'
  else 'OUTROS'
end as tipo,
m.marca,
gp.grupoproduto

from

orcamentos o ,cfop_vinculo cv, produtos prod, orcamentos_itens oi, marcas m, 
grupos_produtos gp
where
cv.id_cfopvinculo=o.is_cfopvinculo
and (extract(year from o.data)=:vano)
and (extract(month from o.data)>=:vmesinicial)
and (extract(month from o.data)<=:vmesfinal)
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
and prod.tipo=:tipotratado
and o.id_orcamento=oi.is_orcamento
and oi.codigo=prod.id_produto
and m.id_marca=prod.is_marca
and gp.id_grupoproduto=prod.is_grupoproduto
and prod.ativo='S'


group by prod.id_produto, prod.descricao, cv.tipo_operacao, prod.tipo, 
m.marca, gp.grupoproduto
order by prod.DESCRICAO

into: jan, :fev, :mar, :abr, :mai, :jun, :jul, :ago, :setembro, :out, :nov, 
:dez, :total,
:codigo, :descricao, :operacao, :tipo, :marca, :grupo
do

  suspend;
  end
end




Query Time
------------------------------------------------
Prepare       : 62 ms
Execute       : 15,078 sec
Avg fetch time: 59,60 ms

Memory
------------------------------------------------
Current: 18337000
Max    : 45720536
Buffers: 2048

Operations
------------------------------------------------
Reads   : 1966
Writes : 7
Fetches: 793355

Plan:
------------------------------------------------
PLAN SORT (SORT (JOIN (CV INDEX (CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3, 
CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3), O INDEX (FK_ORCAMENTOS_7), OI INDEX 
(FK_ORCAMENTOS_ITENS_1), PROD INDEX (PK_PRODUTOS), GP INDEX 
(PK_GRUPOS_PRODUTOS), M INDEX (PK_MARCAS))))SORT (SORT (JOIN (CV INDEX 
(CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3, CFOP_VINCULO_IDX3, 
CFOP_VINCULO_IDX3), O INDEX (FK_ORCAMENTOS_7), OI INDEX 
(FK_ORCAMENTOS_ITENS_1), PROD INDEX (PK_PRODUTOS), GP INDEX 
(PK_GRUPOS_PRODUTOS), M INDEX (PK_MARCAS))))

Enchanced Info:
+--------------------------+-------+-----------+---------+---------+----------+
|        Table Name        | Index | Non-Index | Updated | Deleted | 
Inserted |
|                          | reads |   reads   |         |         | 
|
+--------------------------+-------+-----------+---------+---------+----------+
|                RDB$FIELDS|    38 |         0 |       0 |       0 | 
0 |
|       RDB$RELATION_FIELDS|    38 |         0 |       0 |       0 | 
0 |
|             RDB$RELATIONS|     2 |         0 |       0 |       0 | 
0 |
|               RDB$FORMATS|     1 |         0 |       0 |       0 | 
0 |
|            RDB$PROCEDURES|     0 |       208 |       0 |       0 | 
0 |
|  RDB$PROCEDURE_PARAMETERS|    24 |         0 |       0 |       0 | 
0 |
|              CFOP_VINCULO|     5 |         0 |       0 |       0 | 
0 |
|                ORCAMENTOS| 9.084 |         0 |       0 |       0 | 
0 |
|          ORCAMENTOS_ITENS| 62.60 |         0 |       0 |       0 | 
0 |
|                  PRODUTOS| 62.58 |         0 |       0 |       0 | 
0 |
|                    MARCAS| 39.50 |         0 |       0 |       0 | 
0 |
|           GRUPOS_PRODUTOS| 41.53 |         0 |       0 |       0 | 
0 |
+--------------------------+-------+-----------+---------+---------+----------+




----- Original Message ----- 
From: "Douglas Tosi" <douglasht em gmail.com>
To: "FireBase" <lista em firebase.com.br>
Sent: Friday, August 15, 2008 11:16 PM
Subject: Re: [firebase-br] set plan???


On Fri, Aug 15, 2008 at 10:59 PM, Arlei Ferreira Farnetani Junior
<junior em farsoft.com.br> wrote:
> Pessoal, como fazer o plano abaixo em SQL na minha
> consulta???

Geralmente antes de sair mexendo no plano é melhor mexer na consulta.
Assim o otimizador do Firebird (que *quase* sempre é mais esperto que
a gente) pode fazer o trabalho dele e achar um plano melhor.

Mande seu SQL pra gente, junto com o plano gerado pelo Firebird e a
descrição dos índices envolvidos.

[]s
-- 
Douglas Tosi
www.sinatica.com

______________________________________________
FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
Para saber como gerenciar/excluir seu cadastro na lista, use: 
http://www.firebase.com.br/fb/artigo.php?id=1107
Para consultar mensagens antigas: http://firebase.com.br/pesquisa 





Mais detalhes sobre a lista de discussão lista