Select em tabela com 492.792 registros muito lenta

Firebird

23/07/2018

Olá pessoa, tudo bem?
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

Sidney Abreu

Curtidas 0

Melhor post

Jerson Boer

Jerson Boer

23/07/2018

Olá Sidney, verifique se desta forma a consulta funciona pra você e trás os resultados de forma mais rápida:

 
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
GOSTEI 2

Mais Respostas

Emerson Nascimento

Emerson Nascimento

23/07/2018

tente assim:
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
GOSTEI 0
Sidney Abreu

Sidney Abreu

23/07/2018

Já criei os índices antes de postar aqui, mas continua lento, pq a tabela estoque eu registro todas as movimentações do item, e me um dia um item pode ter 100 movimentações, então no select eu pego a ultima movimentação de cada item.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

23/07/2018

Testou com a instrução que eu te passei?

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.
GOSTEI 0
Sidney Abreu

Sidney Abreu

23/07/2018

Se puder me ajudar agradeço muito.

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
GOSTEI 0
Sidney Abreu

Sidney Abreu

23/07/2018

Se puder me ajudar agradeço muito.

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
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

23/07/2018

Crie o seguinte índice:
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.
GOSTEI 0
Sidney Abreu

Sidney Abreu

23/07/2018

show de bola Emerson, agora leva uns 4segs, só não entendo o left join com select, vc faz um select no left join e dar um alias para o resultado estid, e em seguinda monta outro left join com o resultado do primeiro left join, seria isso?

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
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

23/07/2018

show de bola Emerson, agora leva uns 4segs, só não entendo o left join com select, vc faz um select no left join e dar um alias para o resultado estid, e em seguinda monta outro left join com o resultado do primeiro left join, seria isso?

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.
GOSTEI 0
Sidney Abreu

Sidney Abreu

23/07/2018

Já tinha feito a comparação, a diferença é de 5 min, existe algum livro sobre esses macetes? vc prestaria uma consultoria pra mim em meus selects?
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

23/07/2018

Pode adicionar no skype...
emerson.en
ou
emersonen@hotmail.com

(Emerson E Nascimento)
GOSTEI 0
POSTAR