Fórum Consulta Demorada #55658

31/03/2006

0

Firebird

Tenho a seguinte consulta para um relatório.
O objetivo é determinar o estoque de cada produto em uma determinada data passada.
Funcionar funciona, mas está muito, mas muito demorada. O banco de dados tem somente 15 mil itens.
Estou fazendo algo errado? Há uma maneira de melhorar a performance da consulta?

SQLDATASET1.CommandText := ´SELECT P.REFERENCIA, P.DESCRICAO, P.UNIDADE, P.MARCA, P.ATUAL, CASE ME.OPERACAO WHEN ´+´´´E´´´+´ THEN SUM(ME.QUANTIDADE) END AS ENTRADAS, CASE ME.OPERACAO WHEN ´+´´´S´´´+´ THEN SUM(ME.QUANTIDADE) END AS SAIDAS FROM PRODUTOS P LEFT JOIN MOVIESTOQUE ME ON (ME.PRODUTO = P.REFERENCIA AND ME.DATA >:VDataPosicao) ORDER BY P.REFERENCIA´

O campo REFERENCIA é o índice primário no cadastro de produtos e é do tipo Varchar(40).
Firebird 1.5.3
Delphi 7


Valdirdill

Valdirdill

Responder

Posts

04/04/2006

Armando.boza

Não seria melhor montar isso ai numa Stored Procedure ?

se quiser tem esse exemplo básico que da pra tirar umas ideias, ele funciona como se fosse um extrato de banco.

SET TERM ^ ;

CREATE PROCEDURE SP_KARDEX (
    N_LOCAL INTEGER,
    N_EMP INTEGER,
    N_PROD INTEGER,
    DATAINICIAL DATE,
    DATAFINAL DATE)
RETURNS (
    DATA DATE,
    NMOV VARCHAR(30),
    DESCRICAO VARCHAR(80),
    DOCUMENTO VARCHAR(30),
    QUANTIDADE NUMERIC(15,2),
    SALDO NUMERIC(15,2),
    TIPO CHAR(1))
AS
DECLARE VARIABLE CREDITOS NUMERIC(15,2);
DECLARE VARIABLE DEBITOS NUMERIC(15,2);
DECLARE VARIABLE SALDOANT NUMERIC(15,2);
begin
  /*Montando a primeira linha do kardex com o Saldo Anterior*/
  DATA = :datainicial;
  DESCRICAO = ´SALDO ANTERIOR´;
  documento = ´´;
  SELECT SUM(QUANTPROD) FROM MOVPRODUTO M
 WHERE M.n_local = :n_local
 AND M.n_emp = :n_emp 
 AND M.n_prod = :n_prod 
 AND M.tipomov = ´E´ 
 AND M.datamov < :datainicial
  into :creditos;
  if (creditos is null) then creditos = 0;
  SELECT SUM(QUANTPROD) FROM MOVPRODUTO M 
 WHERE M.n_local = :n_local 
 AND M.n_emp = :n_emp 
 AND M.n_prod = :n_prod 
 AND M.tipomov = ´S´ 
 AND M.datamov < :datainicial
  into :debitos;
  if (debitos is null) then debitos = 0;
  saldo = creditos - debitos;
  if (SALDO < 0) THEN
  BEGIN
    SALDO = SALDO * (-1);
    tipo = ´S´;
  END
  ELSE tipo = ´E´;
  suspend;
 
  data = null;
  nmov = null;
  descricao = null;
  documento = null;
  quantidade = null;
  saldoant = saldo;
  saldo = null;
  /*Fim do Saldo Anterior*/

  /*Verificando as movimentações do produto*/
  FOR SELECT M.DATAMOV,CASE M.descrimov 
WHEN ´NOTA FISCAL´ THEN(select NUMERONF FROM NOTAFISCAL N WHERE M.n_req = N.n_nf) 
WHEN ´REQUISIÇÃO´ THEN(select N_REQ FROM REQUISICAO R WHERE M.n_req = R.n_req) 
WHEN ´BALANÇO´ THEN(select N_BAL FROM BALANCO B WHERE M.n_req = B.n_bal) 
WHEN ´AJUSTE´ THEN(select N_AJU FROM AJUSTE A WHERE M.n_req = A.n_aju) 
END AS "NMOVIMENTO",CASE M.tipomov 
WHEN ´E´ THEN(select RAZAOFORN FROM FORNECEDOR F,NOTAFISCAL N WHERE M.n_req = N.n_nf AND N.n_forn = F.n_forn) 
WHEN ´S´ THEN(select NOMECLI FROM CLIENTE C,REQUISICAO R WHERE M.n_req = R.n_req AND R.n_cli = C.n_cli) 
END AS "Nome/Razão Social",M.descrimov,M.QUANTPROD,M.TIPOMOV
 FROM MOVPRODUTO M 
