Curitiba,

junho 2003 # 132

Otimizando Consultas SQL no ambiente SQLServer

Autores: Flavio Augusto Weber e Elaini Simoni Angelotti

1. Introdução

Através do estudo do desempenho e otimização de consultas SQL, procura-se a minimização do tempo de resposta do servidor de banco de dados. Essa minimização influencia na produtividade de trabalho coletivo à medida que não degrada o desempenho operacional dos sistemas.

Dessa forma, todo sistema que acessa um banco de dados necessita obter as respostas às consultas em um tempo hábil, pois está em jogo não somente a paciência do usuário e credibilidade perante o mesmo, mas também valores monetários obtidos em transações comerciais. Por exemplo, no caso de instituições financeiras como bancos, a lentidão do sistema pode causar a perda de clientes e conseqüentemente seus respectivos investimentos. Outro fator importante é que, ao se melhorar o desempenho de consultas SQL, pode-se evitar gastos com investimentos na troca de software e/ou hardware, se os mesmos estiverem sendo adquiridos na tentativa de se conseguir melhor desempenho do sistema.

Quando se estuda o aprimoramento de desempenho de consultas SQL, percebe-se que, mesmo alterando ajustes de configuração e/ou incluindo hardware mais potente, as mudanças com relação ao aplicativo freqüentemente surtem maiores efeitos de desempenho junto às consultas. Os sistemas de banco de dados, sejam de qualquer plataforma, podem ser extraordinariamente rápidos e eficientes com aplicativos bem planejados e implementados. Em contrapartida, num sistema onde os aplicativos estejam mal planejados ou implementados de forma deficiente, o banco de dados terá um desempenho abaixo do esperado.

A chave para se obter um aplicativo bem projetado e implementado é considerar o fator desempenho por todo o ciclo de desenvolvimento do mesmo e não considerá-lo apenas ao término da construção para então realizar os testes de desempenho. O desenvolvedor precisa considerar o desempenho antes de começar a escrever o código do aplicativo.

Hardware e software apropriados, banco de dados normalizado, e principalmente a forma com que as consultas SQL são construídas, são itens que colaboram para a melhora do desempenho de um sistema de banco de dados.

Em alguns SGBD, existe a presença de um otimizador, como é o caso do SQLServer. O Otimizador tem como objetivo determinar um modo eficiente de implementar a requisição feita através de uma consulta SQL ao banco de dados.

2. Otimização de consultas

O processamento de consultas, segundo SILBERSCHATZ et al. (1999), é uma atividade que permite extrair dados de um banco de dados. Esta atividade inclui a tradução de consultas expressas em linguagens de alto nível do banco de dados em expressões que podem ser implementadas no nível físico do sistema de arquivos, otimizações, traduções e avaliação das consultas.

O custo do processamento de uma consulta é determinado pelo acesso ao disco. Geralmente, há diversas estratégias possíveis para processar uma determinada consulta, principalmente se ela for complexa. A diferença entre uma estratégia boa e uma ruim, em termos do número de acessos de disco exigidos é freqüentemente significativa e pode ser de grande magnitude. Conseqüentemente, vale a pena gastar uma quantia significativa de tempo na seleção de uma estratégia boa para processar uma consulta.

Conforme DATE (2000), a otimização representa ao mesmo tempo um desafio, visto que, a otimização é uma exigência para os sistemas relacionais quando se espera um desempenho que atinja níveis pré-determinados, e uma oportunidade, já que a otimização é favorecida pelo fato das expressões relacionais estarem em um nível semântico adequado para que seja possível essa otimização.

Para que o banco de dados apresente um bom desempenho é preciso partir do pressuposto da existência de uma boa estrutura de banco de dados, adequadamente normalizada e que possua índices úteis. Além disso, é preciso escrever consultas de forma otimizada. No entanto, deve-se enfatizar a importância de se conhecer como o otimizador de consultas funciona para melhor formular uma consulta ou para entender quais índices podem ser criados. Deve-se escrever as consultas da maneira mais intuitiva e tentar otimizá-las apenas se seu desempenho não parecer suficientemente bom (SOUKUP & DELANEY, 1999).

Para cada tabela envolvida na consulta SQL, o otimizador do SQLServer avalia os argumentos de pesquisa e avalia até que ponto o índice pode excluir linhas de uma seleção. Quanto mais linhas puderem ser excluídas, menor será o número de linhas a serem processadas.

