Fórum Melhorar Performance da SP #514041
18/03/2015
0
Galera tenho essa sp que me traz os produtos faturados, só que a performance não está legal, tem como melhorar? Demora muito! (Firebird 2.5)
CREATE PROCEDURE SP_TESTE_FATURAMENTO (
dt_ini date,
dt_fim date,
loja integer,
opcao integer)
returns (
prod_id integer,
prod_descricao varchar(120),
grupo varchar(60),
fornecedor_titulo varchar(130),
for_id integer,
fornecedor varchar(120),
prod_und_trib varchar(6),
qtde_caixa numeric(9,2),
prod_valor_compra numeric(18,2),
preco_medio numeric(18,2),
qtde_vendida numeric(10,2),
qtde_bonificacao numeric(10,2),
qtde_avaria numeric(10,2),
qtde_vencidos numeric(10,2),
qtde_total numeric(10,2),
custo numeric(18,2),
venda_bonificada numeric(18,2),
perc_bonificao numeric(5,2),
venda_avaria numeric(18,2),
perc_avaria numeric(5,2),
venda_total numeric(18,2),
venda_bruta numeric(18,2),
venda_liquida numeric(18,2),
perc_venda numeric(5,2),
desconto numeric(18,2),
perc_desconto numeric(5,2),
lucro_operacional numeric(18,2),
perc_lucro numeric(5,2))
as
BEGIN
qtde_caixa = 0;
prod_valor_compra = 0;
preco_medio = 0;
qtde_vendida = 0;
qtde_bonificacao = 0;
qtde_avaria = 0;
qtde_vencidos = 0;
qtde_total = 0;
custo = 0;
venda_bonificada = 0;
perc_bonificao = 0;
venda_avaria = 0;
venda_total = 0;
venda_bruta = 0;
venda_liquida = 0;
perc_venda = 0;
desconto = 0;
perc_desconto = 0;
lucro_operacional = 0;
perc_lucro = 0;
if (opcao = 1) then
begin
FOR
select distinct
produtos.prod_id,
produtos.prod_descricao,
grupo_prod.grupo,
produtos.for_id || ' - ' || substring(fornecedor.for_nome from 1 for 110) as fornecedor_titulo,
produtos.for_id,
substring(fornecedor.for_nome from 1 for 120) as fornecedor,
produtos.prod_und_trib,
coalesce(produtos.qtde_caixa,0) as qtde_caixa,
produtos.prod_valor_compra
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
inner join produtos on (pedidos_itens.prod_id = produtos.prod_id)
inner join grupo_prod on (produtos.gru_id = grupo_prod.gru_id)
inner join fornecedor on (produtos.for_id = fornecedor.for_id)
where
pedidos.status = 1 and pedidos.loj_id =:loja and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
order by fornecedor, grupo_prod.grupo, produtos.prod_descricao
INTO :PROD_ID,
:PROD_DESCRICAO,
:GRUPO,
:fornecedor_titulo,
:FOR_ID,
:FORNECEDOR,
:PROD_UND_TRIB,
:QTDE_CAIXA,
:PROD_VALOR_COMPRA
DO
FOR
select
coalesce(avg(pedidos_itens.pi_preco),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id < 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id = :loja and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :preco_medio
DO
FOR
select
coalesce(sum(pedidos_itens.pi_qtde),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id < 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id = :loja and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :qtde_vendida
DO
FOR
select
coalesce(sum(pedidos_itens.pi_qtde),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id = 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id = :loja and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :qtde_bonificacao
DO
FOR
select
coalesce(sum(pedidos_itens.pi_qtde),0) +:qtde_bonificacao
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id < 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id = :loja and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :Qtde_Total
DO
FOR
select
coalesce(:prod_valor_compra * sum(pedidos_itens.pi_qtde),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where
pedidos.status = 1 and pedidos_itens.prod_id = :prod_id and pedidos.tipo_id <> 6 and pedidos.loj_id = :loja and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :Custo
DO
FOR
select
coalesce(:prod_valor_compra * sum(it.pi_qtde),0) from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id = 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :VENDA_BONIFICADA
DO
FOR
select
coalesce(case
when :venda_bonificada = 0 then 0
when coalesce(sum(it.pi_total),0) > 0 then
coalesce( (:venda_bonificada / (:venda_bonificada + (sum(it.pi_total) + sum(it.pi_desconto)) ) )*100,0)
else
100
end,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and it.prod_id = :prod_id and pedidos.tipo_id < 5 and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_bonificao
DO
FOR
select coalesce((select
coalesce(:prod_valor_compra * sum(it.pi_qtde),0) from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id = 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim) +
coalesce((sum(it.pi_total) + sum(it.pi_desconto)),0),0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :venda_total
DO
FOR
select coalesce((sum(it.pi_total) + sum(it.pi_desconto)),0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Venda_bruta
DO
FOR
select coalesce(sum(it.pi_total) ,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Venda_liquida
DO
FOR
select
coalesce((sum(it.pi_total)
/ (:venda_bonificada + (sum(it.pi_total) + sum(it.pi_desconto)))) *100,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and it.prod_id = :prod_id and pedidos.tipo_id < 5 and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_venda
DO
FOR
select coalesce(sum(it.pi_desconto) ,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Desconto
DO
FOR
select coalesce((sum(it.pi_desconto) / :venda_total) *100,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_desconto
DO
FOR
select coalesce((:venda_bonificada+(coalesce(sum(it.pi_total),0))),0) -:custo
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Lucro_operacional
DO
FOR
select
case
when :custo > 0 then
(((:venda_bonificada+(coalesce(sum(it.pi_total),0))) - :custo) /
(:venda_bonificada+(coalesce(sum(it.pi_total),0)))) * 100
else
0
end
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id = :loja and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_lucro
DO
BEGIN
SUSPEND;
END
end
/* geral ------------------------------------------------------------------------------------------------*/
if (opcao = 0) then
begin
FOR
select distinct
produtos.prod_id,
produtos.prod_descricao,
grupo_prod.grupo,
produtos.for_id || ' - ' || substring(fornecedor.for_nome from 1 for 110) as fornecedor_titulo,
produtos.for_id,
substring(fornecedor.for_nome from 1 for 120) as fornecedor,
produtos.prod_und_trib,
coalesce(produtos.qtde_caixa,0) as qtde_caixa,
produtos.prod_valor_compra
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
inner join produtos on (pedidos_itens.prod_id = produtos.prod_id)
inner join grupo_prod on (produtos.gru_id = grupo_prod.gru_id)
inner join fornecedor on (produtos.for_id = fornecedor.for_id)
where
pedidos.status = 1 and pedidos.loj_id in(1,2) and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
order by fornecedor, grupo_prod.grupo, produtos.prod_descricao
INTO :PROD_ID,
:PROD_DESCRICAO,
:GRUPO,
:fornecedor_titulo,
:FOR_ID,
:FORNECEDOR,
:PROD_UND_TRIB,
:QTDE_CAIXA,
:PROD_VALOR_COMPRA
DO
FOR
select
coalesce(avg(pedidos_itens.pi_preco),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id < 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id in(1,2) and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :preco_medio
DO
FOR
select
coalesce(sum(pedidos_itens.pi_qtde),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id < 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id in(1,2) and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :qtde_vendida
DO
FOR
select
coalesce(sum(pedidos_itens.pi_qtde),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id = 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id in(1,2) and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :qtde_bonificacao
DO
FOR
select
coalesce(sum(pedidos_itens.pi_qtde),0) +:qtde_bonificacao
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id < 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id in(1,2) and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :Qtde_Total
DO
FOR
select
coalesce(:prod_valor_compra * sum(pedidos_itens.pi_qtde),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where
pedidos.status = 1 and pedidos_itens.prod_id = :prod_id and pedidos.tipo_id <> 6 and pedidos.loj_id in(1,2) and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :Custo
DO
FOR
select
coalesce(:prod_valor_compra * sum(it.pi_qtde),0) from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id = 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :VENDA_BONIFICADA
DO
FOR
select
coalesce(case
when :venda_bonificada = 0 then 0
when coalesce(sum(it.pi_total),0) > 0 then
coalesce( (:venda_bonificada / (:venda_bonificada + (sum(it.pi_total) + sum(it.pi_desconto)) ) )*100,0)
else
100
end,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and it.prod_id = :prod_id and pedidos.tipo_id < 5 and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_bonificao
DO
FOR
select coalesce((select
coalesce(:prod_valor_compra * sum(it.pi_qtde),0) from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id = 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim) +
coalesce((sum(it.pi_total) + sum(it.pi_desconto)),0),0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :venda_total
DO
FOR
select coalesce((sum(it.pi_total) + sum(it.pi_desconto)),0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Venda_bruta
DO
FOR
select coalesce(sum(it.pi_total) ,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Venda_liquida
DO
FOR
select
coalesce((sum(it.pi_total)
/ (:venda_bonificada + (sum(it.pi_total) + sum(it.pi_desconto)))) *100,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and it.prod_id = :prod_id and pedidos.tipo_id < 5 and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_venda
DO
FOR
select coalesce(sum(it.pi_desconto) ,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Desconto
DO
FOR
select coalesce((sum(it.pi_desconto) / :venda_total) *100,0)
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_desconto
DO
FOR
select coalesce((:venda_bonificada+(coalesce(sum(it.pi_total),0))),0) -:custo
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :Lucro_operacional
DO
FOR
select
case
when :custo > 0 then
(((:venda_bonificada+(coalesce(sum(it.pi_total),0))) - :custo) /
(:venda_bonificada+(coalesce(sum(it.pi_total),0)))) * 100
else
0
end
from pedidos
inner join pedidos_itens it on (pedidos.ped_id = it.ped_id)
where
pedidos.status = 1 and pedidos.tipo_id < 5 and it.prod_id = :prod_id and pedidos.loj_id in(1,2) and
it.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
into :perc_lucro
DO
BEGIN
SUSPEND;
END
end
END
Sidney Abreu
Curtir tópico
+ 0
Responder
Posts
18/03/2015
Thiago Santana
Sidney, irei analisar a sua procedure e te dou um retorno!
Mas uma dica que sempre ouço é que quando suas consultas tem DISTINCT pelo meio, pode ter certeza que pode melhorar!
Mas uma dica que sempre ouço é que quando suas consultas tem DISTINCT pelo meio, pode ter certeza que pode melhorar!
Responder
Gostei + 0
18/03/2015
Marcos P
Sidney,
Apenas como sugestão inicial, eu começaria fatorando o código no sentido de torná-lo menor e mais legível...
Se você reparar a diferença da ( opcao = 1 ) x ( opcao = 0 ), é que na primeira você trabalha sempre com todas as lojas ( pedidos.loj_id =:loja ) e na segunda, apenas com as lojas 1 e 2 ( pedidos.loj_id in(1,2) ).
Essa lógica está correta ?
Se estiver, tente aplicar o conceito de query dinâmica que conversamos no outro post... gerando tudo dinamicamente, através da concatenação dos parâmetros de acordo com as particularidades de cada tipo de opção.
Senão estiver, unifique ou revise essa estrutura condicional.
Depois disso ( ou além disso ), as questões de tunning de performance devem seguir os padrões mais comuns... procure responder as seguintes perguntas :
1. Estatísticas no banco de dados estão ativadas para mensurar os custos de execução das queries nesse servidor ?
2. Qual o custo de execução da query completa ?
3. Qual o custo de execução parcial da query ?
4. É possível identificar os pontos de gargalo de perfomance ?
5. É possível melhorar a lógica das consultas nos gargalos identificados ?
6. Qual o nível de isolamento utilizado ?
7. A alteração para níveis de isolamento mais permissíveis, altera a performance ?
8. Qual o impacto de uma eventual alteração do nível de isolamento na integridade da operação ?
9. Qual a estrutura de índices suporta essas consultas ?
10. Cabe ajustes na estrutura de índices para melhoria de performance ?
Sempre cabem ajustes, principalmente se o tunning de performance nunca foi efetuado no ambiente.
Abraços,
Apenas como sugestão inicial, eu começaria fatorando o código no sentido de torná-lo menor e mais legível...
Se você reparar a diferença da ( opcao = 1 ) x ( opcao = 0 ), é que na primeira você trabalha sempre com todas as lojas ( pedidos.loj_id =:loja ) e na segunda, apenas com as lojas 1 e 2 ( pedidos.loj_id in(1,2) ).
Essa lógica está correta ?
Se estiver, tente aplicar o conceito de query dinâmica que conversamos no outro post... gerando tudo dinamicamente, através da concatenação dos parâmetros de acordo com as particularidades de cada tipo de opção.
Senão estiver, unifique ou revise essa estrutura condicional.
Depois disso ( ou além disso ), as questões de tunning de performance devem seguir os padrões mais comuns... procure responder as seguintes perguntas :
1. Estatísticas no banco de dados estão ativadas para mensurar os custos de execução das queries nesse servidor ?
2. Qual o custo de execução da query completa ?
3. Qual o custo de execução parcial da query ?
4. É possível identificar os pontos de gargalo de perfomance ?
5. É possível melhorar a lógica das consultas nos gargalos identificados ?
6. Qual o nível de isolamento utilizado ?
7. A alteração para níveis de isolamento mais permissíveis, altera a performance ?
8. Qual o impacto de uma eventual alteração do nível de isolamento na integridade da operação ?
9. Qual a estrutura de índices suporta essas consultas ?
10. Cabe ajustes na estrutura de índices para melhoria de performance ?
Sempre cabem ajustes, principalmente se o tunning de performance nunca foi efetuado no ambiente.
Abraços,
Responder
Gostei + 0
18/03/2015
Marcos P
A dica do Thiago também é válida... DISTINCT sempre gera custo a mais no banco !
Responder
Gostei + 0
18/03/2015
Sidney Abreu
show de bola, agradeço sua ajuda
Responder
Gostei + 0
18/03/2015
Sidney Abreu
Marcos, mais uma vez obrigado pela força.
Bem, a lógica é essa mesma. Não consigui adaptar a query dinâmica nesse código.
Sobre as perguntas, quando executo ele me da o seguinte relatório:
------ Performance info ------
Prepare time = 31ms
Execute time = 5s 507ms
Avg fetch time = 1.101,40 ms
Current memory = 10.983.916
Max memory = 13.417.108
Memory buffers = 2.048
Reads from disk to cache = 149
Writes from cache to disk = 0
Fetches from cache = 37.754.614
Mas refiz o código e o relatório ficou assim:
------ Performance info ------
Prepare time = 0ms
Execute time = 124ms
Avg fetch time = 5,17 ms
Current memory = 11.994.860
Max memory = 13.417.108
Memory buffers = 2.048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 767.048
Uma melhora considerável, mas se poderem me ajudar a deixa o código mais enxuto e legível, agradeço.
Sobre as perugntas, são interessantes. Como havia dito, sou iniciante, e tem coisa que não tenho certeza em responder, mas vou tentar.
1. Estatísticas no banco de dados estão ativadas para mensurar os custos de execução das queries nesse servidor ?
R - Nâo sei disser, não sei onde ativa. Mas no IBExpert ele me passa aquele relatório.
2. Qual o custo de execução da query completa ?
R - Passei aquele relatório
3. Qual o custo de execução parcial da query ?
R - Passei aquele relatório
4. É possível identificar os pontos de gargalo de perfomance ?
R - Não sei informar, inclusive não entendi até hj o que é o PLAN e PLAN Adapter que ele mostra no relatório.
5. É possível melhorar a lógica das consultas nos gargalos identificados ?
R - Acredito que sim
6. Qual o nível de isolamento utilizado ?
R - Não sei informar. O que seria esse nível de isolamento?
7. A alteração para níveis de isolamento mais permissíveis, altera a performance ?
R - Não sei informar.
8. Qual o impacto de uma eventual alteração do nível de isolamento na integridade da operação ?
R - Não sei informar.
9. Qual a estrutura de índices suporta essas consultas ?
R - Acredito que seja os índices criados. São esses:
10. Cabe ajustes na estrutura de índices para melhoria de performance ?
R - Acredito que sim
Bem, a lógica é essa mesma. Não consigui adaptar a query dinâmica nesse código.
Sobre as perguntas, quando executo ele me da o seguinte relatório:
------ Performance info ------
Prepare time = 31ms
Execute time = 5s 507ms
Avg fetch time = 1.101,40 ms
Current memory = 10.983.916
Max memory = 13.417.108
Memory buffers = 2.048
Reads from disk to cache = 149
Writes from cache to disk = 0
Fetches from cache = 37.754.614
Mas refiz o código e o relatório ficou assim:
------ Performance info ------
Prepare time = 0ms
Execute time = 124ms
Avg fetch time = 5,17 ms
Current memory = 11.994.860
Max memory = 13.417.108
Memory buffers = 2.048
Reads from disk to cache = 0
Writes from cache to disk = 0
Fetches from cache = 767.048
Uma melhora considerável, mas se poderem me ajudar a deixa o código mais enxuto e legível, agradeço.
CREATE OR ALTER PROCEDURE SP_TESTE (
dt_ini date,
dt_fim date,
loja integer,
opcao integer)
returns (
prod_id integer,
prod_descricao varchar(120),
grupo varchar(60),
fornecedor_titulo varchar(130),
for_id integer,
fornecedor varchar(120),
prod_und_trib varchar(6),
qtde_caixa numeric(9,2),
prod_valor_compra numeric(18,2),
preco_medio numeric(18,2),
qtde_vendida numeric(10,2),
qtde_bonificacao numeric(10,2),
qtde_avaria numeric(10,2),
qtde_vencidos numeric(10,2),
qtde_total numeric(10,2),
custo numeric(18,2),
venda_bonificada numeric(18,2),
perc_bonificao numeric(5,2),
venda_avaria numeric(18,2),
perc_avaria numeric(5,2),
venda_total numeric(18,2),
venda_bruta numeric(18,2),
venda_liquida numeric(18,2),
perc_venda numeric(5,2),
desconto numeric(18,2),
perc_desconto numeric(5,2),
lucro_operacional numeric(18,2),
perc_lucro numeric(5,2))
as
BEGIN
qtde_caixa = 0;
prod_valor_compra = 0;
preco_medio = 0;
qtde_vendida = 0;
qtde_bonificacao = 0;
qtde_avaria = 0;
qtde_vencidos = 0;
qtde_total = 0;
custo = 0;
venda_bonificada = 0;
perc_bonificao = 0;
venda_avaria = 0;
venda_total = 0;
venda_bruta = 0;
venda_liquida = 0;
perc_venda = 0;
desconto = 0;
perc_desconto = 0;
lucro_operacional = 0;
perc_lucro = 0;
if (opcao = 1) then
begin
FOR
select distinct
produtos.prod_id,
produtos.prod_descricao,
grupo_prod.grupo,
produtos.for_id || ' - ' || substring(fornecedor.for_nome from 1 for 110) as fornecedor_titulo,
produtos.for_id,
substring(fornecedor.for_nome from 1 for 120) as fornecedor,
produtos.prod_und_trib,
coalesce(produtos.qtde_caixa,0) as qtde_caixa,
produtos.prod_valor_compra
from produtos
inner join grupo_prod on (produtos.gru_id = grupo_prod.gru_id)
inner join fornecedor on (produtos.for_id = fornecedor.for_id)
order by fornecedor, grupo_prod.grupo, produtos.prod_descricao
INTO :PROD_ID,
:PROD_DESCRICAO,
:GRUPO,
:fornecedor_titulo,
:FOR_ID,
:FORNECEDOR,
:PROD_UND_TRIB,
:QTDE_CAIXA,
:PROD_VALOR_COMPRA
DO
FOR
select
coalesce(avg(pedidos_itens.pi_preco),0),
coalesce(sum(pedidos_itens.pi_qtde),0),
coalesce((sum(pedidos_itens.pi_total) + sum(pedidos_itens.pi_desconto)),0),
coalesce(sum(pedidos_itens.pi_total),0),
coalesce(sum(pedidos_itens.pi_desconto),0)
from pedidos_itens
left join pedidos on (pedidos_itens.ped_id = pedidos.ped_id)
where pedidos.status = 1 and pedidos.tipo_id < 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id = :loja and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :preco_medio,
:qtde_vendida,
:venda_bruta,
:Venda_liquida,
:Desconto
DO
FOR
select
coalesce(sum(pedidos_itens.pi_qtde),0),
coalesce(:prod_valor_compra * coalesce(sum(pedidos_itens.pi_qtde),0),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where pedidos.status = 1 and pedidos.tipo_id = 5 and pedidos_itens.prod_id = :prod_id and pedidos.loj_id = :loja and
pedidos_itens.cancelado = 'N' and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :qtde_bonificacao,
:VENDA_BONIFICADA
DO
FOR
select
coalesce(:prod_valor_compra * sum(pedidos_itens.pi_qtde),0)
from pedidos
inner join pedidos_itens on (pedidos.ped_id = pedidos_itens.ped_id)
where
pedidos.status = 1 and pedidos_itens.prod_id = :prod_id and pedidos.tipo_id <> 6 and pedidos.loj_id = :loja and
cast(pedidos.data_emissao as date) between :dt_ini and :dt_fim
INTO :Custo
DO
BEGIN
Qtde_Total = :qtde_vendida+:qtde_bonificacao;
venda_total = :venda_bonificada + :venda_bruta;
perc_bonificao = iif(:venda_bonificada = 0, 0,
iif(:venda_liquida > 0, (:venda_bonificada / (:venda_bonificada + :venda_bruta))*100,100));
perc_venda = iif(:venda_liquida > 0,(:venda_liquida / (:venda_bonificada + :venda_bruta))*100,0);
perc_desconto = iif(:venda_liquida > 0, (:desconto / (:venda_bonificada + :venda_bruta)) *100,0);
lucro_operacional = iif(:venda_liquida > 0, (:venda_bonificada+:venda_liquida) - :custo,0);
perc_lucro = iif( :custo > 0 , (((:venda_bonificada+:venda_liquida) - :custo) / (:venda_bonificada+:venda_liquida))*100,0);
SUSPEND;
END
end
END
Sobre as perugntas, são interessantes. Como havia dito, sou iniciante, e tem coisa que não tenho certeza em responder, mas vou tentar.
1. Estatísticas no banco de dados estão ativadas para mensurar os custos de execução das queries nesse servidor ?
R - Nâo sei disser, não sei onde ativa. Mas no IBExpert ele me passa aquele relatório.
2. Qual o custo de execução da query completa ?
R - Passei aquele relatório
3. Qual o custo de execução parcial da query ?
R - Passei aquele relatório
4. É possível identificar os pontos de gargalo de perfomance ?
R - Não sei informar, inclusive não entendi até hj o que é o PLAN e PLAN Adapter que ele mostra no relatório.
5. É possível melhorar a lógica das consultas nos gargalos identificados ?
R - Acredito que sim
6. Qual o nível de isolamento utilizado ?
R - Não sei informar. O que seria esse nível de isolamento?
7. A alteração para níveis de isolamento mais permissíveis, altera a performance ?
R - Não sei informar.
8. Qual o impacto de uma eventual alteração do nível de isolamento na integridade da operação ?
R - Não sei informar.
9. Qual a estrutura de índices suporta essas consultas ?
R - Acredito que seja os índices criados. São esses:
PLAN JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS_ITENS INDEX (PEDIDOS_ITENS_IDX2), PEDIDOS INDEX (PK_PEDIDOS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4, PEDIDOS_IDX2), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS))SORT (SORT (JOIN (PEDIDOS INDEX (PEDIDOS_IDX4, PEDIDOS_IDX2), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS), PRODUTOS INDEX (PK_PRODUTOS), GRUPO_PROD INDEX (PK_GRUPO_PROD), FORNECEDOR INDEX (RDB$PRIMARY2))))JOIN (IT INDEX (PEDIDOS_ITENS_IDX2), PEDIDOS INDEX (PK_PEDIDOS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), IT INDEX (PK_PEDIDOS_ITENS))JOIN (IT INDEX (PEDIDOS_ITENS_IDX2), PEDIDOS INDEX (PK_PEDIDOS))JOIN (PEDIDOS_ITENS INDEX (PEDIDOS_ITENS_IDX2), PEDIDOS INDEX (PK_PEDIDOS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS_ITENS INDEX (PEDIDOS_ITENS_IDX2), PEDIDOS INDEX (PK_PEDIDOS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS))JOIN (PEDIDOS INDEX (FK_PEDIDOS_1, PEDIDOS_IDX4), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS))SORT (SORT (JOIN (PEDIDOS INDEX (PEDIDOS_IDX4), PEDIDOS_ITENS INDEX (PK_PEDIDOS_ITENS), PRODUTOS INDEX (PK_PRODUTOS), GRUPO_PROD INDEX (PK_GRUPO_PROD), FORNECEDOR INDEX (RDB$PRIMARY2))))
10. Cabe ajustes na estrutura de índices para melhoria de performance ?
R - Acredito que sim
Responder
Gostei + 0
18/03/2015
Thiago Santana
Outra dica, caso tenha índices na sua tabela, efetue a consulta filtrando por esses índices, pois ter um índice e não utiliza gera um custo desnecessário!
Responder
Gostei + 0
18/03/2015
Sidney Abreu
Como eu tenho a certeza que estou utilizando esses índices?
Responder
Gostei + 0
18/03/2015
Marcos P
Sidney,
Parabéns pela revisão da lógica, conseguiu uma melhoria importante na performance !
Quanto a ajuda em deixar o código mais enxuto, gostaria muito de ajudar, mas preciso montar um ambiente FB para isso... e, infelizmente, não disponho de tempo no momento.
Quanto as suas respostas, vou tentar dar mais uma ajuda rápida :
Estatísticas de Banco de Dados, representa o mecanismo de coleta de informações de todas as transações submetidas ao servidor.
De posse dessas informações o SGBD pode propor a criação de objetos ( principalmente índices ) e mensurar os custos de execução de cada query ( antes mesmo da query rodar ).
Em ambiente profissional, não existe Banco de Dados sem estatísticas confiáveis !
Para Firebird dê uma olhada em :
> http://www.ib-aid.com/en/articles/ibanalyst-how-to-get-statistics-from-interbase-firebird-database-in-right-way/
> http://ibexpert.net/ibe/index.php?n=Doc.Optimization
> http://pt.slideshare.net/ibsurgeon/resolving-firebird-performance-problems
_______________
Planos de Execução, detalham todos os elementos de banco de dados envolvidos em determinada transação, definindo para cada parte específica da query o custo de execução e permitindo identificar os pontos de gargalo.
Para Firebird dê uma olhada em :
> http://pabloj.blogspot.com.br/2006/10/firebirds-explain-plan_116205780384456436.html
> http://www.firebirdfaq.org/faq224/
> http://www.sqlmanager.net/en/products/ibfb/manager/documentation/hs6140.html
_______________
Nível de Isolamento, define como o SGBD irá tratar o acesso de leitura aos dados quando JÁ houver uma transação de escrita ( insert, delete ou update ) em curso, sob o mesmo conjunto de dados.
Nível de isolamento mais restritivos, indica que as leituras serão mais confiáveis mas o nível de travamento de outros usuários tende a ser maior
Nível de isolamento menos restritivos, indica o contrário... o que as leituras serão MENOS confiáveis mas o nível de travamento de outros usuários tende a ser BAIXO
Para Firebird dê uma olhada em :
> http://www.firebirdsql.org/manual/isql-transactions.html
> http://www.google.com.br/url?sa=t&rct=j&q=&esrc=s&source=web&cd=3&cad=rja&uact=8&ved=0CDEQFjAC&url=http%3A%2F%2Ffhasovic.blogspot.com%2F2005%2F02%2Ftransaction-isolation-levels-in.html&ei=ON0JVfS8M437sATfnIDICw&usg=AFQjCNHkkYct8oyIuoF1-YJ0ihN-wt7K7Q&sig2=xqON_y3E46vzd0J-7agFFw&bvm=bv.88198703,d.cWc
> http://forum.lazarus.freepascal.org/index.php?topic=23307.0
_______________
Índices, bem... índices são índices !
Existe muita bobagem na WEB sobre isso... muita gente implementa uma modelo de dados furado e uma lógica de consulta toda errada e tenta consertar a performance com índices depois.
Primeiro, garanta a integridade do seu modelo de dados...
Depois, garanta a integridade de sua lógica ( por isso te orientei a começar pelo código )...
Para só então... otimizar a performance com os índices necessários ( e apenas os necessários ).
Para Firebird dê uma olhada em :
> https://www.youtube.com/watch?v=6RKgmD-NefM
> http://www.firebirdfaq.org/faq167/
> http://firebirdsurgeon.blogspot.com.br/2011/07/how-to-ruin-firebird-performance-with.html
Bem, acho que é isso... encerro aqui nesse post, deixando o alerta que você tem bastante estudo e um caminho longo pela frente.
Boa sorte !
Parabéns pela revisão da lógica, conseguiu uma melhoria importante na performance !
Quanto a ajuda em deixar o código mais enxuto, gostaria muito de ajudar, mas preciso montar um ambiente FB para isso... e, infelizmente, não disponho de tempo no momento.
Quanto as suas respostas, vou tentar dar mais uma ajuda rápida :
Estatísticas de Banco de Dados, representa o mecanismo de coleta de informações de todas as transações submetidas ao servidor.
De posse dessas informações o SGBD pode propor a criação de objetos ( principalmente índices ) e mensurar os custos de execução de cada query ( antes mesmo da query rodar ).
Em ambiente profissional, não existe Banco de Dados sem estatísticas confiáveis !
Para Firebird dê uma olhada em :
> http://www.ib-aid.com/en/articles/ibanalyst-how-to-get-statistics-from-interbase-firebird-database-in-right-way/
> http://ibexpert.net/ibe/index.php?n=Doc.Optimization
> http://pt.slideshare.net/ibsurgeon/resolving-firebird-performance-problems
_______________
Planos de Execução, detalham todos os elementos de banco de dados envolvidos em determinada transação, definindo para cada parte específica da query o custo de execução e permitindo identificar os pontos de gargalo.
Para Firebird dê uma olhada em :
> http://pabloj.blogspot.com.br/2006/10/firebirds-explain-plan_116205780384456436.html
> http://www.firebirdfaq.org/faq224/
> http://www.sqlmanager.net/en/products/ibfb/manager/documentation/hs6140.html
_______________
Nível de Isolamento, define como o SGBD irá tratar o acesso de leitura aos dados quando JÁ houver uma transação de escrita ( insert, delete ou update ) em curso, sob o mesmo conjunto de dados.
Nível de isolamento mais restritivos, indica que as leituras serão mais confiáveis mas o nível de travamento de outros usuários tende a ser maior
Nível de isolamento menos restritivos, indica o contrário... o que as leituras serão MENOS confiáveis mas o nível de travamento de outros usuários tende a ser BAIXO
Para Firebird dê uma olhada em :
> http://www.firebirdsql.org/manual/isql-transactions.html
> http://www.google.com.br/url?sa=t&rct=j&q=&esrc=s&source=web&cd=3&cad=rja&uact=8&ved=0CDEQFjAC&url=http%3A%2F%2Ffhasovic.blogspot.com%2F2005%2F02%2Ftransaction-isolation-levels-in.html&ei=ON0JVfS8M437sATfnIDICw&usg=AFQjCNHkkYct8oyIuoF1-YJ0ihN-wt7K7Q&sig2=xqON_y3E46vzd0J-7agFFw&bvm=bv.88198703,d.cWc
> http://forum.lazarus.freepascal.org/index.php?topic=23307.0
_______________
Índices, bem... índices são índices !
Existe muita bobagem na WEB sobre isso... muita gente implementa uma modelo de dados furado e uma lógica de consulta toda errada e tenta consertar a performance com índices depois.
Primeiro, garanta a integridade do seu modelo de dados...
Depois, garanta a integridade de sua lógica ( por isso te orientei a começar pelo código )...
Para só então... otimizar a performance com os índices necessários ( e apenas os necessários ).
Para Firebird dê uma olhada em :
> https://www.youtube.com/watch?v=6RKgmD-NefM
> http://www.firebirdfaq.org/faq167/
> http://firebirdsurgeon.blogspot.com.br/2011/07/how-to-ruin-firebird-performance-with.html
Bem, acho que é isso... encerro aqui nesse post, deixando o alerta que você tem bastante estudo e um caminho longo pela frente.
Boa sorte !
Responder
Gostei + 0
18/03/2015
Sidney Abreu
Mais uma vez obrigado pessoal. Abraços
Responder
Gostei + 0
Clique aqui para fazer login e interagir na Comunidade :)