Sub Query demora

Question

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.

Answer 1

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

Answer 2

Boa tarde gabimex,

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

Answer 3

Gabriel, confirma uma coisa... aquela query que você postou no inicio está retornando os produtos com movimento, certo?

---

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

Answer 4

'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, né?

Answer 5

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.

Answer 6

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)

Answer 7

José,

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

Abraço

---

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

Answer 8

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.

Answer 9

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.

Answer 10

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.

Answer 11

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

Answer 12

Muito obrigado a você e ao gapimex pela ajuda.

Answer 13

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.