Entretanto, nem sempre o otimizador realiza com êxito seu propósito pelo fato das consultas não terem sido escritas de forma eficiente. Por este motivo, deve-se conhecer as três fases que o otimizador realiza durante a execução de um consulta. A seguir, serão descritas essas três fases.

2.1 Análise da consulta

Nesta primeira fase da otimização, conforme SOUKUP & DELANEY (1999), o otimizador examina cada cláusula da consulta e determina se ela pode ser útil na limitação do volume de dados que devam ser percorridos, ou seja, se a cláusula é útil como argumento de pesquisa (SARG) ou como parte dos critérios de união.

Uma cláusula utilizada como argumento de pesquisa é referida como sargable ou otimizável e pode fazer uso de um índice para recuperação mais rápida. Uma SARG limita uma pesquisa porque especifica uma correspondência exata, um intervalo de valores ou uma conjunção de dois ou mais itens unidos pela função lógica AND.

A utilização da cláusula SARG pode fazer uso de um índice para a recuperação mais rápida de uma informação. Entretanto, uma expressão que não é sargable não pode limitar a pesquisa, portanto, um índice não é útil para essas expressões.

2.2 Seleção do índice

Na segunda fase, o otimizador de consultas determina se existe um índice para cada cláusula sargable, avalia a utilidade do índice determinando a seletividade da cláusula e estima o custo para encontrar as linhas qualificadas. A seletividade é a porcentagem expressa através da fórmula abaixo (Soukup e Delaney, 1999):

Se a relação entre o número de registros qualificados e o total de registros for baixa, o índice é altamente seletivo e útil, caso contrário o índice tem pouca seletividade e não é útil. O índice será útil quando a relação é de 5% ou menos. Caso o índice tenha uma seletividade maior de 5%, provavelmente não será utilizado, ou ainda um outro índice será escolhido ou a tabela será percorrida através de uma varredura completa nos dados.

Um índice é potencialmente útil se sua primeira coluna é usada no argumento de pesquisa e esse argumento estabelece um limite inferior, um limite superior ou ambos, para limitar a pesquisa. Além disso, se um índice contiver cada coluna referenciada em uma consulta, mesmo que nenhuma dessas colunas seja a primeira do índice, este índice é considerado útil.

A criação e utilização de índices são tarefas muito importantes para a obtenção de um bom desempenho, pois os índices podem acelerar substancialmente a recuperação e a seleção de dados.

Soukup e Delaney (1999) citam os dois tipos de índices suportados pelo SQL Server: índices setorizados (ou agrupados) e índices não setorizados (ou não agrupados). Os dois tipos de índices têm como base a árvore B (balanced) - que significa árvore balanceada. A árvore B propicia rápido acesso aos dados pesquisando em um valor-chave do índice e, pelo fato de ser balanceada, localiza qualquer registro exigindo aproximadamente o mesmo volume de recursos, sendo a velocidade de recuperação coerente, pois o índice tem o mesmo alcance por toda a parte.

A figura 1 exibe a árvore padrão B, a figura 2 exibe a árvore B para um índice setorizado e a 3 a árvore B para um índice não setorizado.

Figura 1 - Árvore B padrão para um índice do SQL Server.

Figura 2 - Árvore B para um índice setorizado.

Figura 3 - Árvore B para um índice não setorizado.

2.3. Seleção da união

Nesta terceira fase da otimização de consultas, se a consulta envolve várias tabelas ou é uma auto-união, o otimizador de consultas avalia a seleção de união e seleciona a estratégia de união com o menor custo. O otimizador pode utilizar três estratégias para processar uniões: uniões de loop aninhado, uniões de mesclagem e uniões por hash.

- União de loop aninhado: Neste tipo de união são processadas um conjunto de loops que extraem uma linha da primeira tabela e usam essa linha para percorrer a tabela mais interna e assim por diante, até que o resultado que satisfaça seja utilizado para percorrer a última tabela. O número de iterações através de qualquer um dos loops é igual ao número de varreduras que devem ser realizadas. O conjunto de resultados é reduzido à medida que ele avança de uma tabela para outra dentro de cada iteração no loop.

- União de mesclagem: Pode-se utilizar uma união de mesclagem quando nas duas tabelas a serem unidas existirem índices setorizados na coluna de união. Geralmente, o otimizador escolhe a estratégia de união de mesclagem quando as duas entradas de união (as tabelas a serem unidas) já estão classificadas na coluna união.