WHERE M.n_local = :n_local 
AND M.n_emp = :n_emp 
AND M.n_prod = :n_prod 
AND (M.datamov BETWEEN :datainicial AND :datafinal)
      ORDER BY 1
      INTO :data, :nmov ,:descricao, :documento, :quantidade, :tipo DO
      BEGIN
         if (not tipo is null) then begin
            if (tipo = ´E´) then SALDO = SALDOANT + QUANTIDADE;
            if (tipo = ´S´) then SALDO = SALDOANT - QUANTIDADE;
            saldoant = saldo;
            suspend;
            data = null;
            nmov = null;
            descricao = null;
            documento = null;
            quantidade = null;
            tipo = null;
         end
      end
      data = datafinal;
      descricao = ´SALDO´;
      documento = ´´;
      saldo = saldoant;
      suspend;
   end^

SET TERM ; ^



Responder

Gostei + 0

04/04/2006

Armindo

Não seria melhor montar isso ai numa Stored Procedure ? se quiser tem esse exemplo básico que da pra tirar umas ideias, ele funciona como se fosse um extrato de banco. 
SET TERM ^ ;

CREATE PROCEDURE SP_KARDEX (
    N_LOCAL INTEGER,
    N_EMP INTEGER,
    N_PROD INTEGER,
    DATAINICIAL DATE,
    DATAFINAL DATE)
RETURNS (
    DATA DATE,
    NMOV VARCHAR(30),
    DESCRICAO VARCHAR(80),
    DOCUMENTO VARCHAR(30),
    QUANTIDADE NUMERIC(15,2),
    SALDO NUMERIC(15,2),
    TIPO CHAR(1))
AS
DECLARE VARIABLE CREDITOS NUMERIC(15,2);
DECLARE VARIABLE DEBITOS NUMERIC(15,2);
DECLARE VARIABLE SALDOANT NUMERIC(15,2);
begin
  /*Montando a primeira linha do kardex com o Saldo Anterior*/
  DATA = :datainicial;
  DESCRICAO = ´SALDO ANTERIOR´;
  documento = ´´;
  SELECT SUM(QUANTPROD) FROM MOVPRODUTO M
 WHERE M.n_local = :n_local
 AND M.n_emp = :n_emp 
 AND M.n_prod = :n_prod 
 AND M.tipomov = ´E´ 
 AND M.datamov < :datainicial
  into :creditos;
  if (creditos is null) then creditos = 0;
  SELECT SUM(QUANTPROD) FROM MOVPRODUTO M 
 WHERE M.n_local = :n_local 
 AND M.n_emp = :n_emp 
 AND M.n_prod = :n_prod 
 AND M.tipomov = ´S´ 
 AND M.datamov < :datainicial
  into :debitos;
  if (debitos is null) then debitos = 0;
  saldo = creditos - debitos;
  if (SALDO < 0) THEN
  BEGIN
    SALDO = SALDO * (-1);
    tipo = ´S´;
  END
  ELSE tipo = ´E´;
  suspend;
 
  data = null;
  nmov = null;
  descricao = null;
  documento = null;
  quantidade = null;
  saldoant = saldo;
  saldo = null;
  /*Fim do Saldo Anterior*/

  /*Verificando as movimentações do produto*/
  FOR SELECT M.DATAMOV,CASE M.descrimov 
