Clausulas desnecessárias em consultas SQL

Uma atividade bastante comum de DBA é a monitoração das consultas mais exigentes para o SGBD Em alguns casos, elas são óbvias, por exemplo, consultas que demoram horas. Em outros casos, mais sutis, pode ocorrer uma consulta de, digamos, 20 segundos, mas repetida muitas vezes ao longo do dia. Isoladamente, não é importante, mas realizada 100 vezes ao longo do dia, torna-se muito significativa.

O que fazer?

Uma vez identificada a consulta, é necessário compreende-la, identificando as tabelas utilizadas, os campos retornados na resposta, condições na clausula WHERE e a existência de clausulas como ORDER e GROUP BY. Também é necessário verificar qual aplicação esta disparando a consulta e qual a viabilidade de alterá-la. A compreensão da consulta é fundamental para garantir a sua correta execução e modificação, se for o caso.

Clausulas desnecessárias

Na minha experiência, uma alternativa usual para melhorar uma consulta é reescrevê-la, retirando os excessos. Na medida em que a consulta esteja em uma aplicação, é necessária a colaboração de analista de sistemas e programadores, mas, muitas vezes, é recompensador.

Um excesso comum é a inclusão de clausulas que obrigam o SGBD a ordenar o resultado da consulta, ou pelo menos, um resultado parcial da mesma. Apenas para lembrar, ordenar/classificar é uma operação clara, bem mais difícil de ser realizada que uma seleção simples. Há dois custos envolvidos: processamento e acesso a disco.

Na melhor hipótese, toda informação já esta em memória e pode ser mantida durante a operação. Ou seja, há menos dados que a memória disponível. O algoritmo, neste caso, é de ordem O(n log n), sendo n o número de tuplas. A tabela 1 mostra a consequência numérica. Dobrar o número de registros envolvidos (n), mais que dobra o custo da operação, como pode ser percebido na coluna do custo normalizado. O número de operações é aproximado, mas, para casos reais onde os dados estejam aleatórios, é bastante aproximado.

Na hipótese realista, não é possível manter todos os dados em memória. Assim, o algoritmo de ordenação deve carregar uma parte dos registros para memória, ordenar, gravar em disco, ler outra parte, ordenar, gravar e combinar as partes, relendo todos registros. Ou seja, o número de operações em disco passa a ser importante, além do processamento em memória. Não vou entrar em todos detalhes, mas, caro leitor, acredite, dobrar o número de registros tende a multiplicar por três o tempo de processamento.

Uma segunda característica da ordenação é seu potencial blocante. Os SGBD utilizam um mecanismo de pipeline para processar as várias etapas de uma consulta e evitar a gravação em disco de respostas intermediárias. Se a consulta, por exemplo, puder ser dividida em três operações, tão logo a primeira já disponha de alguns resultados, eles são repassados para a segunda, que manda para a terceira, até a resposta chegar ao usuário. A figura 1 procura ilustrar este processo.

Mas a ordenação requer que todos os registros estejam disponíveis na entrada, para ordenar adequadamente a informação. Isto porque, seja lá qual for o algoritmo utilizado, o último registro da entrada pode ser o primeiro da sequência, ou seja o primeiro a sair. E isto bloqueia o pipeline.

Portanto, é importante ter claro: ordenação é blocante e demorado, sendo altamente indesejada. Assim, se puder ser retirada da consulta, muito melhor.

Ordenação em consultas SQL

A forma mais evidente de ordenação em consultas SQL é a clausula ORDER BY, que, explicitamente, pede para o SGBD classificar o resultado por um ou mais critérios. Por óbvia e visível, é simples de ser retirada.

Como exemplo, realizei as consultas abaixo, baseadas nas tabelas do TPC-C, em um computador caseiro, utilizando Oracle 10 XP em Windows Home. Escolhi para retornar os primeiros 50 registros, algo bastante comum em aplicações atuais. A primeira consulta foi processada em 0,28 segundos. A segunda consulta, com a clausula ORDER BY, exigiu 11,28 segundos.

select OL.OL_DIST_INFO

from   OORDER OO,

ORDER_LINE OL

where   OL.OL_W_ID=OO.O_W_ID

and    OL.OL_D_ID=OO.O_D_ID

and    OL.OL_O_ID=OO.O_ID
      

select OL.OL_DIST_INFO

from   OORDER OO,
           
ORDER_LINE OL
           
where   OL.OL_W_ID=OO.O_W_ID
           
and    OL.OL_D_ID=OO.O_D_ID
           
and    OL.OL_O_ID=OO.O_ID
           
order by 1
      

Mas há outras formas mais sutis, que resultam em uma operação de ordenação. A primeira é a clausula DISTINCT no SELECT. Para eliminar as repetições, o SGBD ordena os registros e, depois, percorre-os para identificar dois registros em sequência com o mesmo valor. Incluindo nas consultas anteriores o DISTINCT, temos, para a consulta à esquerda, 12,48 segundos, uma vez que o SGBD não apenas ordena os resultados, como ainda elimina réplicas. Na consulta à direita, com DISTINCT e ORDER BY, o tempo de processamento foi 19,21 segundos, pois o SGBD ordena duas vezes: uma para eliminar réplicas, outra para mostrar o resultado.

Conclusão

Com base nestes resultados, é óbvio que, sendo possível, o melhor é evitar ordenação e distincts em consultas SQL, pois elas obrigam o SGBD a realizar uma ordenação custosa. Sendo possível retirá-la das consultas, tanto melhor.

Este artigo mostra apenas uma das alternativas para reescrever uma consulta e reduzir o tempo de processamento. Há outras alternativas, que abordarei em próximos artigos.