Select em tabela com 492.792 registros muito lenta
23/07/2018
0
Me deparei com um problema de performance em um BD com Firebird 2.5 que atingiu uma quantidade de registros que eu não esperava.
Tenho uma tabela de estoque onde registro todas as movimentações do produto. Essa tabela hoje está com 492.792 registros e a consulta com sumarização ficou muito lenta, levando até 2 min. Já criei índices ascendentes e descendentes, mas melhorou muito pouco.
Vou precisar lista o estoque por mês durante um ano específico.
Ex: JAN FEV MAR ABR MAI...
1000 525 458 855 255
Fiz um select simples e o mesmo demora muito.
select prod_id, (select first 1 estoq_atual from estoque where ano = :ano and estoque.prod_id = produtos.prod_id order by estoq_id desc) as estoque from produtos order by prod_id
Resultado Local
Adapted Plan
PLAN (ESTOQUE ORDER ESTOQUE_IDX7 INDEX (ESTOQUE_IDX3, ESTOQUE_IDX1)) PLAN (PRODUTOS ORDER PK_PRODUTOS)
------ Performance info ------
Prepare time = 15ms
Execute time = 44s 695ms
Avg fetch time = 3.724,58 ms
Current memory = 2.493.468
Max memory = 2.664.676
Memory buffers = 384
Reads from disk to cache = 591.568
Writes from cache to disk = 0
Fetches from cache = 868.070
Estrutura da Tabela
CREATE TABLE ESTOQUE ( ESTOQ_ID INTEGER NOT NULL PRIMARY KEY, PROD_ID INTEGER, ESTOQ_DATA TIMESTAMP, ESTOQ_ORIGEM VARCHAR(40), ESTOQ_ANTERIOR NUMERIC(5,3) DEFAULT 0, ESTOQ_ENTRADA NUMERIC(5,3) DEFAULT 0, ESTOQ_SAIDA NUMERIC(5,3) DEFAULT 0, ESTOQ_MIN NUMERIC(5,3) DEFAULT 0, ESTOQ_MAX NUMERIC(5,3) DEFAULT 0, ESTOQ_ATUAL COMPUTED BY (ESTOQ_ANTERIOR +ESTOQ_ENTRADA - ESTOQ_SAIDA), USU_ID INTEGER, ESTOQ_VENCIDOS NUMERIC(5,3) DEFAULT 0, ESTOQ_AVARIA NUMERIC(5,3) DEFAULT 0, ESTOQ_NFE NUMERIC(5,3) DEFAULT 0, ESTOQ_REAL COMPUTED BY ((ESTOQ_AVARIA - ESTOQ_VENCIDOS) - ESTOQ_ATUAL), LOJ_ID INTEGER, ESTOQ_ACAO VARCHAR(60) COLLATE WIN_PTBR, CUSTO NUMERIC(12,2) DEFAULT 0 , ESTOQ_DATE DATE, ANO INTEGER, MES INTEGER, DIA INTEGER );
Alguém pode me dar um dica de como posso melhorar a performance?
Desde já agradeço.
Sidney Abreu
Post mais votado
23/07/2018
select prod_id, estoque.estoq_atual as estoque from produtos inner join estoque on estoque.prod_id = produtos.prod_id and ano = :ano order by prod_id
Jerson Boer
Mais Posts
23/07/2018
Emerson Nascimento
select prod.prod_id, coalesce(est.estoq_atual,0) estoque from produtos prod left join ( select prod_id, ano, max(estoq_id) estoq_id from estoque group by prod_id, ano ) estid on estid.prod_id = prod.prod_id and estid.ano = :ano left join estoque est on est.estoq_id = estid.estoq_id order by prod.prod_id
para melhorar a perfornance você precisará ter um índice na tabela estoque pelos campos prod_id, ano, mes, dia
23/07/2018
Sidney Abreu
24/07/2018
Emerson Nascimento
select prod.prod_id, coalesce(est.estoq_atual,0) estoque from produtos prod left join ( select prod_id, ano, max(estoq_id) estoq_id from estoque group by prod_id, ano ) estid on estid.prod_id = prod.prod_id and estid.ano = :ano left join estoque est on est.estoq_id = estid.estoq_id order by prod.prod_id
Se ainda estiver lento, disponibilize as duas tabelas com seus conteúdos pra que eu possa testar aqui.
24/07/2018
Sidney Abreu
Tabela
/******************************************************************************/ /*** Generated by IBExpert 24/07/2018 16:59:54 ***/ /******************************************************************************/ /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_ESTOQUE_ID; CREATE TABLE ESTOQUE ( ESTOQ_ID INTEGER NOT NULL, PROD_ID INTEGER, ESTOQ_DATA TIMESTAMP, ESTOQ_ORIGEM VARCHAR(40), ESTOQ_ANTERIOR NUMERIC(5,3) DEFAULT 0, ESTOQ_ENTRADA NUMERIC(5,3) DEFAULT 0, ESTOQ_SAIDA NUMERIC(5,3) DEFAULT 0, ESTOQ_MIN NUMERIC(5,3) DEFAULT 0, ESTOQ_MAX NUMERIC(5,3) DEFAULT 0, ESTOQ_ATUAL COMPUTED BY (ESTOQ_ANTERIOR +ESTOQ_ENTRADA - ESTOQ_SAIDA), USU_ID INTEGER, ESTOQ_VENCIDOS NUMERIC(5,3) DEFAULT 0, ESTOQ_AVARIA NUMERIC(5,3) DEFAULT 0, ESTOQ_NFE NUMERIC(5,3) DEFAULT 0, ESTOQ_REAL COMPUTED BY ((ESTOQ_AVARIA - ESTOQ_VENCIDOS) - ESTOQ_ATUAL), LOJ_ID INTEGER, ESTOQ_ACAO VARCHAR(60) COLLATE WIN_PTBR, CUSTO "DValor" DEFAULT 0 /* "DValor" = NUMERIC(12,2) DEFAULT 0 */, ESTOQ_DATE DATE, ANO INTEGER, MES INTEGER, DIA INTEGER ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE ESTOQUE ADD CONSTRAINT PK_ESTOQUE PRIMARY KEY (ESTOQ_ID); /******************************************************************************/ /*** Indices ***/ /******************************************************************************/ CREATE INDEX ESTOQUE_IDX1 ON ESTOQUE (ANO); CREATE INDEX ESTOQUE_IDX2 ON ESTOQUE (MES); CREATE INDEX ESTOQUE_IDX3 ON ESTOQUE (PROD_ID); CREATE INDEX ESTOQUE_IDX4 ON ESTOQUE (ESTOQ_DATA); CREATE INDEX ESTOQUE_IDX5 ON ESTOQUE (DIA); CREATE INDEX ESTOQUE_IDX6 ON ESTOQUE (LOJ_ID); CREATE DESCENDING INDEX ESTOQUE_IDX7 ON ESTOQUE (ESTOQ_ID); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: ESTOQUE_BI */ CREATE OR ALTER TRIGGER ESTOQUE_BI FOR ESTOQUE ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ESTOQ_ID IS NULL) THEN NEW.ESTOQ_ID = GEN_ID(GEN_ESTOQUE_ID,1); END ^ /* Trigger: TRG_ESTOQUE_DATA */ CREATE OR ALTER TRIGGER TRG_ESTOQUE_DATA FOR ESTOQUE ACTIVE BEFORE INSERT POSITION 0 AS begin IF (INSERTING) THEN BEGIN NEW.estoq_data = CURRENT_TIMESTAMP; NEW.estoq_date = CURRENT_DATE; new.custo = coalesce((select produtos.prod_valor_compra from produtos where produtos.prod_id = new.prod_id),0); new.dia = extract(day from CURRENT_DATE); new.ano = extract(year from CURRENT_DATE); END end ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/
O insert vou anexar no post
24/07/2018
Sidney Abreu
Tabela
/******************************************************************************/ /*** Generated by IBExpert 24/07/2018 16:59:54 ***/ /******************************************************************************/ /******************************************************************************/ /*** Tables ***/ /******************************************************************************/ CREATE GENERATOR GEN_ESTOQUE_ID; CREATE TABLE ESTOQUE ( ESTOQ_ID INTEGER NOT NULL, PROD_ID INTEGER, ESTOQ_DATA TIMESTAMP, ESTOQ_ORIGEM VARCHAR(40), ESTOQ_ANTERIOR NUMERIC(5,3) DEFAULT 0, ESTOQ_ENTRADA NUMERIC(5,3) DEFAULT 0, ESTOQ_SAIDA NUMERIC(5,3) DEFAULT 0, ESTOQ_MIN NUMERIC(5,3) DEFAULT 0, ESTOQ_MAX NUMERIC(5,3) DEFAULT 0, ESTOQ_ATUAL COMPUTED BY (ESTOQ_ANTERIOR +ESTOQ_ENTRADA - ESTOQ_SAIDA), USU_ID INTEGER, ESTOQ_VENCIDOS NUMERIC(5,3) DEFAULT 0, ESTOQ_AVARIA NUMERIC(5,3) DEFAULT 0, ESTOQ_NFE NUMERIC(5,3) DEFAULT 0, ESTOQ_REAL COMPUTED BY ((ESTOQ_AVARIA - ESTOQ_VENCIDOS) - ESTOQ_ATUAL), LOJ_ID INTEGER, ESTOQ_ACAO VARCHAR(60) COLLATE WIN_PTBR, CUSTO "DValor" DEFAULT 0 /* "DValor" = NUMERIC(12,2) DEFAULT 0 */, ESTOQ_DATE DATE, ANO INTEGER, MES INTEGER, DIA INTEGER ); /******************************************************************************/ /*** Primary Keys ***/ /******************************************************************************/ ALTER TABLE ESTOQUE ADD CONSTRAINT PK_ESTOQUE PRIMARY KEY (ESTOQ_ID); /******************************************************************************/ /*** Indices ***/ /******************************************************************************/ CREATE INDEX ESTOQUE_IDX1 ON ESTOQUE (ANO); CREATE INDEX ESTOQUE_IDX2 ON ESTOQUE (MES); CREATE INDEX ESTOQUE_IDX3 ON ESTOQUE (PROD_ID); CREATE INDEX ESTOQUE_IDX4 ON ESTOQUE (ESTOQ_DATA); CREATE INDEX ESTOQUE_IDX5 ON ESTOQUE (DIA); CREATE INDEX ESTOQUE_IDX6 ON ESTOQUE (LOJ_ID); CREATE DESCENDING INDEX ESTOQUE_IDX7 ON ESTOQUE (ESTOQ_ID); /******************************************************************************/ /*** Triggers ***/ /******************************************************************************/ SET TERM ^ ; /******************************************************************************/ /*** Triggers for tables ***/ /******************************************************************************/ /* Trigger: ESTOQUE_BI */ CREATE OR ALTER TRIGGER ESTOQUE_BI FOR ESTOQUE ACTIVE BEFORE INSERT POSITION 0 AS BEGIN IF (NEW.ESTOQ_ID IS NULL) THEN NEW.ESTOQ_ID = GEN_ID(GEN_ESTOQUE_ID,1); END ^ /* Trigger: TRG_ESTOQUE_DATA */ CREATE OR ALTER TRIGGER TRG_ESTOQUE_DATA FOR ESTOQUE ACTIVE BEFORE INSERT POSITION 0 AS begin IF (INSERTING) THEN BEGIN NEW.estoq_data = CURRENT_TIMESTAMP; NEW.estoq_date = CURRENT_DATE; new.custo = coalesce((select produtos.prod_valor_compra from produtos where produtos.prod_id = new.prod_id),0); new.dia = extract(day from CURRENT_DATE); new.ano = extract(year from CURRENT_DATE); END end ^ SET TERM ; ^ /******************************************************************************/ /*** Privileges ***/ /******************************************************************************/
O insert segue o link http://7master.com.br/ESTOQUE.sql
25/07/2018
Emerson Nascimento
CREATE INDEX ESTOQUE_IDX8 ON ESTOQUE (PROD_ID, ANO, ESTOQ_ID);
execute a instrução abaixo:
select prod.prod_id, coalesce(est.estoq_atual,0) estoque from produtos prod left join ( select prod_id, ano, max(estoq_id) estoq_id from estoque group by prod_id, ano ) estid on estid.prod_id = prod.prod_id and estid.ano = :ano left join estoque est on est.estoq_id = estid.estoq_id order by prod.prod_id
e publique plano de execução.
26/07/2018
Sidney Abreu
Plan
PLAN JOIN (JOIN (PROD ORDER PK_PRODUTOS INDEX (PRODUTOS_IDX4), ESTID ESTOQUE ORDER ESTOQUE_IDX8 INDEX (ESTOQUE_IDX8)), EST INDEX (PK_ESTOQUE))
Adapted Plan
PLAN JOIN (JOIN (PROD ORDER PK_PRODUTOS INDEX (PRODUTOS_IDX4), ESTID ESTOQUE ORDER ESTOQUE_IDX8 INDEX (ESTOQUE_IDX8)), EST INDEX (PK_ESTOQUE))
------ Performance info ------
Prepare time = 15ms
Execute time = 281ms
Avg fetch time = 31,22 ms
Current memory = 2.568.892
Max memory = 33.953.920
Memory buffers = 384
Reads from disk to cache = 90.428
Writes from cache to disk = 0
Fetches from cache = 336.424
26/07/2018
Emerson Nascimento
a instrução
select prod_id, ano, max(estoq_id) estoq_id from estoque group by prod_id, ano
irá trazer o id da última movimentação do ano para cada produto. assim eu posiciono no produto correto e ano desejado e obtenho o id da última movimentação daquele produto; a partir daí fica mais fácil efetuar a busca no último left join, porque utilizará a chave primária da tabela. fiz desta forma porque acho fica mais fácil o entendimento.
na verdade o segredo está na criação do índice, como eu havia falado no meu primeiro post.
agora, por curiosidade - e com o índice criado - execute aquela sua primeira instrução e veja como será a performance.
26/07/2018
Sidney Abreu
26/07/2018
Emerson Nascimento
emerson.en
ou
emersonen@hotmail.com
(Emerson E Nascimento)
Clique aqui para fazer login e interagir na Comunidade :)