WHEN ´NOTA FISCAL´ THEN(select NUMERONF FROM NOTAFISCAL N WHERE M.n_req = N.n_nf) 
WHEN ´REQUISIÇÃO´ THEN(select N_REQ FROM REQUISICAO R WHERE M.n_req = R.n_req) 
WHEN ´BALANÇO´ THEN(select N_BAL FROM BALANCO B WHERE M.n_req = B.n_bal) 
WHEN ´AJUSTE´ THEN(select N_AJU FROM AJUSTE A WHERE M.n_req = A.n_aju) 
END AS "NMOVIMENTO",CASE M.tipomov 
WHEN ´E´ THEN(select RAZAOFORN FROM FORNECEDOR F,NOTAFISCAL N WHERE M.n_req = N.n_nf AND N.n_forn = F.n_forn) 
WHEN ´S´ THEN(select NOMECLI FROM CLIENTE C,REQUISICAO R WHERE M.n_req = R.n_req AND R.n_cli = C.n_cli) 
END AS "Nome/Razão Social",M.descrimov,M.QUANTPROD,M.TIPOMOV
 FROM MOVPRODUTO M 
WHERE M.n_local = :n_local 
AND M.n_emp = :n_emp 
AND M.n_prod = :n_prod 
AND (M.datamov BETWEEN :datainicial AND :datafinal)
      ORDER BY 1
      INTO :data, :nmov ,:descricao, :documento, :quantidade, :tipo DO
      BEGIN
         if (not tipo is null) then begin
            if (tipo = ´E´) then SALDO = SALDOANT + QUANTIDADE;
            if (tipo = ´S´) then SALDO = SALDOANT - QUANTIDADE;
            saldoant = saldo;
            suspend;
            data = null;
            nmov = null;
            descricao = null;
            documento = null;
            quantidade = null;
            tipo = null;
         end
      end
      data = datafinal;
      descricao = ´SALDO´;
      documento = ´´;
      saldo = saldoant;
      suspend;
   end^

SET TERM ; ^


Obrigado, mas nunca usei SP. Pretendo começar a usar, pois me parece ser uma boa opção.
Será que você poderia me ajudar a montar essa SP para fazer o que preciso?
Obrigado


Responder

Gostei + 0

04/04/2006

Armando.boza

não sou muito bom em SPs mas dou pro gasto .. kkkk

coloca ai a estrutura das tabelas pra podermos ajudar vc.

o nosso amigo [b:57cc4d70a9]emerson.en[/b:57cc4d70a9] manja do assunto, com certeza ele tb ajudará.

att


Responder

Gostei + 0

04/04/2006

Armindo

não sou muito bom em SPs mas dou pro gasto .. kkkk coloca ai a estrutura das tabelas pra podermos ajudar vc. o nosso amigo [b:9ab4ef3f4a]emerson.en[/b:9ab4ef3f4a] manja do assunto, com certeza ele tb ajudará. att


Tabela MoviEstoque
- Produto - VarChar(12)
- Data - Date
- Operacao - Char(1)
- Quantidade - Numeric(18,2)


Tabela Produtos
- Referencia - VarChar(12)
- Descricao - VarChar(40)
- Unidade - VarChar(3)
- Marca - VarChar(30)
- Atual - Numeric(18,2)

Bom, teria mais campos, mas acho que esses são os que interessam.
Obrigado.


Responder

Gostei + 0

04/04/2006

Valdirdill

E ae? Alguma idéia?


Responder

Gostei + 0

05/04/2006

Armando.boza

E ae? Alguma idéia?


tenta essa.

SET TERM ^ ;

CREATE PROCEDURE SP_EXTRATO (
    PRODUTO VARCHAR(12),
    DATAINICIAL DATE,
    DATAFINAL DATE)
RETURNS (
    DATA DATE,
    REFERENCIA VARCHAR(40),
    DESCRICAO VARCHAR(12),
    UNIDADE VARCHAR(3),
    MARCA VARCHAR(30),
    QUANTIDADE NUMERIC(18,2),
    SALDO NUMERIC(18,2),
    OPERACAO CHAR(1))
