Discussoes de Programacao e Tecnologia

SQL lento, na hora de gerar o relatorio

2 respostas
T
Testes_Atenas

Estou gerando o relatório na empresa referente a um balanço de estoque, mas a query do SQL esta sobrecarregada, quando vou rodar ele(relatório) com a opção de pendente junto ele trava o Banco e a Aplicação
segue o código de SQL da query, Identifiquei algo relacionado a lentidão nos sub selects referentes a quando entra no POR_PENDENTE.
Mas não consegui fazer ela rodar sem travar.
Se alguém souber alguma dica, ou algo que pode me ajudar agradeço!

SELECT
     TME.COD_PRODUTO,
     TME.COD_MOVIMENTO_ESTOQUE,
     TME.DT_MOVIMENTO, 
     TME.COD_USUARIO_LOGADO,
     TME.VLR_CUSTO, 
     TME.IND_TIPO_MOVIMENTO, 
     TME.QDE_MOVIMENTO, 
     TME.QDE_ESTOQUE_ATUAL,
     TPRODUTO.DESC_PRODUTO,
     TPRODUTO.DESC_CODIGO_MANUAL, 
  COALESCE((CASE
     WHEN (TOS.DESC_TITULO IS NULL) THEN
       CAST(TOS.COD_ORDEM_SERVICO AS VARCHAR(10)) || ' - ' || TC.DESC_CADASTRO || ' (' || COALESCE(TME.DESC_OBSERVACAO,'') || ')' 
     ELSE
       CAST(TOS.COD_ORDEM_SERVICO AS VARCHAR(10)) || ' - ' || TC.DESC_CADASTRO || ' (' || COALESCE(TOS.DESC_TITULO,'') || ')' || ' (' || COALESCE(TME.DESC_OBSERVACAO,'') || ')'
  END), TME.DESC_OBSERVACAO) AS OBSERVACAO,
      TME.COD_FILIAL,
      (SELECT F.DESC_FILIAL FROM TB_FILIAL F WHERE F.COD_FILIAL = TME.COD_FILIAL) AS DESC_FILIAL
FROM
      TBLJ_MOVIMENTO_ESTOQUE TME LEFT JOIN TBLJ_ORDEM_SERVICO TOS LEFT JOIN TBLJ_CADASTRO TC ON TC.COD_CADASTRO = TOS.COD_CADASTRO
                                                                   ON TOS.COD_ORDEM_SERVICO = TME.COD_ORDEM_SERVICO,
      TBLJ_PRODUTO TPRODUTO      