- União por hash: A união por hash pode ser utilizada quando não existe nenhum índice útil na coluna de união em nenhuma das entradas. O hash permite determinar se um item de dados em particular corresponde a um valor já existente, dividindo os dados existentes em grupos baseados em alguma propriedade. Os dados com o mesmo valor são colocados num hash bucket. Para verificar se um novo valor possui um correspondente nos dados existentes, simplesmente examina-se o bucket quanto ao valor correto.

3. Experimentos computacionais e resultados

Com o objetivo de demonstrar a execução e monitoramento de consultas SQL, bem como comprovar a otimização obtida através das técnicas estudadas, foram necessárias várias implementações de consultas SQL. As consultas foram construídas de maneiras diferentes, com o intuito de demonstrar a diferença de desempenho entre uma e outra forma de implementação (WEBER, 2002).

Para auxiliar o monitoramento destas consultas, foram utilizadas as opções STATISTICS IO e SHOWPLAN que fazem parte da ferramenta Query Analyzer do SQLServer.

É importante salientar o ambiente onde estes testes de validações das consultas foram realizados, pois os tempos de execuções das consultas podem variar substancialmente de um ambiente para outro. Para esse trabalho, o ambiente é composto de SGBD SQL Server 7.0, sistema operacional Windows ME, sendo executado em um computador com processador Pentium III, 1Ghz e 128 RAM.

Após cada consulta, houve a desconexão do banco de dados para que os dados retornados não permanecessem em memória cache, impedindo, dessa forma, a execução mais rápida da próxima consulta a ser realizada.

3.1. Banco de Dados Utilizado - PUBS

- O banco de dados utilizado para implementação das consultas SQL está baseado no banco de dados PUBS que acompanha o software SQL Server.

A figura 4 abaixo, exibe o Modelo Entidade-Relacionamento do banco de dados PUBS:

Figura 4 - Diagrama Entidade-Relacionamento do banco de dados PUBS

3.2. Resultados Obtidos

A seguir serão apresentados algumas técnicas que foram implementadas neste trabalho e os resultados com relação a utilização dessas técnicas de modo a melhorar o desempenho de execução das consultas.

Cláusula Where

De acordo com os testes efetuados e confirmando o que PLEW & STEPHENS (2000) afirmam, deve-se utilizar, sempre que possível, a cláusula WHERE para limitar o número de linhas retornadas pela consulta. Isto porque a utilização da cláusula WHERE evita que o SQL Server realize uma varredura total na tabela, impedindo, assim, tempo de processamento desnecessário.

Select*

Não deve-se usar SELECT * para retornar todos os atributos se for necessário apenas dados de alguns atributos, conforme explicitado por McGEHEE (2000).

A busca por todos atributos produz um trabalho adicional, já que será necessário ler a página de dados de cada linha para obter os valores dos atributos que não fazem parte do índice, caso o mesmo exista.

Cláusulas Sargable

Não deve-se utilizar cláusulas WHERE que não sejam sargable. Cláusulas não-sargable podem evitar que um índice seja aproveitado para melhorar o desempenho da consulta, pois essas cláusulas não limitam a pesquisa, ou seja, toda linha deve ser avaliada(SOUKUP & DELANEY, 1999).

Através dos testes realizados foi possível confirmar a veracidade dos fatos descritos acima.

Índice Setorizado

SOUKUP & DELANEY (1999) afirmam que é importante usar índice setorizado em consultas delimitadas por intervalo de valores.

Isso ocorre porque quando usamos um índice setorizado em consultas delimitadas por intervalo de valores ele mantém os dados fisicamente ordenados em uma tabela em relação à chave, facilitando assim a busca por um intervalo determinado de dados.

Índice Não-Setorizado

SOUKUP & DELANEY (1999) propõem a utilização de um índice não-setorizado em consultas que sejam altamente seletiva (seletividade <= 5%).

Nos testes realizados percebeu-se que a recuperação de dados usando índice não-setorizado envolve várias leituras para determinar os dados a serem retornados. Se esta consulta for altamente seletiva o número de leituras será menor e será menor também o tempo de execução dessa consulta.

Índice de Cobertura

Em consultas onde os atributos a serem retornados sejam fixos, é muito útil a inclusão destes como parte da chave do índice não-setorizado. Com isso, não há necessidade de leituras adicionais para obtenção dos outros atributos mencionados na consulta, confirmando o que SOUKUP & DELANEY (1999) afirmaram.

Cláusula Order By

PLEW & STEPHENS (2000) sugerem que a cláusula ORDER BY deve ser evitada, a menos que seja realmente necessário.