AS
DECLARE VARIABLE CREDITOS NUMERIC(18,2);
DECLARE VARIABLE DEBITOS NUMERIC(18,2);
DECLARE VARIABLE SALDOANT NUMERIC(18,2);
begin
  
  /*Montando a primeira linha do kardex com o Saldo Anterior*/
  DATA = :datainicial;
  DESCRICAO = ´SALDO ANTERIOR´;
  SELECT SUM(QUANTIDADE) FROM MOVIESTOQUE M
  WHERE M.PRODUTO = :produto
  AND M.operacao = ´E´
  AND M.data < :datainicial
  into :creditos;
  if (creditos is null) then creditos = 0;
  
  SELECT SUM(QUANTIDADE) FROM MOVIESTOQUE M
  WHERE M.PRODUTO = :produto
  AND M.operacao = ´S´
  AND M.data < :datainicial
  into :debitos;
  if (debitos is null) then debitos = 0;
  
  saldo = creditos - debitos;
  if (SALDO < 0) THEN
  BEGIN
    SALDO = SALDO * (-1);
    operacao = ´S´;
  END
  ELSE operacao = ´E´;
  suspend;
     
    
  data = null;
  referencia = null;
  descricao = null;
  unidade = null;
  quantidade = null;
  marca = null;
  saldoant = saldo;
  saldo = null;
  /*Fim do Saldo Anterior*/


  /*Verificando as movimentações do produto*/
  FOR SELECT M.DATA, M.PRODUTO, P.DESCRICAO, P.UNIDADE, P.MARCA, M.QUANTIDADE, M.OPERACAO
  FROM MOVIESTOQUE M
  WHERE M.PRODUTO = :produto
  AND (M.data BETWEEN :datainicial AND :datafinal)
      ORDER BY 1
      INTO :data, :referencia, :descricao, :unidade, :marca, :quantidade, :operacao DO
      BEGIN
         if (not operacao is null) then begin
            if (operacao = ´E´) then SALDO = SALDOANT + QUANTIDADE;
            if (operacao = ´S´) then SALDO = SALDOANT - QUANTIDADE;
            saldoant = saldo;
            suspend;
            data = null;
            referencia = null;
            descricao = null;
            unidade = null;
            marca = null;
            quantidade = null;
            operacao = null;
         end
      end
      data = datafinal;
      referencia = ´SALDO´;
      saldo = saldoant;
      suspend;
   end^

SET TERM ; ^



Responder

Gostei + 0

05/04/2006

Emerson Nascimento

tente assim:
SELECT
  P.REFERENCIA, P.DESCRICAO, P.UNIDADE, P.MARCA, P.ATUAL,
  SUM(CASE ME.OPERACAO WHEN ´E´ THEN COALESCE(ME.QUANTIDADE,0) ELSE 0 END) AS ENTRADAS,
  SUM(CASE ME.OPERACAO WHEN ´S´ THEN COALESCE(ME.QUANTIDADE,0) ELSE 0 END) AS SAIDAS
FROM
  PRODUTOS P
LEFT JOIN
  MOVIESTOQUE ME
  ON (ME.PRODUTO = P.REFERENCIA AND ME.DATA > :VDataPosicao)
GROUP BY
  P.REFERENCIA, P.DESCRICAO, P.UNIDADE, P.MARCA, P.ATUAL
ORDER BY
  P.REFERENCIA
na tabela [b:3e82174f77]MoviEstoque[/b:3e82174f77] você deveria ter um indice por:
Produto, Data, Operacao


ou, via stored procedure:
SET TERM ^ ; 

CREATE PROCEDURE SP_KARDEX(
    DATAPOSICAO DATE,
    REFERENCIAENTRADA VARCHAR(40))
RETURNS (
    REFERENCIA VARCHAR(40), 
    DESCRICAO VARCHAR(40),
    UNIDADE VARCHAR(3),
    MARCA VARCHAR(30),
    DATAPOSICAO DATE,
    SALDOANTERIOR NUMERIC(18,2),
    ENTRADAS NUMERIC(18,2),
    SAIDAS NUMERIC(18,2))
    SALDOATUAL NUMERIC(18,2),
AS 
begin
  -- se não for passada uma referência de produto,
  -- traz todos os produtos
  if (referenciaentrada is null) then referenciaentrada = ´¬´;

  -- seleciona todos os produtos
  for select
        referencia, descricao, unidade, marca, atual
      from
        produto
      where
        referencia like :referenciaentrada
  into :referencia, :descricao, :unidade, :marca, :saldoatual do
  begin
    -- soma as entradas e saídas do produto no período ANTERIOR à data informada
    select
      sum(case operacao = ´E´ then coalesce(QUANTIDADE,0) else 0 end),
      sum(case operacao = ´S´ then coalesce(QUANTIDADE,0) else 0 end)
    from
      MoviEstoque
    where
      produto = :referencia
      and data <=  :dataposicao
    into :entradas, :saidas;

    saldoanterior = entradas - saidas;

    -- soma as entradas e saídas do produto no período POSTERIOR ao informado
    select
      sum(case operacao = ´E´ then coalesce(QUANTIDADE,0) else 0 end),
      sum(case operacao = ´S´ then coalesce(QUANTIDADE,0) else 0 end)
    from
      MoviEstoque
    where
      produto = :referencia
      and data > :dataposicao
    into :entradas, :saidas;

    suspend;
  end

end^ 

SET TERM ; ^



Responder

Gostei + 0

05/04/2006

Valdirdill

Obrigado pela resposta.
Estou tentando executar o script para criar a SP, mas dá erro Dynamic SQL Error - Token Unknow na linha ´ into :creditos;´ -


Responder

Gostei + 0

06/04/2006

Valdirdill

tente assim: 
SELECT
  P.REFERENCIA, P.DESCRICAO, P.UNIDADE, P.MARCA, P.ATUAL,
  SUM(CASE ME.OPERACAO WHEN ´E´ THEN COALESCE(ME.QUANTIDADE,0) ELSE 0 END) AS ENTRADAS,
  SUM(CASE ME.OPERACAO WHEN ´S´ THEN COALESCE(ME.QUANTIDADE,0) ELSE 0 END) AS SAIDAS
FROM
  PRODUTOS P
LEFT JOIN
  MOVIESTOQUE ME
  ON (ME.PRODUTO = P.REFERENCIA AND ME.DATA > :VDataPosicao)
GROUP BY
  P.REFERENCIA, P.DESCRICAO, P.UNIDADE, P.MARCA, P.ATUAL
ORDER BY
  P.REFERENCIA
na tabela [b:661f11bfb1]MoviEstoque[/b:661f11bfb1] você deveria ter um indice por: Produto, Data, Operacao ou, via stored procedure: 
SET TERM ^ ; 

CREATE PROCEDURE SP_KARDEX(
    DATAPOSICAO DATE,
    REFERENCIAENTRADA VARCHAR(40))
RETURNS (
    REFERENCIA VARCHAR(40), 
    DESCRICAO VARCHAR(40),
    UNIDADE VARCHAR(3),
    MARCA VARCHAR(30),
    DATAPOSICAO DATE,
    SALDOANTERIOR NUMERIC(18,2),
    ENTRADAS NUMERIC(18,2),
    SAIDAS NUMERIC(18,2))
    SALDOATUAL NUMERIC(18,2),
AS 
begin
  -- se não for passada uma referência de produto,
  -- traz todos os produtos
  if (referenciaentrada is null) then referenciaentrada = ´¬´;

  -- seleciona todos os produtos
  for select
        referencia, descricao, unidade, marca, atual
      from
        produto
      where
        referencia like :referenciaentrada
  into :referencia, :descricao, :unidade, :marca, :saldoatual do
  begin
    -- soma as entradas e saídas do produto no período ANTERIOR à data informada
    select
      sum(case operacao = ´E´ then coalesce(QUANTIDADE,0) else 0 end),
      sum(case operacao = ´S´ then coalesce(QUANTIDADE,0) else 0 end)
    from
      MoviEstoque
    where
      produto = :referencia
      and data <=  :dataposicao
    into :entradas, :saidas;

    saldoanterior = entradas - saidas;

    -- soma as entradas e saídas do produto no período POSTERIOR ao informado
    select
      sum(case operacao = ´E´ then coalesce(QUANTIDADE,0) else 0 end),
      sum(case operacao = ´S´ then coalesce(QUANTIDADE,0) else 0 end)
    from
      MoviEstoque
    where
      produto = :referencia
      and data > :dataposicao
    into :entradas, :saidas;

    suspend;
  end

end^ 

SET TERM ; ^


Com o select direto funciona. O problema é que preciso filtrar para que me retorne somente os produtos que tenham P.ATUAL > 0 em VDataPosicao. Do jeito que está esse select, se eu acrescentar um ´Where P.ATUAL > 0´ serão listados os produtos com saldo positivo na data atual e não em VDataPosicao, entendeu?


Tentei executar a SP, mas dá erro Dynamic SQL Error - Token Unknow na linha ´ into :creditos;´ -

Pode ajudar?
Obrigado.


Responder

Gostei + 0

Utilizamos cookies para fornecer uma melhor experiência para nossos usuários, consulte nossa política de privacidade.

Aceitar