Loading…

Melhorando a Performance de Consultas no Totvs Protheus – Parte 8

Fala Pessoal,

Estamos de volta com novos episódios da série de análise de queries de ambientes Totvs Protheus.

Antes de lerem esse post, caso ainda não tenham lido os anteriores, sugiro que façam:

No post de hoje vamos analisar esse pequeno monstro que estava demorando 4 minutos para executar:

SELECT SB2.B2_FILIAL,
       SB1.B1_COD,
       SB1.B1_DESC2,
       SB2.B2_LOCAL,
       SB2.B2_QATU,
       SB1.B1_GRUPO,
       CASE
           WHEN
           (
               SELECT TOP 1
                      SD2.D2_EMISSAO
               FROM SD2010 SD2
               WHERE SD2.D2_TES IN ( '2342', '3423', '23434')
                     AND SD2.D2_COD = SB1.B1_COD
                     AND SD2.D2_FILIAL = '99'
               ORDER BY SD2.D2_EMISSAO DESC
           ) IS NULL THEN
               'SEM MOVIMENTO'
           ELSE
       (
           SELECT TOP 1
                  SD2.D2_EMISSAO
           FROM SD2010 SD2
           WHERE SD2.D2_TES IN ( '2342', '3423', '23434')
                 AND SD2.D2_COD = SB1.B1_COD
                 AND SD2.D2_FILIAL = '99'
           ORDER BY SD2.D2_EMISSAO DESC
       )
       END AS MOVIMENTO
FROM SB1010 SB1,
     SB2010 SB2
WHERE SB1.B1_COD = SB2.B2_COD
      AND SB2.B2_QATU > 0
      AND SB1.D_E_L_E_T_ = ' '
      AND SB2.D_E_L_E_T_ = ' '
      AND SB1.B1_COD
      BETWEEN '               ' AND 'zzzzzzzzzzzzzzz'
      AND SB2.B2_LOCAL = '01'
      AND SB1.B1_GRUPO
      BETWEEN '    ' AND 'ZZZZ'
      AND SB1.B1_FILIAL = '  '
      AND SB2.B2_FILIAL = '99'
      AND
      (
          CASE
              WHEN
              (
                  SELECT TOP 1
                         SD2.D2_EMISSAO
                  FROM SD2010 SD2
                  WHERE SD2.D2_TES IN ( '2342', '3423', '23434')
                        AND SD2.D2_COD = SB1.B1_COD
                        AND SD2.D2_FILIAL = '99'
                  ORDER BY SD2.D2_EMISSAO DESC
              ) IS NULL THEN
                  'SEM MOVIMENTO'
              ELSE
          (
              SELECT TOP 1
                     SD2.D2_EMISSAO
              FROM SD2010 SD2
              WHERE SD2.D2_TES IN ( '2342', '3423', '23434')
                    AND SD2.D2_COD = SB1.B1_COD
                    AND SD2.D2_FILIAL = '99'
              ORDER BY SD2.D2_EMISSAO DESC
          )
          END <= '20180722'
          OR CASE
                 WHEN
                 (
                     SELECT TOP 1
                            SD2.D2_EMISSAO
                     FROM SD2010 SD2
                     WHERE SD2.D2_TES IN ( '2342', '3423', '23434')
                           AND SD2.D2_COD = SB1.B1_COD
                     ORDER BY SD2.D2_EMISSAO DESC
                 ) IS NULL THEN
                     'SEM MOVIMENTO'
                 ELSE
             (
                 SELECT TOP 1
                        SD2.D2_EMISSAO
                 FROM SD2010 SD2
                 WHERE SD2.D2_TES IN ( '2342', '3423', '23434')
                       AND SD2.D2_COD = SB1.B1_COD
                       AND SD2.D2_FILIAL = '99'
                 ORDER BY SD2.D2_EMISSAO DESC
             )
             END = 'SEM MOVIMENTO'
      )
ORDER BY SB1.B1_DESC2;

obs.: Os parâmetros estão com valores fictícios.

Fabrício, você sabe o que essa query faz no Protheus?

R: Não tenho a menor ideia, mas não precisamos saber para atuar.

