Stored Procedure Demoraaaaaaaada! ;o
Olá pessoal.
Possuo um banco no qual preciso verificar quais clientes estão com os pagamentos das mensalidades dos seus contratos em dia.
Esse procedimento, eu executo no Banco de Dados através de uma Stored Procedure.
A stored procedure verifica os meses que não foram pagos e dá um insert numa tabela temporária, que será usada para se saber os meses em débito de cada contrato.
Bom, a SP está sendo executada com perfeição, pois os meses são gravados na tabela temporária, mas a sua LENTIDÃO É ABSURDA, dada a quantidade de registros que existem na tabela de pagamentos (mais de 80 mil, e mais de 4 mil contratos).
O código da Stored Procedure é o seguinte:
Para fins de teste, eu usei esta SP com 5 (CINCO) clientes, usando a tabela de pagamentos com os 80 mil registros e a execução da mesma demorou 1 minuto e 4 segundos.
Imagine a execução com o banco usando os 4000 clientes, verificando um a um... nesse cenário, tive que abortar a aplicação após mais de 30 minutos processando.......
[b:f1b4047068]O que preciso saber é se alguém tem uma idéia mais interessante para realizar a captura dos meses em atraso, ou se tem idéia de que tipo de índice eu possa usar (estou usando os campos da consulta à tabela PLANOSPAGAMENTOS indexados). Se alguém tiver uma idéia que melhore substancialmente a performance da SP, eu agradeço.[/b:f1b4047068]
Já pensei em cortar a inclusão de mês a mês das mensalidades na tabela temporária, incluindo somente a quantidade total de meses, mas a consulta à tabela PLANOSPAGAMENTOS irá permanecer a cada cliente e a cada mês, o que tem gerado mais de 79 milhões de fetches no caso do piloto que fiz com os 5 clientes apenas. Imagine!!!!
Segue, abaixo, a Análise de Desempenho do IBExpert usando a procedure com 5 clientes apenas:
Query time: 1m 4s 984ms
Obrigado a todos pela atenção e, quando e conseguir a solução, posto aqui, caso ainda não tenham respondido.
Edson Cabral
Possuo um banco no qual preciso verificar quais clientes estão com os pagamentos das mensalidades dos seus contratos em dia.
Esse procedimento, eu executo no Banco de Dados através de uma Stored Procedure.
A stored procedure verifica os meses que não foram pagos e dá um insert numa tabela temporária, que será usada para se saber os meses em débito de cada contrato.
Bom, a SP está sendo executada com perfeição, pois os meses são gravados na tabela temporária, mas a sua LENTIDÃO É ABSURDA, dada a quantidade de registros que existem na tabela de pagamentos (mais de 80 mil, e mais de 4 mil contratos).
O código da Stored Procedure é o seguinte:
SET TERM ^ ;
CREATE PROCEDURE SP_PROCESSA_PREST_ATR_GERAL
AS
DECLARE VARIABLE DATA1PARCELA DATE;
DECLARE VARIABLE DATAINICIAL DATE;
DECLARE VARIABLE DATAFINAL DATE;
DECLARE VARIABLE CONTADOR_PAG INTEGER;
DECLARE VARIABLE LOJA INTEGER;
DECLARE VARIABLE CONTRATO INTEGER;
DECLARE VARIABLE MESREFERENCIA SMALLINT;
DECLARE VARIABLE ANOREFERENCIA SMALLINT;
DECLARE VARIABLE VALOR NUMERIC(9,2);
begin
--limpa a tabela temporária...
delete from TEMP_PRESATRASADAS;
DATAFINAL = CURRENT_DATE;
for select DATVENC1PARCELA, CODIGO, CODLOJA, VALORMENSALIDADE
from CONTRATOS into DATA1PARCELA, CONTRATO, LOJA, VALOR do
begin
--em alguns casos, a DATAINICIAL chega a ser de 01/01/1998 (clientes antigos)
DATAINICIAL = DATA1PARCELA;
while (DATAINICIAL < DATAFINAL) do
begin
MESREFERENCIA = EXTRACT(MONTH from DATAINICIAL);
ANOREFERENCIA = EXTRACT(YEAR from DATAINICIAL);
--vê quantos meses está em atraso... e joga na var contador_pag
select count(*) from PLANOSPAGAMENTOS where
(PLANOSPAGAMENTOS.ANOREFERENCIA = ANOREFERENCIA) and (PLANOSPAGAMENTOS.MESREFERENCIA = MESREFERENCIA)
into CONTADOR_PAG;
if (CONTADOR_PAG = 0) then
begin
insert into
TEMP_PRESATRASADAS(LOJA, CONTRATO, MESREFERENCIA, ANOREFERENCIA, VALOR)
values
(:LOJA, :CONTRATO, :MESREFERENCIA, :ANOREFERENCIA, :VALOR);
end
--esta função abaixo incrementa o mês passado como parâmetro...
DATAINICIAL = F_ADDMONTH(DATAINICIAL, 1);
end
end
--posta um evento para informar às estações o processamento concluído
POST_EVENT ´processa_prest_atr_concluida´;
--Informa na tabela config quando foi o último processamento de prestações atrasadas...
update STATUS set ULTIMO_PROCESS_PREST_ATR_GERAL = CURRENT_TIMESTAMP;
end^
Para fins de teste, eu usei esta SP com 5 (CINCO) clientes, usando a tabela de pagamentos com os 80 mil registros e a execução da mesma demorou 1 minuto e 4 segundos.
Imagine a execução com o banco usando os 4000 clientes, verificando um a um... nesse cenário, tive que abortar a aplicação após mais de 30 minutos processando.......
[b:f1b4047068]O que preciso saber é se alguém tem uma idéia mais interessante para realizar a captura dos meses em atraso, ou se tem idéia de que tipo de índice eu possa usar (estou usando os campos da consulta à tabela PLANOSPAGAMENTOS indexados). Se alguém tiver uma idéia que melhore substancialmente a performance da SP, eu agradeço.[/b:f1b4047068]
Já pensei em cortar a inclusão de mês a mês das mensalidades na tabela temporária, incluindo somente a quantidade total de meses, mas a consulta à tabela PLANOSPAGAMENTOS irá permanecer a cada cliente e a cada mês, o que tem gerado mais de 79 milhões de fetches no caso do piloto que fiz com os 5 clientes apenas. Imagine!!!!
Segue, abaixo, a Análise de Desempenho do IBExpert usando a procedure com 5 clientes apenas:
Query ------------------------------------------------ Plan ------------------------------------------------ Adapted Plan ------------------------------------------------ Query Time ------------------------------------------------ Prepare : 0,00 ms Execute : 64.984,00 ms Avg fetch time: 0,00 ms Memory ------------------------------------------------ Current: 9.615.200 Max : 9.877.788 Buffers: 2.048 Operations ------------------------------------------------ Read : 615.265 Writes : 0 Fetches: 79.538.982 Enchanced Info: +--------------------------+-----------+-----------+-------------+---------+---------+---------+ | Table Name | Records | Indexed | Non-Indexed | Updates | Deletes | Inserts | | | Total | reads | reads | | | | +--------------------------+-----------+-----------+-------------+---------+---------+---------+ | CONTRATOS| 0 | 0 | 5 | 0 | 0 | 0 | | PLANOSPAGAMENTOS| 0 | 0 | 34896992 | 0 | 0 | 0 | | STATUS| 0 | 0 | 1 | 1 | 0 | 0 | +--------------------------+-----------+-----------+-------------+---------+---------+---------+
Query time: 1m 4s 984ms
Obrigado a todos pela atenção e, quando e conseguir a solução, posto aqui, caso ainda não tenham respondido.
Edson Cabral
Edsoncabral
Curtidas 0
Respostas
Mpcsharp2008
10/12/2008
ola,
acredito q um índice vai resolver seu problema
vc já tentou criar um índice no campo DATVENC1PARCELA? e fazer no for select um order by pelo campo no qual existe o índice? como abaixo:
for select DATVENC1PARCELA, CODIGO, CODLOJA, VALORMENSALIDADE
from CONTRATOS order by DATVENC1PARCELA into DATA1PARCELA, CONTRATO, LOJA, VALOR do
begin
....
se não der certo analiso com mais tempo
acredito q um índice vai resolver seu problema
vc já tentou criar um índice no campo DATVENC1PARCELA? e fazer no for select um order by pelo campo no qual existe o índice? como abaixo:
for select DATVENC1PARCELA, CODIGO, CODLOJA, VALORMENSALIDADE
from CONTRATOS order by DATVENC1PARCELA into DATA1PARCELA, CONTRATO, LOJA, VALOR do
begin
....
se não der certo analiso com mais tempo
GOSTEI 0
Mpcsharp2008
10/12/2008
ola,
analisando aqui não tem como vc filtrar mais a seguinte instrução?
select count(*) from PLANOSPAGAMENTOS where
(PLANOSPAGAMENTOS.ANOREFERENCIA = ANOREFERENCIA) and (PLANOSPAGAMENTOS.MESREFERENCIA = MESREFERENCIA)
into CONTADOR_PAG;
por exemplo da maneira q está ele está contando toda a tabela PLANOSPAGAMENTOS essa tabela não tem o código do cliente? pq aí vc acrescentaria no where ´and PLANOSPAGAMENTOS.CODCLIENTE = CODCLIENTE´
ele não iria ler todos os registros de todos os clientes
ve se entendi direito
um abraço
analisando aqui não tem como vc filtrar mais a seguinte instrução?
select count(*) from PLANOSPAGAMENTOS where
(PLANOSPAGAMENTOS.ANOREFERENCIA = ANOREFERENCIA) and (PLANOSPAGAMENTOS.MESREFERENCIA = MESREFERENCIA)
into CONTADOR_PAG;
por exemplo da maneira q está ele está contando toda a tabela PLANOSPAGAMENTOS essa tabela não tem o código do cliente? pq aí vc acrescentaria no where ´and PLANOSPAGAMENTOS.CODCLIENTE = CODCLIENTE´
ele não iria ler todos os registros de todos os clientes
ve se entendi direito
um abraço
GOSTEI 0
Edsoncabral
10/12/2008
ola,
analisando aqui não tem como vc filtrar mais a seguinte instrução?
select count(*) from PLANOSPAGAMENTOS where
(PLANOSPAGAMENTOS.ANOREFERENCIA = ANOREFERENCIA) and (PLANOSPAGAMENTOS.MESREFERENCIA = MESREFERENCIA)
into CONTADOR_PAG;
por exemplo da maneira q está ele está contando toda a tabela PLANOSPAGAMENTOS essa tabela não tem o código do cliente? pq aí vc acrescentaria no where ´and PLANOSPAGAMENTOS.CODCLIENTE = CODCLIENTE´
ele não iria ler todos os registros de todos os clientes
ve se entendi direito
um abraço
Bem, nessa tabela eu não tenho o código do cliente, mas tenho o código do contrato, que cada cliente possui o seu. Vou tentar e, com certeza, irá reduzir muito o número de fetches.
Valeu pela ajuda. Vou tentar isso logo.
Edson
GOSTEI 0
Edsoncabral
10/12/2008
ola,
analisando aqui não tem como vc filtrar mais a seguinte instrução?
select count(*) from PLANOSPAGAMENTOS where
(PLANOSPAGAMENTOS.ANOREFERENCIA = ANOREFERENCIA) and (PLANOSPAGAMENTOS.MESREFERENCIA = MESREFERENCIA)
into CONTADOR_PAG;
por exemplo da maneira q está ele está contando toda a tabela PLANOSPAGAMENTOS essa tabela não tem o código do cliente? pq aí vc acrescentaria no where ´and PLANOSPAGAMENTOS.CODCLIENTE = CODCLIENTE´
ele não iria ler todos os registros de todos os clientes
ve se entendi direito
um abraço
Não mudou praticamente nada.
Eu acrescentei no where o campo CONTRATO, entretanto o tempo de processamento foi praticamente o mesmo.
Acho que a demora se dá no loop que ele faz mês a mês.
Vou tenta criar uns índices para ver a eficiências deles na consulta.
Se tiver mais alguma idéia, passa aí...
Valeu.
GOSTEI 0
Edsoncabral
10/12/2008
Problema resolvido.
Eu mudei a stored procedure. Ao invés de fazer os loops mês a mês, eu criei uma consulta que informa apenas a quantidade de meses em débito e o valor total, sem detalhar quais os meses.
Caso o usuário deseje saber os meses, detalhadamente, deve consultar individualmente o contrato para não sobrecarregar a rede e o servidor.
mpcsharp2008, valeu a força.
Edson Cabral
Eu mudei a stored procedure. Ao invés de fazer os loops mês a mês, eu criei uma consulta que informa apenas a quantidade de meses em débito e o valor total, sem detalhar quais os meses.
Caso o usuário deseje saber os meses, detalhadamente, deve consultar individualmente o contrato para não sobrecarregar a rede e o servidor.
mpcsharp2008, valeu a força.
Edson Cabral
GOSTEI 0