WHERE
      TME.COD_PRODUTO = TPRODUTO.COD_PRODUTO
      AND
     (:POR_COD_MANUAL = -1
     OR
     UPPER(TPRODUTO.DESC_CODIGO_MANUAL) LIKE UPPER(:COD_MANUAL))
  AND
     (:COD_PRODUTO = -1
     OR
     TME.COD_PRODUTO = :COD_PRODUTO)
     AND
     (:COD_ORDEM_SERVICO = -1
     OR
     TME.COD_ORDEM_SERVICO = :COD_ORDEM_SERVICO) 
     AND
     (:POR_DT_INICIAL = -1
     OR
     TME.DT_MOVIMENTO >= :DT_INICIAL)
     AND
     (:POR_DT_FINAL = -1
     OR
     TME.DT_MOVIMENTO <= :DT_FINAL)
     AND
     (:POR_MARCA = -1
     OR
     TPRODUTO.DESC_MARCA LIKE :DESC_MARCA)
     AND
     (:POR_TIPO_PRODUTO = -1
     OR
     TPRODUTO.IND_TIPO_PRODUTO LIKE :IND_TIPO_PRODUTO)
     AND
     (:POR_NUMERO = -1
     OR
     TPRODUTO.DESC_NUMERO LIKE :DESC_NUMERO)
     AND
     (:POR_COLECAO = -1
     OR
     TPRODUTO.DESC_COLECAO LIKE :DESC_COLECAO)
     AND
     (:POR_CATEGORIA = -1
     OR
     TPRODUTO.DESC_CATEGORIA LIKE :DESC_CATEGORIA)
     AND
     (:POR_COR = -1
     OR
     TPRODUTO.DESC_COR LIKE :DESC_COR)
     AND
     (:POR_SESSAO = -1
     OR
     TPRODUTO.DESC_SESSAO LIKE :DESC_SESSAO)
     AND
     (:POR_FILIAL = -1
     OR
     TME.COD_FILIAL = :COD_FILIAL)
     AND
     (:POR_SAIDA = -1
      OR
      (TME.IND_TIPO_MOVIMENTO = 'SAIDA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'VENDA FATURADA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'RECEITUARIO FAT'
      OR
      TME.IND_TIPO_MOVIMENTO = 'OS'
      OR
      TME.IND_TIPO_MOVIMENTO = 'OS FATURADA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'DEV-TRO'
      OR
      TME.IND_TIPO_MOVIMENTO = 'TRANSF-SAI'
      OR
      TME.IND_TIPO_MOVIMENTO = 'PRODUCAO'
      OR
      TME.IND_TIPO_MOVIMENTO = 'SAI CONDICIONAL'
      OR
      TME.IND_TIPO_MOVIMENTO = 'EXC CONDICIONAL'
      OR
      TME.IND_TIPO_MOVIMENTO = 'COMPRA-EST'))
     AND
     (:POR_ENTRADA = -1
      OR
      (TME.IND_TIPO_MOVIMENTO = 'SLD_INI'
      OR
      TME.IND_TIPO_MOVIMENTO = 'ENTRADA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'COMPRA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'VENDA ESTORNADA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'OS ESTORNADA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'RECEITUARIO EST'
      OR
      TME.IND_TIPO_MOVIMENTO = 'DEV-PRO'
      OR
      TME.IND_TIPO_MOVIMENTO = 'DEV-CLI'
      OR
      TME.IND_TIPO_MOVIMENTO = 'TRANSF-ENT'
      OR
      TME.IND_TIPO_MOVIMENTO = 'PRODUZIDO'
      OR
      TME.IND_TIPO_MOVIMENTO = 'BAI CONDICIONAL'))
     AND
     (:POR_TRANSFERENCIA = -1
      OR
      (TME.IND_TIPO_MOVIMENTO = 'TRANSF-SAI'
      OR
      TME.IND_TIPO_MOVIMENTO = 'TRANSF-ENT')
     AND
     (:POR_CONSUMO = -1
      OR
      (TME.IND_TIPO_MOVIMENTO = 'OFICINA' OR TME.IND_TIPO_MOVIMENTO = 'CONSUMO'))
     AND
     (:POR_RETORNO = -1
      OR
      TME.IND_TIPO_MOVIMENTO = 'RETORNO')
  )
  AND
  (:POR_PENDENTE = -1
  OR
  (
      TME.IND_TIPO_MOVIMENTO = 'SLD_INI'
      OR
      TME.IND_TIPO_MOVIMENTO = 'SAIDA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'ENTRADA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'OFICINA'
      OR
      TME.IND_TIPO_MOVIMENTO = 'CONSUMO'
      OR
      (
        TME.IND_TIPO_MOVIMENTO = 'SAI CONDICIONAL'
        AND
        TME.COD_CONDICIONAL_PRODUTO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_CONDICIONAL_PRODUTO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_CONDICIONAL_PRODUTO = TME.COD_CONDICIONAL_PRODUTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'BAI CONDICIONAL'
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'VENDA FATURADA')
        AND
        TME.COD_VENDA_PRODUTO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_VENDA_PRODUTO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_VENDA_PRODUTO = TME.COD_VENDA_PRODUTO
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'VENDA ESTORNADA')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'RECEITUARIO FAT')
        AND
        TME.COD_RECEITUARIO_OTICA_PROD NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_RECEITUARIO_OTICA_PROD
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_RECEITUARIO_OTICA_PROD = TME.COD_RECEITUARIO_OTICA_PROD
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'RECEITUARIO EST')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'OS FATURADA')
        AND
        TME.COD_ORDEM_SERVICO_PRODUTO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_ORDEM_SERVICO_PRODUTO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_ORDEM_SERVICO_PRODUTO = TME.COD_ORDEM_SERVICO_PRODUTO
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'OS ESTORNADA')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'DEV-FOR-SAI')
        AND
        TME.COD_DEVOLUCAO_FORNECEDOR NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_DEVOLUCAO_FORNECEDOR
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_DEVOLUCAO_FORNECEDOR = TME.COD_DEVOLUCAO_FORNECEDOR
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'DEV-FOR-ENT')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'DEV-CLI-SAI')
        AND
        TME.COD_DEVOLUCAO_CLIENTE NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_DEVOLUCAO_CLIENTE
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_DEVOLUCAO_CLIENTE = TME.COD_DEVOLUCAO_CLIENTE
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'DEV-CLI-ENT')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'TRANSF-SAI')
        AND
        TME.COD_TRANSFERENCIA_PRODUTO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_TRANSFERENCIA_PRODUTO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_TRANSFERENCIA_PRODUTO = TME.COD_TRANSFERENCIA_PRODUTO
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'TRANSF-ENT')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'TRANSF-ENT')
        AND
        TME.COD_TRANSFERENCIA_PRODUTO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_TRANSFERENCIA_PRODUTO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_TRANSFERENCIA_PRODUTO = TME.COD_TRANSFERENCIA_PRODUTO
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'TRANSF-SAI')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'ENTREGA FAT')
        AND
        TME.COD_ENTREGA_PRODUTO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_ENTREGA_PRODUTO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_ENTREGA_PRODUTO = TME.COD_ENTREGA_PRODUTO
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'ENTREGA EST')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'PRODUCAO-SAI')
        AND
        TME.COD_PRODUCAO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_PRODUCAO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_PRODUCAO = TME.COD_PRODUCAO
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'PRODUCAO-ENT')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
      OR
      (
        (TME.IND_TIPO_MOVIMENTO = 'COMPRA')
        AND
        TME.COD_COMPRA_PRODUTO NOT IN
        (SELECT
           TBLJ_MOVIMENTO_ESTOQUE.COD_COMPRA_PRODUTO
         FROM
           TBLJ_MOVIMENTO_ESTOQUE
         WHERE
           TBLJ_MOVIMENTO_ESTOQUE.COD_COMPRA_PRODUTO = TME.COD_COMPRA_PRODUTO
           AND
           (TBLJ_MOVIMENTO_ESTOQUE.IND_TIPO_MOVIMENTO = 'COMPRA-EST')
           AND
           TBLJ_MOVIMENTO_ESTOQUE.DT_MOVIMENTO >= TME.DT_MOVIMENTO
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_MOVIMENTO_ESTOQUE > TME.COD_MOVIMENTO_ESTOQUE
           AND
           TBLJ_MOVIMENTO_ESTOQUE.COD_FILIAL = TME.COD_FILIAL)
      )
  )
  )
