Loading…

Melhorando a Performance de Consultas no Totvs Protheus – Parte 2

Fala pessoal,

Estamos aqui para o segundo post da nossa série que tem um episódio novo a cada semana.

Antes de lerem esse post, sugiro verem o anterior para que sigam a sequência correta da série:

Agora que já sabem do que se trata essa série de posts, vamos para a análise da nossa próxima query.

Novamente olhando o Trace que monitora as queries mais demoradas do ambiente, essa query abaixo apareceu várias vezes consumindo muitos recursos e demorando mais de 1 minuto:

SELECT   R_E_C_N_O_ ,
         D_E_L_E_T_ ,
         CT2_FILIAL
FROM     dbo.CT2040
WHERE    CT2_FILIAL > '01'
         AND D_E_L_E_T_ = ' '
         AND (   CT2_DATA >= '20170101'
                 AND (   ( CT2_DEBITO = '654654654565          ' )
                        OR 
					 ( CT2_CREDIT = '98798798798797          ' )
                     )
             )
ORDER BY CT2_FILIAL ,
         CT2_DATA ,
         CT2_LOTE ,
         CT2_SBLOTE ,
         CT2_DOC ,
         CT2_LINHA ,
         CT2_TPSALD ,
         CT2_EMPORI ,
         CT2_FILORI ,
         CT2_MOEDLC ,
         R_E_C_N_O_;

Esse é o plano da query:

Mais uma vez, todos os índices dessa tabela começam com CT2_FILIAL.

No caso dessa query, o SQL está usando 4 índices para chegar no resultado.

Fabrício, mais uma vez o SQL Server sugeriu um índice. Ser DBA SQL Server é muito fácil. Vamos criar!!!

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[CT2040] ([D_E_L_E_T_],[CT2_FILIAL],[CT2_DATA])
INCLUDE ([CT2_LOTE],[CT2_SBLOTE],[CT2_DOC],[CT2_LINHA],[CT2_MOEDLC],[CT2_DEBITO],[CT2_CREDIT],[CT2_EMPORI],[CT2_FILORI],[CT2_TPSALD],[R_E_C_N_O_])

É aqui que começamos a diferenciar o profissional que entende como os índices funcionam do profissional que caiu de paraquedas nessa análise e não tem interesse em aprender.

D_E_L_E_T_ é uma coluna que tem apenas dois valores distintos (‘*’ ou ”), ou seja, uma péssima coluna para índice, pois não é nada seletiva.

Além disso, a tabela tem quase 30 milhões de registros. Um índice com todas essas colunas vai ficar bem grande.

Vamos tentar outras possibilidades menos traumáticas.

Vamos focar nos filtros da query:

WHERE    CT2_FILIAL > '01'
         AND D_E_L_E_T_ = ' '
         AND (   CT2_DATA >= '20170101'
                 AND (   ( CT2_DEBITO = '654654654565          ' )
                        OR 
					 ( CT2_CREDIT = '98798798798797          ' )
                     )
             )
  • CT2_FILIAL e D_E_L_E_T_ não filtram nada, ou seja, não são colunas boas para índices.
  • CT2_DATA filtra muito pouco, pois é bem provável que teremos muitos registros em 2017.
  • A solução para a query está em CT2_DEBITOCT2_CREDIT.

Mas Fabrício, agora deu ruim. Tem um miserável de um OR ai nessa query. Já me falaram que OR mata a performance de uma query.

Pois é… Vamos lá…

Realmente OR no WHERE não é legal. Mas é um ERP, se for query padrão não vamos conseguir mudar o código. Temos que sobreviver com esse OR ai mesmo.

Uma coisa que podemos fazer é usar um negócio chamado Index Intersection. Vamos criar um índice em cada coluna do OR e ver se o SQL Server vai usar os dois índices separados, juntar os dois e dar o resultado da query.

create nonclustered index CT2040W01 on CT2040 (CT2_DEBITO ) with(FILLFACTOR=90,DATA_COMPRESSION=PAGE)

create nonclustered index CT2040W02 on CT2040 (CT2_CREDIT ) with(FILLFACTOR=90,DATA_COMPRESSION=PAGE)

Os dois índices ficaram com um tamanho de 500 MB. Imagina o índice sugerido com todas aquelas colunas.

WOW!!!! Ao rodar a query novamente ela passa a rodar de forma instantânea:

Esse é o novo plano:

SQL faz um seek nos meus dois índices e depois um Merge Join.

Incluindo todas as colunas usadas na query em um desses índices eu poderia evitar o Key Lookup, mas como a query já passou de 1 minuto e 30 segundos para 0 segundos, vale a pena fazer isso?

Consumo da query antes do índice:

Table 'CT2040'. Scan count 12, logical reads 263520

SQL Server Execution Times:
   CPU time = 167669 ms,  elapsed time = 89505 ms.

Consumo da query depois do índice:

Table 'CT2040'. Scan count 2, logical reads 3336

SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 197 ms.

Diferença absurda!!!

Antes o SQL Server tinha que ler 263 mil páginas de 8 kb para dar o resultado dessa query. Após o índice ele precisa ler apenas 3 mil páginas para dar o mesmo resultado. Essas páginas tem que passar pela memória, então, antes de comprar mais memória para um servidor, crie alguns índices que o resultado pode ser o mesmo.

Olhando no Trace de queries demoradas, a query ficou tão rápida que tenho que apelar e colocar um waitfor delay para poder visualizar e comparar:

Ela rodou em 0,5 segundos e a diferença de leituras de páginas (263 mil para 3 mil) e do consumo de CPU (176 mil para 31) é gigante!!!

É isso ai pessoal, melhoramos mais uma query no 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

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

  1. Essa série tá massa, Fabrício!
    Muito didática e simples, dando a visão correta de como utilizar as ferramentas que temos à disposição no SQL Server.
    Não trabalho com clientes TOTVS, mas isso se aplica a muitos casos que já vi no meu dia a dia com outros ERPs. As consultas às vezes poderiam ser melhoradas, sem dúvida, mas nem sempre isso é fácil de se conseguir. Então cabe ao DBA contornar esses gargalos de alguma forma.

    Parabéns mais uma vez pelo excelente trabalho!

  2. Como obtenho as informações abaixo?

    Table ‘CT2040’. Scan count 12, logical reads 263520

    SQL Server Execution Times:
    CPU time = 167669 ms, elapsed time = 89505 ms.
    Table ‘CT2040’. Scan count 12, logical reads 263520

    SQL Server Execution Times:
    CPU time = 167669 ms, elapsed time = 89505 ms.

    1. Olá Junior,

      Basta executar o comando abaixo uma vez na sessão para deixar a opção habilitada (somente nessa sessão). Depois disso, quando você executar algum comando ele irá retornar as informações.

      SET STATISTICS IO, TIME ON

      Abraço,
      Fabrício

Deixe uma resposta

%d blogueiros gostam disto: