10 técnicas para otimização de instruções SQL

Neste artigo vou apresentar dez técnicas de otimização de instruções SQL. Elas não são específicas de um banco de dados e podem ser aplicadas em qualquer SGBD que utilize SQL e até bancos de dados NoSQL.

Também destaco que estas são apenas sugestões de técnicas que podem ser aplicadas, pois o processo de tuning de instruções SQL, ou seja, otimizar uma instrução para que ela seja executada mais rapidamente, é complexo, demorado, depende de cada cenário e exige uma boa dose de experiência. Contudo, quanto mais nos esforçarmos e focarmos na otimização, melhores serão os resultados.

Outro ponto importante, que é preciso destacar, é que estas técnicas podem ser aplicadas às quatro principais instruções SQL: SELECT, INSERT, UPDATE e DELETE. Como dito anteriormente, algumas delas também são válidas para os bancos de dados NoSQL, um vez que, apesar da linguagem SQL não ser empregada, muitos dos conceitos são os mesmos (geração de plano de execução, uso de índices, métricas, etc).

1. Use bem os índices

Um dos principais fatores que impacta no desempenho de uma instrução SQL é o uso de índices, principalmente em instruções que empregam tabelas com muitos dados. Este é um fator tão importante, que a mera criação de um índice pode reduzir drasticamente a quantidade de passos internos na execução de instruções SQL.

Neste momento surge uma pergunta: como começar a analisar e criar os índices adequados? Infelizmente, não existe uma única resposta para esta pergunta. Mas para ajudar o leitor que não tem muita experiência no uso de índices, montei o seguinte fluxograma apresentado abaixo. Este diagrama é apenas uma sugestão genérica e deve ser utilizado somente como ponto de partida para aqueles que possuem pouca experiência. Obviamente, a cada criação de índice devem ser realizados diversos testes.

2. Explore o paralelismo

Atualmente, vivemos em uma era onde os microprocessadores são dominados por múltiplos núcleos. Até dispositivos móveis como smartphones, tablets e consoles portáteis estão recebendo microprocessadores com múltiplos núcleos. Mas isso não quer dizer que as aplicações estejam preparadas para obter o máximo de desempenho proporcionado por este hardware.

Sendo assim, é recomendável sempre procurar explorar o paralelismo em banco de dados. Apesar desta preocupação ser relativamente recente, e talvez um pouco mais comum para quem trabalha com programação, a exploração de paralelismo deve ser considerada quando for necessário otimizar instruções SQL.

Infelizmente, existem poucos recursos para a manipulação, ou aplicação direta de conceitos de programação paralela direto no banco de dados. Entretanto, é possível utilizar algumas técnicas para explorar o paralelismo na execução de instruções SQL,. dependendo, principalmente, do banco de dados a ser utilizado. Por exemplo: tanto o Oracle, quanto o SQL Server possuem diversos operadores utilizados internamente no plano de execução para mostrar se o plano escolhido utilizou, ou não paralelismo. Outro exemplo é a técnica apresentada no meu artigo de capa da revista SQL Magazine número 91, chamado Processamento paralelo de instruções SQL, disponível em neste aqui.

3. Saiba trabalhar com condições de pesquisa dinâmica

 

Imagine a seguinte situação: uma aplicação permite ao usuário escolher diversos campos para pesquisar na base de dados. Estes campos são apresentados através de elementos de um formulário como campos de texto, checkbox, combo e outros. Se o valor de algum campo for preenchido, este valor será montado em uma string, cujo conteúdo será utilizado para montar uma cláusula WHERE na instrução SELECT, que será encaminhada para o banco de dados. Este cenário é muito comum tanto em aplicações desktop, como em aplicações web.

Deixando as preocupações com SQL Injection de lado, como garantir que instruções SELECT dinâmicas possam ser executadas com um bom desempenho? Bem, como esta situação é muito comum, há uma técnica chamada condições de pesquisa dinâmica que pode auxiliar a criação da instrução SELECT visando tanto a flexibilidade proporcionada pela escolha dinâmica de filtros, quanto pelo desempenho.

Para quem trabalha com o SQL Server, existe alguns artigos escritos por Erland Sommarskog e que abordam esta técnica. Eles estão disponíveis neste link. Os conceitos apresentados podem ser adaptados para outros bancos de dados sem grandes problemas.

4. Conheça bem o modelo de dados

Quando se trabalha com uma instrução SQL, como SELECT, INSERT, UPDATE e DELETE é extremamente importante tem um bom conhecimento do modelo de dados. De fato, o modelo pode impactar muito no desempenho da instrução, devido aos joins que são realizados, os filtros na cláusula where e outros fatores.