Nos testes realizados usando esta a cláusula supracitada percebeu-se que ela representa um overhead adicional na consulta, degradando o desempenho da mesma.

Ordem dos atributos do índice

Os testes realizados confirmam o que SOUKUP & DELANEY (1999) afirmaram sobre ordem dos atributos no índice. Esta ordem deve ser observada, pois o índice só é útil se os critérios da consulta correspondem aos atributos localizados mais à esquerda na chave de índice.

Um exemplo disso é uma lista telefônica, ou seja, ela está organizada em ordem alfabética composta de sobrenome e nome. Para localizar um número conhecendo-se o sobrenome basta seguir a ordem alfabética para encontrar o número desejado. No caso de tentar localizar o número conhecendo-se apenas o nome, será necessário percorrer a lista, pois o nome poderá estar em qualquer página da lista.

Ordem das tabelas em um junção

Conforme afirma PLEW & STEPHENS (2000), dependendo como o otimizador analisa a consulta, a ordem das tabelas na cláusula FROM pode fazer diferença, listando as tabelas menores primeiro. Isto porque, avaliando as tabelas menores em primeiro lugar, um subconjunto menor de dados é retornado, reduzindo os overheads da consulta.

Analisando os resultados obtidos pelas consultas, verificou-se que, independente da ordem das tabelas em uma junção, o desempenho foi idêntico, ou seja, o otimizador do SQL Server adota a mesma estratégia, indiferentemente da forma em que são posicionadas as tabelas na cláusula FROM.

Condição mais restritiva em uma junção

Segundo PLEW & STEPHENS (2000), as condições mais restritivas da cláusula WHERE devem ser avaliadas em primeiro lugar. Dessa forma, um subconjunto menor de dados é retornado, reduzindo os overheads da consulta.

Ao analisar os resultados obtidos pelas consultas, verificou-se que, independente da ordem da condição mais restritiva, o desempenho foi idêntico, ou seja, o otimizador do SQL Server adota a mesma estratégia, indiferentemente da forma em que são posicionadas as condições de restrição na cláusula WHERE. >

Ansi Join X Join SQL-92

Na tentativa de verificar se há diferença de desempenho ao utilizar duas consultas que retornam os mesmos dados mas que são escritas de forma diferente (ANSI Join e Join SQL-92), pode-se concluir que ambas as consultas são equivalentes.

Confirmando SOUKUP & DELANEY(1999), constatou-se que é gerado o mesmo plano de execução através do mecanismo empregado pelo otimizador do SQL Server para ambas as consultas, resultando em tempos de execução iguais.

Predicado IN X Operador OR

Segundo PLEW & STEPHENS (2000), deve-se evitar o uso do operador OR e no seu lugar utilizar o predicado IN, aumentado assim o desempenho da consulta.

Pode-se constatar através dos testes efetuados que o otimizador do SQL Server avalia e executa tanto o operador OR como o predicado IN da mesma forma e com o mesmo tempo de execução.

Dessa forma, a afirmação de PLEW & STEPHENS não tem comprovação neste ambiente.

Cláusula Group By

Deve-se avaliar com cuidado a utilização da cláusula GROUP BY (PLEW & STEPHENS, 2000).

A utilização da cláusula GROUP BY envolve grandes operações de classificação, fazendo com que o tempo de resposta da consulta seja prejudicado.

Cláusula Having

PLEW & STEPHENS (2000) afirmam que a utilização da cláusula HAVING deve ser evitada, pois acreditam que a mesma provoca um overhead adicional, aumentando o tempo de execução da pesquisa.

Foi constatado, através da execução das consultas, que tanto quando se utiliza a cláusula HAVING como quando não se utiliza, o desempenho é igual para ambas consultas, inclusive retornando o mesmo plano de execução.

Stored Procedures

A utilização de stored procedures (procedimentos armazenados) melhora o desempenho de consultas em um ambiente de rede, visto que os mesmos já estão pré-compilados e contém um plano de execução guardado na memória (GUNDERLOY & JORDEN, 2001).

Pelo fato das consultas não terem sido executadas em um ambiente de rede, no qual o tempo gasto para recompilar a consulta não armazenada não se mostra evidente, os resultados foram os mesmos para as consultas analisadas.

Seleção de União

O otimizador, geralmente, adota a melhor estratégia de processar uniões (loop aninhado, mesclagem ou hash) em uma consulta. Entretanto, pode-se sugerir que o mesmo adote uma estratégia diferente daquela escolhida por ele.

Com base nas execuções das consultas analisadas, ficou evidente o fato de que o otimizador nem sempre escolhe a melhor estratégia de união. Isto porque, na primeira consulta, onde o otimizador escolhe a estratégia de união, o tempo de execução ficou em 11 segundos mais lento que o melhor tempo de resposta que é de 5s, obtido através da estratégia de união por hash para esta consulta em particular.

Isso ocorreu porque através da utilização da estratégia de união por hash, os acessos às tabelas envolvidas nas leituras são reduzidas a apenas um acesso, enquanto que tanto na estratégia traçada pelo otimizador como na estratégia de união de loop aninhado, os acessos às tabelas são superiores, pois ambas estratégias realizam várias iterações que extraem uma linha da primeira tabela e a utiliza para percorrer a tabela mais interna e assim por diante, acarretando num tempo maior de execução.

Deve-se portanto, testar todas as estratégias de seleção de união e não deixar isso por conta do otimizador.

Cláusula Distinct

A cláusula DISTINCT provoca um overhead adicional na consulta, conseqüentemente degrada o desempenho da mesma. Por isso, deve-se utilizá-la com extremo cuidado, apenas em casos onde realmente seja necessário. McGEHEE, 2000)

Join X Subconsulta

Se puder escolher entre a utilização de uma JOIN ou de uma subconsulta, é interessante testar os dois métodos, pois o desempenho de cada um desses métodos pode variar para determinada consulta.

Cláusula Not In X Cláusulas Not Exists / Left Outer Join

A utilização da cláusula NOT IN numa consulta acarreta um loop aninhado, resultando em um baixo desempenho da consulta. Por isso, deve-se utilizar em seu lugar uma das seguintes cláusulas: NOT EXISTS ou LEFT OUTER JOIN checando a condição NULL, conforme afirma McGEHEE (2000).

Abaixo, na tabela 1, um quadro comparativo das técnicas de consultas SQL analisadas:

Tabela 1 - Tabela de técnicas de otimização.

4. Conclusão e trabalhos futuros

A grande maioria das técnicas de otimização aqui apresentadas, propostas por seus respectivos autores, foi devidamente comprovada como eficaz e reduziu significativamente o tempo de execução da consulta, contribuindo, assim, positivamente para o desempenho da consulta.

No entanto, houve técnicas de otimização que não se comportaram conforme previsto por seus respectivos autores, isto é, não corresponderam à expectativa de redução do tempo empregado para a execução da consulta.

Também foram apresentadas técnicas onde os tempos de execuções iguais obtidos comprovaram as afirmações feitas por seus autores, ou seja, técnicas de otimizações equivalentes entre si.

A discordância dos desempenhos das técnicas descritas por seus respectivos autores, diante do que se obteve na prática, se deve à atuação do otimizador do SQLServer. Por isso é de fundamental importância conhecer o funcionamento do mesmo.

5. Referências

1. DATE, C. J. Introdução a sistemas de banco de dados. 7. ed. Rio de Janeiro: Campus, 2000.

2.GUNDERLOY, M.; JORDEN, J. L. Dominando o SQL Server 2000. São Paulo : Makron Books, 2001.

3. MCGEHEE, B. M. Transact-SQL SQL server performance tuning tips. 2000. Disponível em: <http://www.sql-server-performance.com/transact_sql.asp>. Acesso em: 24 maio 2002.

4.PLEW, R. R.; STEPHENS, R. K. Aprenda em 24 horas SQL. 2. ed. Rio de Janeiro : Campus, 2000.

5. SILBERSCHATZ, A.; KORTH, H. F.; SUDARSHAN, S. Sistemas de banco de dados.3. ed. São Paulo: Makron Books, 1999.

6. SOUKUP, R.; DELANEY, K. Desvendando o Microsoft SQL Server 7.0. Rio de Janeiro: Campus, 1999.

7.WEBER, F. A. Análise de desempenho e otimização de consultas SQL. Curitiba, 2002. 136 f. Monografia (Graduação em Tecnologia e Processamento de Dados) - Faculdade de Ciências Exatas e Tecnologia, Universidade Tuiuti do Paraná.

Nota:
Este trabalho foi desenvolvido como uma monografia de trabalho final para obtenção de conclusão do curso de Tecnologia em Processamento de Dados da Universidade Tuiuti do Paraná, além de servir como base para a publicação do artigo "Otimização de Consultas" apresentado no Congresso Nacional de Tecnologia da Informação e Comunicação - Sucesu2003.

flaviow@celepar.gov.br
elaini.angelotti@utp.br