Usamos o SET STATISTICS IO,TIME ON para ver o consumo da query:

Table 'Worktable'. Scan count 8547, logical reads 86299988

Table 'SD2010'. Scan count 6, logical reads 143406, physical reads 0

SQL Server Execution Times:

   CPU time = 362139 ms,  elapsed time = 245129 ms.

SQL está matando o tempdb para rodar essa query.

Olhando o plano dela também podemos ver isso. O plano do monstro é gigante, então vou colocar só uma parte dele:

Reparem os operadores de Spool. O Spool armazena um resultado temporário no TempDB para ser reutilizado em consultas futuras na execução dessa query.

Quer saber mais sobre o Spool?

Segue post do mestre Fabiano Amorimhttps://blogfabiano.com/2009/01/13/operador-do-dia-spool/

Olhando para a query já conseguimos ver coisas estranhas nesse monstrinho né!?

6 Subqueries com o mesmo código?

Hummm…

Alterar a query não é possível. Só conseguimos resolver com índices.

Olhando só a subquery:

 SELECT TOP 1
                      SD2.D2_EMISSAO
               FROM SD2010 SD2
               WHERE SD2.D2_TES IN ( '2342', '3423', '23434')
                     AND SD2.D2_COD = SB1.B1_COD
                     AND SD2.D2_FILIAL = '99'
               ORDER BY SD2.D2_EMISSAO DESC

Será que existe um índice começando por D2_COD?

R: Não existia.

Se eu criar vai ajuda essa query?

R: Vamos descobrir.

Segue o índice que foi criado:

create nonclustered index SD2010W01 on SD2010(D2_COD,D2_FILIAL,D2_TES,D2_EMISSAO)
with(DATA_COMPRESSION=PAGE,FILLFACTOR=90)

Após criar o índice, a query que rodava em 4 minutos passou a rodar em 3 segundos.

WOW!

Mesmo a query sendo um monstrinho com 6 subqueries conseguimos fazer ela rodar em 3 segundos.

--Consumo Antes

Table 'Worktable'. Scan count 8547, logical reads 86299988

Table 'SD2010'. Scan count 6, logical reads 143406, physical reads 0

SQL Server Execution Times:

   CPU time = 362139 ms,  elapsed time = 245129 ms.

--Consumo Depois

Table 'Worktable'. Scan count 1132, logical reads 4504, physical reads 0

Table 'SD2010'. Scan count 104711, logical reads 322107, physical reads 0

 SQL Server Execution Times:

   CPU time = 12626 ms,  elapsed time = 3591 ms.

Melhorias:

  • Tempo total de 4min05seg para 03 seg.
  • 6 minutos consumindo CPU (em paralelo) para 12 segundos de processamento. De nada, processador!
  • 86 milhões de leituras no tempdb para 4 mil leituras. De nada, Disco!
  • A quantidade de leituras na SD2 aumentou, mas nosso problema foi resolvido. Segue a vida.

Viram quanta leitura e quanta CPU sendo consumida?

Antes de comprar hardware, analise algumas consultas que pode ter a mesma melhoria deixando de usar recursos sem necessidade.

É isso ai pessoal, melhoramos mais uma query do Totvs Protheus.

Até a próxima análise de query.

 

Atualizado no dia 07/10/2020:

Publiquei um curso com 11 horas de duração com toda minha experiência de anos no assunto e de dezenas de clientes Protheus atendidos:

Curso: Melhorando a Performance de Consultas no Totvs Protheus

Gravei uma aula grátis com 60 minutos de duração sobre o que você deve aprender para melhorar a performance no Protheus:

 

Gostou desse Post?

Curta, comente, compartilhe com os coleguinhas…

Assine meu canal no Youtube e curta minha Página no Facebook para receber Dicas de Leituras e Eventos sobre SQL Server.

Abraços,

Fabrício Lima

Microsoft Data Platform MVP

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

2 thoughts on “Melhorando a Performance de Consultas no Totvs Protheus – Parte 8

Deixe uma resposta para Fabrício LimaCancelar resposta

%d blogueiros gostam disto: