Procedure com performance degradada
Olá amigos,
Criei uma procedure que efetua alguns calculos de quantidades e valores, sendo que para isso utilizo selects que buscam registros em um determinado período.
Essa procedure, estava demorando um absurdo para executar, então verifiquei a necessidade da criação de alguns indices que melhoraram muito o tempo de execução.
Acontece que quando a quantidade de registros a ser processada é muito grande, ela vai perdendo performance durante a execução até virar uma ´tartaruga´ e consumindo totalmente a cpu da máquina.
Esta mesma rotina, feita diretamente no delphi, executa em 7 min e diretamente no banco eu abortei a operação com 2 horas de execução.
Alguém tem alguma idéia do que possa estar ocorrendo e como posso resolver esse problemão?
Verifiquei com um programa de análise de performance que os seguintes itens vão aumentando a medida em que a procedure vai rodando:
Memory Buffer Reads
Memory Buffer Writes
Current Memory
Max Memory
Number of Buffers
Como não conheço muito bem essa parte de melhoria de performance, estou recorrendo ao fórum pra ver se alguém pode me ajudar.
Desde já agradeço,
Renato
Criei uma procedure que efetua alguns calculos de quantidades e valores, sendo que para isso utilizo selects que buscam registros em um determinado período.
Essa procedure, estava demorando um absurdo para executar, então verifiquei a necessidade da criação de alguns indices que melhoraram muito o tempo de execução.
Acontece que quando a quantidade de registros a ser processada é muito grande, ela vai perdendo performance durante a execução até virar uma ´tartaruga´ e consumindo totalmente a cpu da máquina.
Esta mesma rotina, feita diretamente no delphi, executa em 7 min e diretamente no banco eu abortei a operação com 2 horas de execução.
Alguém tem alguma idéia do que possa estar ocorrendo e como posso resolver esse problemão?
Verifiquei com um programa de análise de performance que os seguintes itens vão aumentando a medida em que a procedure vai rodando:
Memory Buffer Reads
Memory Buffer Writes
Current Memory
Max Memory
Number of Buffers
Como não conheço muito bem essa parte de melhoria de performance, estou recorrendo ao fórum pra ver se alguém pode me ajudar.
Desde já agradeço,
Renato
Renatotm
Curtidas 0
Respostas
Emerson Nascimento
10/03/2006
se você puder, publique sua stored procedure. assim o pessoal do fórum poderá te ajudar de forma mais consistente, inclusive num possível problema de lógica.
GOSTEI 0
Renatotm
10/03/2006
CREATE PROCEDURE SALDO_PRODUTOS (
DATA_LIMITE DATE,
CGC_CLIENTE VARCHAR(14) CHARACTER SET NONE)
RETURNS (
NM_CLI VARCHAR(60) CHARACTER SET NONE,
CGC VARCHAR(14) CHARACTER SET NONE,
ID_PRODUTO VARCHAR(20) CHARACTER SET NONE,
ID_PRODUTO1 VARCHAR(20) CHARACTER SET NONE,
DESCRICAO VARCHAR(60) CHARACTER SET NONE,
UNIDADE VARCHAR(5) CHARACTER SET NONE,
CLASSFISCAL VARCHAR(15) CHARACTER SET NONE,
DT_SALDO_INI DATE,
QTD_SALDO_INI NUMERIC(15,3),
VAL_SALDO_INI NUMERIC(15,3),
QTD_ENT NUMERIC(15,3),
VAL_ENT NUMERIC(15,3),
QTD_SAI NUMERIC(15,3),
VAL_SAI NUMERIC(15,3),
QTD_SALDO NUMERIC(15,3),
VAL_SALDO NUMERIC(15,3))
AS
DECLARE VARIABLE ULTIMA_DATA DATE;
DECLARE VARIABLE ID_PROD VARCHAR(20);
DECLARE VARIABLE ID_PROD1 VARCHAR(20);
DECLARE VARIABLE DETALHE VARCHAR(60);
DECLARE VARIABLE UNID VARCHAR(5);
DECLARE VARIABLE CLAS_FISC VARCHAR(15);
DECLARE VARIABLE DT_SALDO DATE;
DECLARE VARIABLE QT_SALDO NUMERIC(15,3);
DECLARE VARIABLE VL_SALDO NUMERIC(15,3);
DECLARE VARIABLE QT_ENT NUMERIC(15,3);
DECLARE VARIABLE VL_ENT NUMERIC(15,3);
DECLARE VARIABLE QT_SAI NUMERIC(15,3);
DECLARE VARIABLE VL_SAI NUMERIC(15,3);
DECLARE VARIABLE DATAINI DATE;
begin
/* Pega a data mais recente de saldo */
SELECT MAX(DATA) AS DATA
FROM SALDO_PRODUTO
WHERE CGC = :CGC_CLIENTE
AND DATA <= :DATA_LIMITE
INTO :ULTIMA_DATA;
dataini = ULTIMA_DATA + 1;
dataini = CAST(EXTRACT(day FROM dataini) AS VARCHAR(2)) || ´.´ ||
CAST(EXTRACT(month FROM dataini) AS VARCHAR(2)) || ´.´ ||
(CAST(EXTRACT(year FROM dataini) AS VARCHAR(4)));
/* Seleciona os produtos do cliente */
for
SELECT P.ID_PRODUTO
, P.ID_PRODUTO1
, P.DETALHE
, P.CLASSIFISCAL
, P.UNIDADE
FROM PRODUTO P
WHERE P.CGC = :CGC_CLIENTE
ORDER BY P.ID_PRODUTO1
, P.DETALHE
into :id_prod
, :id_prod1
, :detalhe
, :clas_fisc
, :unid
do
begin
/* Pega saldo inicial do produto */
SELECT SALDO
, VALOR_SALDO
, DATA
FROM SALDO_PRODUTO
WHERE DATA = :ultima_data
AND CGC = :cgc_cliente
AND ID_PRODUTO = :id_prod
INTO :qt_saldo
, :vl_saldo
, :dt_saldo;
/* Pega entradas do produto no periodo */
select sum(nfeitem.qtd)
, sum(nfeitem.qtd * nfeitem.valor)
from nfeitem
where nfeitem.data_ent between :dataini and :data_limite
and nfeitem.id_produto = :id_prod
and nfeitem.cgc = :cgc_cliente
INTO :qt_ent
, :vl_ent;
if (qt_ent is null) then
qt_ent = 0;
if (vl_ent is null) then
vl_ent = 0;
/* Pega saidas do produto no periodo */
select sum(nfsitem.qtde)
, sum(nfsitem.qtde * nfsitem.preco_unit)
from nfscab
, nfsitem
where nfsitem.controle = nfscab.controle
and nfscab.data_emissao between :dataini and :data_limite
and nfscab.cgc = :cgc_cliente
and nfscab.situacao in (´EMI´, ´SEP´, ´EXP´, ´BLQ´)
and nfsitem.id_produto = :id_prod
INTO :qt_sai
, :vl_sai;
if (qt_sai is null) then
qt_sai = 0;
if (vl_sai is null) then
vl_sai = 0;
nm_cli = ´ ´;
cgc = cgc_cliente;
id_produto = id_prod;
id_produto1 = id_prod1;
descricao = detalhe;
unidade = unid;
classfiscal = clas_fisc;
dt_saldo_ini = dt_saldo;
qtd_saldo_ini = qt_saldo;
val_saldo_ini = vl_saldo;
qtd_ent = qt_ent;
val_ent = vl_ent;
qtd_sai = qt_sai;
val_sai = vl_sai;
qtd_saldo = (qt_saldo + qt_ent) - qt_sai;
val_saldo = (vl_saldo + vl_ent) - vl_sai;
suspend;
end
end^
DATA_LIMITE DATE,
CGC_CLIENTE VARCHAR(14) CHARACTER SET NONE)
RETURNS (
NM_CLI VARCHAR(60) CHARACTER SET NONE,
CGC VARCHAR(14) CHARACTER SET NONE,
ID_PRODUTO VARCHAR(20) CHARACTER SET NONE,
ID_PRODUTO1 VARCHAR(20) CHARACTER SET NONE,
DESCRICAO VARCHAR(60) CHARACTER SET NONE,
UNIDADE VARCHAR(5) CHARACTER SET NONE,
CLASSFISCAL VARCHAR(15) CHARACTER SET NONE,
DT_SALDO_INI DATE,
QTD_SALDO_INI NUMERIC(15,3),
VAL_SALDO_INI NUMERIC(15,3),
QTD_ENT NUMERIC(15,3),
VAL_ENT NUMERIC(15,3),
QTD_SAI NUMERIC(15,3),
VAL_SAI NUMERIC(15,3),
QTD_SALDO NUMERIC(15,3),
VAL_SALDO NUMERIC(15,3))
AS
DECLARE VARIABLE ULTIMA_DATA DATE;
DECLARE VARIABLE ID_PROD VARCHAR(20);
DECLARE VARIABLE ID_PROD1 VARCHAR(20);
DECLARE VARIABLE DETALHE VARCHAR(60);
DECLARE VARIABLE UNID VARCHAR(5);
DECLARE VARIABLE CLAS_FISC VARCHAR(15);
DECLARE VARIABLE DT_SALDO DATE;
DECLARE VARIABLE QT_SALDO NUMERIC(15,3);
DECLARE VARIABLE VL_SALDO NUMERIC(15,3);
DECLARE VARIABLE QT_ENT NUMERIC(15,3);
DECLARE VARIABLE VL_ENT NUMERIC(15,3);
DECLARE VARIABLE QT_SAI NUMERIC(15,3);
DECLARE VARIABLE VL_SAI NUMERIC(15,3);
DECLARE VARIABLE DATAINI DATE;
begin
/* Pega a data mais recente de saldo */
SELECT MAX(DATA) AS DATA
FROM SALDO_PRODUTO
WHERE CGC = :CGC_CLIENTE
AND DATA <= :DATA_LIMITE
INTO :ULTIMA_DATA;
dataini = ULTIMA_DATA + 1;
dataini = CAST(EXTRACT(day FROM dataini) AS VARCHAR(2)) || ´.´ ||
CAST(EXTRACT(month FROM dataini) AS VARCHAR(2)) || ´.´ ||
(CAST(EXTRACT(year FROM dataini) AS VARCHAR(4)));
/* Seleciona os produtos do cliente */
for
SELECT P.ID_PRODUTO
, P.ID_PRODUTO1
, P.DETALHE
, P.CLASSIFISCAL
, P.UNIDADE
FROM PRODUTO P
WHERE P.CGC = :CGC_CLIENTE
ORDER BY P.ID_PRODUTO1
, P.DETALHE
into :id_prod
, :id_prod1
, :detalhe
, :clas_fisc
, :unid
do
begin
/* Pega saldo inicial do produto */
SELECT SALDO
, VALOR_SALDO
, DATA
FROM SALDO_PRODUTO
WHERE DATA = :ultima_data
AND CGC = :cgc_cliente
AND ID_PRODUTO = :id_prod
INTO :qt_saldo
, :vl_saldo
, :dt_saldo;
/* Pega entradas do produto no periodo */
select sum(nfeitem.qtd)
, sum(nfeitem.qtd * nfeitem.valor)
from nfeitem
where nfeitem.data_ent between :dataini and :data_limite
and nfeitem.id_produto = :id_prod
and nfeitem.cgc = :cgc_cliente
INTO :qt_ent
, :vl_ent;
if (qt_ent is null) then
qt_ent = 0;
if (vl_ent is null) then
vl_ent = 0;
/* Pega saidas do produto no periodo */
select sum(nfsitem.qtde)
, sum(nfsitem.qtde * nfsitem.preco_unit)
from nfscab
, nfsitem
where nfsitem.controle = nfscab.controle
and nfscab.data_emissao between :dataini and :data_limite
and nfscab.cgc = :cgc_cliente
and nfscab.situacao in (´EMI´, ´SEP´, ´EXP´, ´BLQ´)
and nfsitem.id_produto = :id_prod
INTO :qt_sai
, :vl_sai;
if (qt_sai is null) then
qt_sai = 0;
if (vl_sai is null) then
vl_sai = 0;
nm_cli = ´ ´;
cgc = cgc_cliente;
id_produto = id_prod;
id_produto1 = id_prod1;
descricao = detalhe;
unidade = unid;
classfiscal = clas_fisc;
dt_saldo_ini = dt_saldo;
qtd_saldo_ini = qt_saldo;
val_saldo_ini = vl_saldo;
qtd_ent = qt_ent;
val_ent = vl_ent;
qtd_sai = qt_sai;
val_sai = vl_sai;
qtd_saldo = (qt_saldo + qt_ent) - qt_sai;
val_saldo = (vl_saldo + vl_ent) - vl_sai;
suspend;
end
end^
GOSTEI 0
Emerson Nascimento
10/03/2006
veja se assim funciona e se a performance melhora:
(os índices devem ser criados para aqueles campos que fazer parte de relacionamento ou que constam em cláusulas where)
create procedure saldo_produtos (
data_limite date,
cgc_cliente varchar(14) character set none)
RETURNS (
nm_cli varchar(60) character set none,
cgc varchar(14) character set none,
id_produto varchar(20) character set none,
id_produto1 varchar(20) character set none,
descricao varchar(60) character set none,
unidade varchar(5) character set none,
classfiscal varchar(15) character set none,
dt_saldo_ini date,
qtd_saldo_ini numeric(15,3),
val_saldo_ini numeric(15,3),
qtd_ent numeric(15,3),
val_ent numeric(15,3),
qtd_sai numeric(15,3),
val_sai numeric(15,3),
qtd_saldo numeric(15,3),
val_saldo numeric(15,3))
as
declare variable ultima_data DATE;
declare variable dataini DATE;
begin
/* Pega a data mais recente de saldo */
select max(data) as data from saldo_produto
where cgc = :cgc_cliente and data <= :data_limite
into :ultima_data;
dataini = ultima_data + 1;
cgc = cgc_cliente;
dt_saldo_ini = ultima_data;
/* Seleciona os produtos do cliente */
for
select
p.id_produto, p.id_produto1, p.detalhe,
p.classifiscal, p.unidade,
Coalesce(SP.SALDO,0) saldo,
Coalesce(SP.VALOR_SALDO,0) valor_saldo,
Coalesce(sum(nfeitem.qtd),0) qt_ent,
Coalesce(sum(nfeitem.qtd * nfeitem.valor),0) vl_ent
from
produto p
left join
saldo_produto sp ON (sp.id_produto = p.id_produto
and sp.cgc = p.cgc and sp.data = :ultima_data)
left join
nfeitem nfi ON (nfei.id_produto = p.id_produto AND nfei.cgc = p.cgc
and nfei.data_ent between :dataini and :data_limite)
where
p.cgc = :cgc
group by
p.id_produto, p.id_produto1, p.detalhe,
p.classifiscal, p.unidade,
sp.saldo,
sp.valor_saldo
order by
p.id_produto1, p.detalhe
into :id_produto, :id_produto1, :descricao,
:classfiscal, :unidade,
:qtd_saldo_ini, :val_saldo_ini,
:qtd_ent, :val_ent do
begin
/* Pega saidas do produto no periodo */
select
coalesce(sum(nfsitem.qtde),0),
coalesce(sum(nfsitem.qtde * nfsitem.preco_unit),0)
from
nfscab
inner join
nfsitem on (nfsitem.controle = nfscab.controle)
where
nfscab.data_emissao between :dataini and :data_limite
and nfscab.cgc = :cgc
and nfscab.situacao in (´EMI´, ´SEP´, ´EXP´, ´BLQ´)
and nfsitem.id_produto = :id_produto
into :qtd_sai, :val_sai;
nm_cli = ´ ´;
qtd_saldo = (qtd_saldo_ini + qtd_ent) - qtd_sai;
val_saldo = (val_saldo_ini + val_ent) - val_sai;
suspend;
end
end^(os índices devem ser criados para aqueles campos que fazer parte de relacionamento ou que constam em cláusulas where)
GOSTEI 0
Renatotm
10/03/2006
Olá Emerson,
Fiz o teste com a procedure que você mandou e a performance melhorou um pouco, mas ainda está inaceitável, visto que cancelei com 30 minutos e ainda não estava nem na metade dos registros.
Não entendo o que acontece, porque a mesma rotina feita no delphi roda tão rápido... Esta é a primeira vez que uma procedure fica mais lenta que o sistema.. hehehe...
Obrigado pela ajuda e se tiver mais alguma idéia será bem vinda.
Renato
Fiz o teste com a procedure que você mandou e a performance melhorou um pouco, mas ainda está inaceitável, visto que cancelei com 30 minutos e ainda não estava nem na metade dos registros.
Não entendo o que acontece, porque a mesma rotina feita no delphi roda tão rápido... Esta é a primeira vez que uma procedure fica mais lenta que o sistema.. hehehe...
Obrigado pela ajuda e se tiver mais alguma idéia será bem vinda.
Renato
GOSTEI 0
Emerson Nascimento
10/03/2006
tente assim... altere a procedure para:
e crie os seguintes índices nas tabelas em uso.
create procedure saldo_produtos (
data_limite date,
cgc_cliente varchar(14) character set none)
RETURNS (
nm_cli varchar(60) character set none,
cgc varchar(14) character set none,
id_produto varchar(20) character set none,
id_produto1 varchar(20) character set none,
descricao varchar(60) character set none,
unidade varchar(5) character set none,
classfiscal varchar(15) character set none,
dt_saldo_ini date,
qtd_saldo_ini numeric(15,3),
val_saldo_ini numeric(15,3),
qtd_ent numeric(15,3),
val_ent numeric(15,3),
qtd_sai numeric(15,3),
val_sai numeric(15,3),
qtd_saldo numeric(15,3),
val_saldo numeric(15,3))
as
declare variable ultima_data DATE;
declare variable dataini DATE;
begin
/* Pega a data mais recente de saldo */
select max(data) as data from saldo_produto
where cgc = :cgc_cliente and data <= :data_limite
into :ultima_data;
dataini = ultima_data + 1;
cgc = cgc_cliente;
dt_saldo_ini = ultima_data;
/* Seleciona os produtos do cliente */
for
select
p.id_produto, p.id_produto1, p.detalhe,
p.classifiscal, p.unidade,
Coalesce(SP.SALDO,0) saldo,
Coalesce(SP.VALOR_SALDO,0) valor_saldo,
Coalesce(sum(nfeitem.qtd),0) qt_ent,
Coalesce(sum(nfeitem.qtd * nfeitem.valor),0) vl_ent
from
produto p
left join
saldo_produto sp ON (sp.id_produto = p.id_produto
and sp.cgc = p.cgc and sp.data = :ultima_data)
left join
nfeitem nfi ON (nfei.id_produto = p.id_produto AND nfei.cgc = p.cgc
and nfei.data_ent between :dataini and :data_limite)
where
p.cgc = :cgc
group by
p.id_produto, p.id_produto1, p.detalhe,
p.classifiscal, p.unidade,
sp.saldo,
sp.valor_saldo
order by
p.id_produto1, p.detalhe
into :id_produto, :id_produto1, :descricao,
:classfiscal, :unidade,
:qtd_saldo_ini, :val_saldo_ini,
:qtd_ent, :val_ent do
begin
/* Pega saidas do produto no periodo */
select
coalesce(sum(nfsitem.qtde),0),
coalesce(sum(nfsitem.qtde * nfsitem.preco_unit),0)
from
nfscab
/* esse join sofreu alteração */
inner join
nfsitem on (nfsitem.controle = nfscab.controle
and nfsitem.id_produto = :id_produto)
where
nfscab.data_emissao between :dataini and :data_limite
and nfscab.cgc = :cgc
and nfscab.situacao in (´EMI´, ´SEP´, ´EXP´, ´BLQ´)
into :qtd_sai, :val_sai;
nm_cli = ´ ´;
qtd_saldo = (qtd_saldo_ini + qtd_ent) - qtd_sai;
val_saldo = (val_saldo_ini + val_ent) - val_sai;
suspend;
end
end^e crie os seguintes índices nas tabelas em uso.
+---------------+----------------------------------------+ |TABELA | INDICE | +---------------+----------------------------------------+ |produto | id_produto, cgc | | +----------------------------------------+ | | id_produto1, detalhe | +---------------+----------------------------------------+ |saldo_produto | id_produto, cgc, data | | +----------------------------------------+ | | cgc, data | +---------------+----------------------------------------+ |nfeitem | id_produto, cgc, data_ent | +---------------+----------------------------------------+ |nfscab | controle, cgc, situacao, data_emissao | +---------------+----------------------------------------+ |nfsitem | controle, id_produto | +---------------+----------------------------------------+
GOSTEI 0
Renatotm
10/03/2006
Olá Emerson,
Fiz as alterações que você passou e criei os indices que você mencionou acima, porém, a rotina ainda está lenta.
Mais uma ves obrigado pela ajuda.
Grato,
Renato
Fiz as alterações que você passou e criei os indices que você mencionou acima, porém, a rotina ainda está lenta.
Mais uma ves obrigado pela ajuda.
Grato,
Renato
GOSTEI 0