Já escrevi diversos artigos aqui no iMasters abordando questões de desempenho que tratam do modelo de dados: como eliminar tabelas desnecessárias, tipagem de dados correta e até a análise de modelos de dados do Wordpress. Destaco também um artigo em inglês para o site SQLServerCentral.com, onde apresento algumas dicas para quem trabalha com modelos de dados grandes (com muitas tabelas e relacionamentos).

5. Quebre uma instrução SQL complexa em várias

Durante a minha carreira tenho encontrado muitos desenvolvedores com uma mania um tanto quanto esquisita: tentar montar relatórios utilizando uma instrução SELECT, por mais complexa que ela seja.

Ora, a instrução SELECT possui diversas cláusulas e opções que permitem realizar muitas operações de uma vez só, como filtros, ordenação, agregação, cálculos e outros. Porém, quanto mais operações uma única instrução SELECT realiza, mais complexa ela se torna. Aliás, recomendo o ótimo formatador online de instruções SQL, chamado SQL Online Formater, para auxiliar na melhora do visual de instruções SQL complexas e ajudar a compreendê-las. Este formatador está disponível em aqui.

Voltando ao assunto, a mania de fazer tudo em uma única instrução SELECT é considerada preciosa, e muitas vezes mais ajuda do que atrapalha. Faz muito sentido dividir as operações necessárias para se obter o relatório desejado em mais de uma instrução SELECT, pois assim fica mais fácil de dar manutenção no código, pode-se aplicar outras otimizações, gerenciar melhor o acesso a recursos (locks) e também fazer uso de opções de cache, criptografia e permissões do banco de dados. Uma dúvida pode surgir neste caso: como vou fazer minha aplicação/gerador de relatório utilizar mais de uma instrução SELECT para obter os dados que preciso?

Bem, neste caso existem algumas opções. A utilização de stored procedures é uma delas, mas pode ser utilizadas funções e views. Além das vantagens citadas também existem opções específicas de stored procedures e objetos programáveis do banco de dados que podem ser úteis, como criptografia, uso de parâmetros, opções de cache, entre outras.

6. Empregue os conceitos de teoria de conjuntos

 

Uma das técnicas de otimização mais disseminadas e, ouso a dizer, repetidas à exaustão, envolve trocar o processamento linha a linha (cursor) por operações baseadas em conjunto (set-based). Com certeza esta técnica é válida, mas não devemos parar por aí.

Atualmente, diversos dialetos do padrão SQL (Pl/SQL, T-SQL, etc) possuem operadores que são muito parecidos com as operações de conjuntos união, intersecção e diferença, como os operações UNION, INTERSECT e MINUS, respectivamente. Em muitas situações vale a pena pensar em termos de conjunto para montar uma instrução SQL otimizada. Por exemplo: para atualizar determinado conjunto de linhas, podemos pensar em obter todas as linhas desta tabela e retirar as linhas desta outra tabela. Neste caso deve-se considerar utilizar o operador MINUS.

O mais importante desta técnica não é, necessariamente, empregar este, ou aquele operador, ou joins; e sim mostrar que é possível pensar de maneira diferente em relação ao processo normal e muitas vezes já viciado na cabeça do desenvolvedor. A teoria de conjuntos, matematicamente falando, é muito poderosa e com alto grau de expressão. Saber aplicá-la quando se trabalha com instruções SQL pode fazer muita diferença, principalmente quando se busca obter desempenho.

7. Tenha casos de uso confiáveis para a instrução a ser otimizada

 

Quando se está modificando uma instrução SQL que já funciona corretamente, e se deseja melhorar seu desempenho, é praticamente obrigatório elaborar testes antes da modificação, pois assim é fácil determinar se a(s) modificação(es) implementada(s) quebraram, ou não a funcionalidade existente.

Por exemplo: suponha que uma instrução SELECT receba três parâmetros de filtro e, com certos valores A, B e C retorne, X linhas e T segundos. Guarde em algum lugar esta informação indicando que  para os valores A, B e C dos respectivos parâmetros, estas X linhas são retornadas em T segundos. Este é um caso de testes simples e é conveniente armazenar outros valores para os três parâmetros, as linhas que eles retornam e o tempo de execução.

Depois que estas informações são armazenadas você já pode começar a otimizar a instrução SELECT. A cada nova técnica empregada verifica-se se houve um ganho de tempo de acordo com os valores dos parâmetros armazenados nos casos de teste e nas linhas retornadas.

Este procedimento pode parecer básico, mas isso é de extrema importância quando se está trabalhando com otimizações de instrução SQL e, infelizmente, poucas pessoas fazem isso de maneira pragmática e metódica no dia a dia.

