AGUARDE... CARREGANDO...

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:

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

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

  • 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.

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:

Consumo da query depois do índice:

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.

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

6 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!

Deixe uma resposta

%d blogueiros gostam disto: