none
Sub Query demora RRS feed

  • Pergunta

  • Olá a todos,

    Fiz uma consulta para retornar os produtos que NÃO tiveram movimentações no estoque do meu trabalho, em um determinado período. A consulta funciona bem, porém, o tempo de execução é muito alto (cerca de 3 min), e para colocar em produção para um usuário, isso se torna quase inviável.

    Segue query:

    SELECT DISTINCT A.CODIGOPRD,
    	A.NOMEFANTASIA,
    	B.SALDOFISICO2,
    	A.CUSTOUNITARIO,
    	A.CODUNDCONTROLE,
    	A.CODUNDCOMPRA,
    	A.CODUNDVENDA
    FROM TPRD A, TPRDLOC B, SZPRD C
    WHERE A.IDPRD = B.IDPRD
    AND B.IDPRD = C.IDPRD
    AND B.CODLOC = 002
    AND A.INATIVO = 0
    AND C.TIPOITEM = 'A'
    AND B.SALDOFISICO2 > 0
    AND A.IDPRD NOT IN (SELECT DISTINCT A.IDPRD FROM TITMMOV A, TMOV B 
                                 WHERE A.IDMOV = B.IDMOV
                                 AND B.CODTMV IN ('2.2.08','2.2.16','2.2.12','2.2.10')
                                 AND B.DATAMOVIMENTO >= '2013-03-01'
                                 AND B.DATAMOVIMENTO <= '2013-03-10' )
    ORDER BY NOMEFANTASIA

    Alguém tem alguma sugestão de como melhorar a rapidez de execução da minha query?

    Obrigado.

    Gabriel Alves.



    • Editado galves.rod terça-feira, 30 de abril de 2013 11:03 Erro
    segunda-feira, 29 de abril de 2013 19:10

Respostas

  • Boa tarde,

    Experimente dessa forma:

    with CTE_M as ( SELECT DISTINCT A.IDPRD FROM TITMMOV A INNER JOIN TMOV B ON B.IDMOV = A.IDMOV

    WHERE B.CODTMV IN ('2.2.08','2.2.16','2.2.12','2.2.10') AND B.DATAMOVIMENTO BETWEEN '2013-03-01' AND '2013-03-10' ) SELECT DISTINCT A.CODIGOPRD, A.NOMEFANTASIA, B.SALDOFISICO2, A.CUSTOUNITARIO, A.CODUNDCONTROLE, A.CODUNDCOMPRA, A.CODUNDVENDA FROM TPRD A INNER JOIN TPRDLOC B ON B.IDPRD = A.IDPRD INNER JOIN SZPRD C ON C.IDPRD = B.IDPRD WHERE B.CODLOC = 002 AND A.INATIVO = 0 AND C.TIPOITEM = 'A' AND B.SALDOFISICO2 > 0 AND NOT EXISTS (SELECT 1 FROM CTE_M AS M WHERE M.IDPRD = A.IDPRD) ORDER BY NOMEFANTASIA


    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br


    • Editado gapimex segunda-feira, 29 de abril de 2013 19:40
    • Sugerido como Resposta José Diz quinta-feira, 2 de maio de 2013 15:20
    • Marcado como Resposta galves.rod quinta-feira, 2 de maio de 2013 15:23
    segunda-feira, 29 de abril de 2013 19:39
  • Compreendido, Gabriel.

    Sintetizando aqui em um único post, duas sugestões:

    1) retirar o DISTINCT em CTE_M, no código melhorado sugerido pelo 'gapimex'. Como o objetivo é somente detectar se houve ou não movimento em determinado período, não é necessário o uso do DISTINCT nesse caso. Se analisar o plano de execução da subquery, perceberá a presença do operador lógico DISTINCT SORT.

    with CTE_M as
    (
        SELECT A.IDPRD 
        FROM TITMMOV A
             INNER JOIN TMOV B 
           ON B.IDMOV = A.IDMOV
        WHERE 
           B.CODTMV IN ('2.2.08','2.2.16','2.2.12','2.2.10') AND 
           B.DATAMOVIMENTO BETWEEN '2013-03-01' AND '2013-03-10'
    )
     

    2) Existe algum índice em TMOV pela data de movimento? Provavelmente sim, pelo fato de ser uma tabela de movimento. Mas há algum índice que tenha a data de movimento como primeira coluna do índice e que nesse índice conste CODTMV (seja como parte da chave ou como coluna incluída)?

    Avalie separadamente:

    create nonclustered index TMOV_porData on TMOV (DATAMOVIMENTO) include (CODTMV)



    • Editado José Diz terça-feira, 30 de abril de 2013 13:24 (tirando possíveis arestas)
    • Marcado como Resposta galves.rod quinta-feira, 2 de maio de 2013 15:23
    terça-feira, 30 de abril de 2013 13:06

Todas as Respostas

  • Boa tarde,

    Experimente dessa forma:

    with CTE_M as ( SELECT DISTINCT A.IDPRD FROM TITMMOV A INNER JOIN TMOV B ON B.IDMOV = A.IDMOV

    WHERE B.CODTMV IN ('2.2.08','2.2.16','2.2.12','2.2.10') AND B.DATAMOVIMENTO BETWEEN '2013-03-01' AND '2013-03-10' ) SELECT DISTINCT A.CODIGOPRD, A.NOMEFANTASIA, B.SALDOFISICO2, A.CUSTOUNITARIO, A.CODUNDCONTROLE, A.CODUNDCOMPRA, A.CODUNDVENDA FROM TPRD A INNER JOIN TPRDLOC B ON B.IDPRD = A.IDPRD INNER JOIN SZPRD C ON C.IDPRD = B.IDPRD WHERE B.CODLOC = 002 AND A.INATIVO = 0 AND C.TIPOITEM = 'A' AND B.SALDOFISICO2 > 0 AND NOT EXISTS (SELECT 1 FROM CTE_M AS M WHERE M.IDPRD = A.IDPRD) ORDER BY NOMEFANTASIA


    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br


    • Editado gapimex segunda-feira, 29 de abril de 2013 19:40
    • Sugerido como Resposta José Diz quinta-feira, 2 de maio de 2013 15:20
    • Marcado como Resposta galves.rod quinta-feira, 2 de maio de 2013 15:23
    segunda-feira, 29 de abril de 2013 19:39
  • Boa tarde gabimex,

    A consulta continua demorada, mais ainda assim, eu agradeço muito. 

    segunda-feira, 29 de abril de 2013 19:53
  • Gabriel, confirma uma coisa... aquela query que você postou no inicio está retornando os produtos com movimento, certo?

    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 29 de abril de 2013 21:12
  • 'gapimex', fiquei com a mesma dúvida. No código que ele postou me pareceu que lista produtos com movimento; já o que você postou, entendi que assume "retornar os produtos que NÃO tiveram movimentações" (NOT EXISTS). Então, os resultados deveriam ser diferentes... 

    Mas, considerando-se que o desejado pelo Gabriel seja retornar produtos sem movimento, acho que se retirar o DISTINCT de CTE_M (no código que você postou) elimina-se o operador lógico DISTINCT SORT (vide plano de execução), que pode estar consumindo muito tempo na subquery. Nesse caso não seria necessário o distinct, o que interessa é saber se houve ou não movimento, ?

    • Sugerido como Resposta José Diz quinta-feira, 2 de maio de 2013 15:20
    terça-feira, 30 de abril de 2013 01:04
  • Estava realizando testes e tirei o "not" antes do "in" e esqueci de colocar novamente. A query postada estava errada, já que o quero mesmo, é listar os produtos sem movimentação. Já corrigi a postagem.

    Desculpem, e obrigado.

    terça-feira, 30 de abril de 2013 11:06
  • Compreendido, Gabriel.

    Sintetizando aqui em um único post, duas sugestões:

    1) retirar o DISTINCT em CTE_M, no código melhorado sugerido pelo 'gapimex'. Como o objetivo é somente detectar se houve ou não movimento em determinado período, não é necessário o uso do DISTINCT nesse caso. Se analisar o plano de execução da subquery, perceberá a presença do operador lógico DISTINCT SORT.

    with CTE_M as
    (
        SELECT A.IDPRD 
        FROM TITMMOV A
             INNER JOIN TMOV B 
           ON B.IDMOV = A.IDMOV
        WHERE 
           B.CODTMV IN ('2.2.08','2.2.16','2.2.12','2.2.10') AND 
           B.DATAMOVIMENTO BETWEEN '2013-03-01' AND '2013-03-10'
    )
     

    2) Existe algum índice em TMOV pela data de movimento? Provavelmente sim, pelo fato de ser uma tabela de movimento. Mas há algum índice que tenha a data de movimento como primeira coluna do índice e que nesse índice conste CODTMV (seja como parte da chave ou como coluna incluída)?

    Avalie separadamente:

    create nonclustered index TMOV_porData on TMOV (DATAMOVIMENTO) include (CODTMV)



    • Editado José Diz terça-feira, 30 de abril de 2013 13:24 (tirando possíveis arestas)
    • Marcado como Resposta galves.rod quinta-feira, 2 de maio de 2013 15:23
    terça-feira, 30 de abril de 2013 13:06
  • José,

    Concordo com a sua sugestão de retirar o Distinct.

    Abraço


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 30 de abril de 2013 17:55
  • Bom dia José Diz e gapimex,

    Fiz os testes solicitados e só o fato de ter tirada o 'Distinct' da query, conforme sugeriu o José Diz, agilizou bastante a consulta.

    Analisei o plano de execução e, assim como ele disse, existem operações que estão com um alto custo do CPU. Link para baixar o arquivo: http://www.sendspace.com/file/9usob9.

    Verifiquei se existia index pela data do movimento, existe.

    CREATE NONCLUSTERED INDEX [TXMOVDTM] ON [dbo].[TMOV] 
    (
    	[DATAMOVIMENTO] ASC,
    	[CODCOLIGADA] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO
    

    Obrigado.

    Gabriel Alves.

    quinta-feira, 2 de maio de 2013 12:31
  • Gabriel, o tempo de resposta atual da consulta, após retirar DISTINCT de CTE_M, agora está satisfatório? Passou de 3 minutos para quanto?

    Estive analisando o plano de execução que você nos enviou da consulta atualmente em testes. Há algumas melhorias que podem ser feitas nos índices, especialmente para agilizar a execução de alguns operadores 'Pesquisa de chave' presentes no plano de execução. Entretanto, melhorias na execução dessa consulta em específico poderão ter custo em outras atividades do banco de dados.

    Por exemplo, há uma 'Pesquisa de chave' com custo de 6% cujo objetivo é buscar CODTMV na tabela TMOV através do índice PKTMOV (vide canto superior direito do plano de execução). Se no índice TXMOVDTM for incluída a coluna CODTMV, essa busca na tabela TMOV não mais seria necessária, pois a consulta obteria o valor de CODTMV diretamente do índice TXMOVDTM.

    Isso agilizaria ainda mais essa consulta. Por outro lado, haveria um aumento no espaço físico ocupado por esse índice TXMOVDTM e o database engine teria que manter CODTMV no índice. É um trade-off; ganha-se de um lado e perde-se de outro.

    Para analisar modificações em índices é recomendado conhecer as demais consultas e o banco de dados como um todo. Com o uso de colunas incluídas em índices pode até ser que outras consultas também obtenham ganho.

    Há ainda outro caminho que é, após analisar o plano de execução, avaliar possíveis alterações na consulta, sem ainda partir para modificações nos índices.

    Tudo depende de saber se o tempo atual de resposta está satisfatório ou não.

    • Editado José Diz quinta-feira, 2 de maio de 2013 14:21
    quinta-feira, 2 de maio de 2013 13:49
  • Considerando o mês inteiro, leva cerca de 1 minuto e 15 segundo. Acho que é o melhor que dá pra fazer, pois acredito que o tempo gasto é devido à quantidade de informações presentes. Acho que vou entregar o relatório, e informar que o relatório levará alguns minutos para ser apresentado.

    Mais isso não prejudicará muito o trabalho do usuário ou o processamento do servidor, já que o intuito desse relatório é ser tirado uma vez por mês.

    Se você concordar, vou marcar o poste como respondido.

    quinta-feira, 2 de maio de 2013 14:36
  • Gabriel, sempre é possível otimizar o tempo de resposta de consultas, seja reescrevendo-as ou procedendo a alterações no banco de dados. Mas, considerando-se que a consulta em questão é processada somente uma vez ao mês, 1 min e 15 s está de bom tamanho!   rs

    quinta-feira, 2 de maio de 2013 14:57
  • Muito obrigado a você e ao gapimex pela ajuda.
    quinta-feira, 2 de maio de 2013 15:15
  • Esqueci de comentar,

    O BD é da TOTVS, desenvolvido pela antiga RM, não me sinto nenhum pouco seguro incluindo ou alterando a estrutura do BD, tal como os índices. Poderia até incluir a coluna no TXMOVDTM, mais em base teste. Oficialmente eu não faria.

    quinta-feira, 2 de maio de 2013 15:23