8. Modifique ou exclua poucos dados por vez

Praticamente todos bancos de dados relacionais suportam o conceito de transações, sejam elas implícitas na instrução que modifica os dados (INSERT, UPDATE e DELETE), ou explícita pelo uso de comandos, como BEGIN TRANSACTION, ou similares . E para implementar este recurso há a necessidade de utilização de um log, que pode receber o nome de Transaction Log, Redo Log File, ou qualquer outro.

Utilizar log de transações gera uma implicação importante, que deve ser levada em consideração pelo desenvolvedor: o seu preenchimento. Geralmente, os bancos de dados possuem recursos manuais e automáticos para controlar como o log de transações é preenchido e limpo, porém fica a cargo do desenvolvedor saber que uma transação muito longa, ou seja, que afete muitas linhas, vai gerar um impacto significativo no log e também afetar o desempenho da instrução. Em poucas palavras: use transações curtas para se obter um bom desempenho, pois assim o log será preenchido de forma mais adequada e também serão minimizados os recursos de locks (ou qualquer outro mecanismo de controle de concorrência) necessários para a execução da instrução. Cabe ao desenvolvedor descobrir quando é pouco e quando é muito (em relação à quantidade de linhas por instrução) de acordo com o seu ambiente e modelo de dados.

Para ficar fácil de entender, basta imaginar a analogia do consumo do um bife. Você não vai querer comê-lo todo de uma vez (o que pode causar uma grande indigestão). É preciso cortá-lo em pedaços menores e abocanhá-los um por vez.

9. Cuidado com a recursividade

Trabalhar com recursividade em instruções SQL ainda é um desafio muito grande para diversos desenvolvedores. Apesar de já existirem soluções consolidadas, com muito tempo de mercado e até presentes no padrão SQL, não é raro encontrar instruções SQL não otimizadas para se trabalhar com recursividade. Além disso, ainda há a opção dos bancos NoSQL, que também permitem trabalhar com estruturas hierárquicas, em árvore, ou qualquer combinação entre elas de diversas maneiras.

De acordo com padrão SQL, existem um recurso chamado CTE (Common Table Expression), que permite manipular recursivamente partes de uma instrução SQL. Apesar de nem todos os bancos de dados implementarem este recursos em seus dialetos SQL, como o padrão sugere, ainda sim é possível fazer uso de CTEs em diversas situações. Do ponto de vista de entendimento prepare-se para encontrar instruções SELECT complexas e de difícil compreensão quando se emprega CTE. Do ponto de vista de desempenho, é muito fácil cair em uma recursão que realiza mais instruções do que uma solução interativa e, além disso, faz um uso excessivo da pilha interna que controla a recursividade.

Devido à estes fatores, deve-se utilizar com muita cautela soluções recursivas implementadas diretamente no SQL, principalmente pelo fato de já existirem diversos algoritmos e técnicas de manipulação de estruturas de dados otimizadas, testadas, conhecidas e estudadas para se realizar operações em estruturas hierárquicas.

10. Mantenha-se atualizado para aprender novas técnicas

 

Esta última técnica muitas vezes é menosprezada por profissionais mais experientes. Assim como o modelo de dados e as características do dado em si podem mudar a qualquer momento, os bancos de dados também evoluem (apesar do padrão SQL mais ou menos estacionado há algum tempo).

E isso quer dizer que as técnicas de otimização também devem evoluir. Algo que hoje é válido pode não ser mais depois de um curto período de tempo. Por exemplo, em certas versões de um banco de dado,s diversos livros, sites, blogs e vídeos indicavam que determinada técnica era melhor para otimizar o desempenho naquele momento. Mas com novas versões de bancos de dados, algumas destas técnicas não fazem mais sentido.

Por isso é importante ter sempre a consciência do contexto da técnica que está sendo aplicada. Muitas vezes o banco de dados é o mesmo, porém houve uma mudança na característica dos dados (seletividade, ordem, modelo, tipo de dados, etc) que tornaram certa otimização obsoleta e que não mais obtém o desempenho de outrora. Devido a isso, é sempre importante manter-se atualizado em relação não apenas ao que o mercado de banco de dados apresenta, mas também em relação ao ambiente na qual seus dados estão sendo manipulados.

Para finalizar, gostaria de fazer um pedido aos leitores: estou participando de um concurso que premiará as melhores histórias de otimização de desempenho. Bem, a minha entrada se chama "A day in a DBA Life" e os leitores que gostarem dela podem me ajudar clicando no botão "I Like this Story" nesta página (é preciso se logar no Facebook para votar).