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:

• Melhorando a Performance de Consultas no Totvs Protheus – Parte 1

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_DEBITO e CT2_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