Como otimizar a performance de query em banco de dados

Por que otimizar queries em banco de dados?

Um dos grandes desafios da análise de dados é garantir a disponibilidade de dados dentro de um intervalo de tempo viável e que permita a visualização de dados atualizados e íntegros.

Assim, por exemplo, cada vez que um relatório ou dashboard precisa ser utilizado, o tempo de resposta dele será menor e o usuário terá maior facilidade para realizar análises e filtrar as informações.

Um das maneiras de atingir esse objetivo e ainda reduzir a necessidade (e custos!) de recursos computacionais é através da otimização de queries.

Seja qual for a ferramenta que você utilize, as dicas abaixo vão te ajudar a otimizar a performance de queries em banco de dados imediatamente!

Leia também: Como criar Testes de Validação de Dados

Como otimizar a performance de queries

1. Seja consciente ao selecionar os campos

A primeira dica para otimizar queries de banco de dados é a mais fácil de ser implementada: evite usar o famoso select *

A justificativa é muito simples: por vezes, precisamos apenas de algumas da colunas, então qual seria a utilidade de processar todas as colunas a cada vez que a query corre?

Avalie se você realmente tem a necessidade de ler todas as colunas de uma tabela.

Além de ser um gasto de recursos e de tempo, também impacta diretamente na legibilidade da busca.

Se colocarmos o nome dos campos que precisamos, sabemos exatamente o que será mostrado como resultado e em que ordem, facilitando a compreensão de uma pessoa que ainda não está familiarizada com o código ou com o modelo de dados, e também permite agilizar análises de causa raiz e bugs.

2. Utilize CTEs

Algumas ferramentas de transformação de dados, como o dbt, funcionam melhor através de uma metodologia modular, ou seja, quebrando o código em pequenas partes reutilizáveis. Para isso, utiliza-se muito o conceito das CTEs (Common Table Expression).

As CTEs são resultados temporários que você pode referenciar em qualquer posição do seu código. Essas tabelas auxiliares são interessantes quando temos lógicas complexas, uma vez que nos permitem “quebrar” as etapas de forma a facilitar a legibilidade e otimizar o processamento.

O uso de CTEs também é recomendado para aumentar a performance de queries em banco de dados quando temos lógicas recursivas, ou seja, quando se é preciso autorreferenciar a tabela. Isso acontece, por exemplo, quando temos uma tabela de funcionários e queremos encontrar a hierarquia entre eles.

O mesmo resultado de uma CTE pode ser alcançado através do uso de subqueries, entretanto, as CTEs nos permitem apresentar cada etapa da transformação de forma ordenada e clara, além de permitir criar nomes para cada CTE, facilitando ainda mais a legibilidade e interpretação do código.

Outra vantagem é que como as CTEs podem ser reutilizadas, se for necessário referenciar a mesma transformação mais de uma vez, você apenas precisa referenciar novamente a CTE. No caso se subqueries, você teria que reescrever a lógica novamente, dificultando a legibilidade e gastando mais recursos (tempo e dinheiro!)

3. Use e abuse de filtros

Utilize filtros o quanto antes. Sempre.

Semelhante a selecionar apenas os campos que você necessita, ao filtrar, você processará uma quantidade menor de dados quando chegar a hora de fazer transformações ou lógicas mais complexas.

Os filtros também são muito relevantes para a qualidade e proteção de dados, uma vez que apenas dados relevantes serão processados, reduzindo a possibilidade de erros.

Em adição, o uso de filtros resulta em bancos de dados com menor número de resultados, agilizando o debugging.

Quanto mais cedo você remover os dados que não serão necessários, melhor!

Dica extra: CTEs em conjunto com inner joins para filtrar dados podem melhorar ainda mais a performance da sua query!

4. Atenção ao definir Where Clauses

Filtrar os dados é muito importante para otimizar a performance de queries em bancos de dados, mas alguns cuidados devem ser tomados para que o efeito oposto não aconteça!

Evite utilizar expressões ou cálculos complexos, assim como o uso de wildcards com a função LIKE, por exemplo. Priorize o uso de comparações diretas e igualdades.

5. Cuidados com Order By e Group By

Muitas vezes não há necessidade de utilizar esses comandos, que acabam por ser redundantes ou aumentando o uso de recursos computacionais quando os dados não são ordenados naturalmente.

Para otimizar a performance de queries em bancos de dados, avalie se realmente existe a necessidade de agrupamento e ordenação, em especial para colunas em que há muita variedade de valores distintos ou cardinalidade.

6. Confira o resultado de Joins

Se não avaliarmos bem as condições ou o tipo de join a ser feito, podemos acabar com registros duplicados ou com linhas desnecessárias a mais.

Por conta disso, é sempre importante comparar a quantidade de registros em cada tabela processada e no resultado final. Qualquer inconsistência deve ser corrigida.

Ainda nesse tópico, uma sugestão para otimizar a performance de queries em banco de dados, é utilizar chaves numéricas. Essa abordagem costuma ser mais eficiente por ser mais rápida devido a facilidade de indexação.

Além disso, também colabora com a qualidade de dados, uma vez que auxilia na redução de combinações inconsistentes (espaços em branco, formatação de valores diferentes, capitalização de letras…) e facilita a legibilidade e manutenção do código.

7. Funções e Conversões de tipo de dados

Outra maneira de otimizar a performance de queries em banco de dados é analisando as funções e conversões de tipos de dados. Teste as possibilidade e avalie os resultados, pois isso também pode depender da ferramenta que você utiliza e do tipo de dado que está a transformar.

Algumas funções como cast(), por exemplo, costumam ser evitadas em ferramentas como o dbt por reduzir a performance e poder causas problemas na qualidade de dados.