Clausulas desnecessárias em consultas SQL

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (0)  (1)

Veja neste artigo clausulas desnecessárias em consultas SQL.

Clausulas desnecessárias em consultas SQL

 

por Miguel Fornari

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.

 

Tabela 1. custo de um algoritmo de ordenação, variando o número de tuplas.

Numero de tuplas (n)

Custo – número de operações

Custo normalizado

50.000

780.482

1,00

100.000

1.660.964

2,13

200.000

3.521.928

4,51

400.000

7.443.856

9,54

 

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.

16-02-07pic01.JPG

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 consulta à esquerda foi processada em 0,28 segundos. A consulta à direita, 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.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?