Melhorando a Performance de Consultas no Totvs Protheus – Parte 8 – Fabricio Lima

Melhorando a Performance de Consultas no Totvs Protheus – Parte 8

30 de janeiro de 201910 de outubro de 2020Fabrício LimaAdministração de Banco de Dados, DBA Remoto, Queries do Dia a Dia, Virtual PASS BR

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;

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.

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 Amorim: https://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

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)

1 2	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.

--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:

https://www.youtube.com/watch?v=BKmUa3aZn6s

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

Relacionado

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

João Avila disse:

5 de junho de 2019 às 17:22

Tomara que saiam mais episódios dessa serie!
Muito bom!

Valeu Fabricio

Responder
1. Fabrício Lima disse:
  
  12 de junho de 2019 às 09:41
  
  Nice… Em breve pretendo gravar um curso explicando essa série e outras coisas.
  
  Responder