ORDER BY 
      TME.COD_PRODUTO, TME.COD_MOVIMENTO_ESTOQUE

segue código

Alura Desenvolvimento Back-End Java Sua Carreira em desenvolvimento back-end Java: dos fundamentos à arquitetura de sistemas...

2 Respostas

javaflex
javaflex

Monte o SQL conforme os filtros escolhidos pelo usuário e não uma instrução gigante adaptável a todos os filtros usando ORs. Com a instrução mais enxuta fica mais fácil refinar e chegar no ponto exato do problema, com isso analise o plano de execução, onde de repente vai resolver criando índice para o ponto crítico. Além disso, pergunte para o cliente quais são os filtros realmente necessários, para não aumentar a complexidade do sistema a toa.

Não que vá melhorar performance, mas revise também esse bando de ORs que poderiam ser um IN:

TME.IND_TIPO_MOVIMENTO = 'OS ESTORNADA' OR TME.IND_TIPO_MOVIMENTO = 'RECEITUARIO EST' OR TME.IND_TIPO_MOVIMENTO = 'DEV-PRO' OR TME.IND_TIPO_MOVIMENTO = 'DEV-CLI'

T
Testes_Atenas

Por se tratar de uma tabela muito grande essa TBLJ_MOVIMENTO_ESTOQUE, mudei a forma com que ele pegava este pendente.
Ao invés de fazer esses subselects quando a opção pendente estava marcada, criei um campo dentro dessa tabela de movimento de estoque como se fosse um flag true ou false.
Após isso fiz uma trigger no after insert da tabela que a cada linha adicionada nessa tabela ela verificava se esse movimento era de algum item pendente. Se fosse setava true, caso contrario ficaria false. Dentro dessa trigger coloquei as condições desses selects que antes estavam dentro dessa Query.
Mudei também o select da query que agora verifica quando marcada a opção pendente se é true ou false.
Deixando a consulta muito mais rápida(de 5 horas para 2minutos), e a trigger aumentou infimamente o tempo de faturar e estornar.
E para os dados antigos dei um update na tabela utilizando as condições parecidas com a da trigger, o que demorou, mas é necessário apenas uma vez para os dados antigos.

Creio que ainda tem coisas a serem melhoradas, mas por se tratar de verificar o que o usuário marca ou não, foi a melhor forma que achei para solucionar o mesmo.

Grato Javaflex pelo auxilio.

Criado 1 de março de 2016
Ultima resposta 6 de mar. de 2016
Respostas 2
Participantes 2

Topicos relacionados

Como saber se um mes tem 4 ou 5 semanas?[RESOLVIDO] 31 respostas Como converter inteiro para string! 13 respostas Comando SELECT para selecionar intervalo de data SQL SERVER 12 respostas &, &&, |, ||. Qndo usar? 6 respostas O método split 12 respostas
Alura O que é Python? — um guia completo para iniciar nessa linguagem de programação Acesse agora o guia sobre Python e inicie sua jornada nessa linguagem de programação: o que é e para que serve, sua sintaxe e como iniciar nela!
Casa do Codigo Engenharia de Prompt para Devs: Um guia para aprender a... Por Ricardo Pupo Larguesa — Casa do Codigo