10 técnicas de otimização de consultas SQL

As organizações estão recorrendo cada vez mais à Tecnologia da Informação e Comunicação para gerenciar e armazenar os dados que são processados e manipulados no dia a dia. Com isso, o volume de dados vem aumentando consideravelmente, evidenciando a necessidade de otimização dos Sistemas Gerenciadores de Banco de Dados (SGBDS).

Dessa maneira, é primordial que as operações realizadas no banco de dados sejam eficazes e processadas no tempo certo para a tomada de decisão. Para garantir o desempenho e a eficiência das informações acessadas no banco, a otimização deve ser almejada pelos Administradores de Banco de Dados (DBA), em especial a efetivação de consultas que gerem informações rápidas e precisas.

Existem diferentes estratégias de otimização de um sistema de banco de dados baseado no SQL Server, sendo possível aumentar a eficiência e eficácia dos processos. Muitos trabalhos citam técnicas de otimização de consultas em bancos de dados, mostrando sua aplicação e descrevendo os seus benefícios. Contudo até o presente momento nenhum fez um levantamento real do ganho em processamento e tempo na utilização destas técnicas focado diretamente no banco de dados SQL Server.

As técnicas de otimização de consultas que serão analisadas correspondem às de modelagem e programação das consultas no qual o desenvolvedor pode remodela-las para obter maior desempenho no banco de dados. No decorrer do artigo, buscaremos responder a seguinte pergunta: Quais os benefícios em se aplicar algumas técnicas de otimização de consultas em um banco de dados SQL Server? O objetivo central que orienta este artigo é mostrar os benefícios da aplicabilidade de algumas técnicas de otimizações de consultas utilizando o SQL server. Para isso, será feita uma análise das consultas com e sem a utilização das técnicas, comparando o tempo e desempenho de resposta de ambos conseguindo assim demostrar a sua vantagem.

Ferramentas

Neste artigo utilizamos o SQL Server por ser um dos SGBD mais utilizados nas empresas. A execução das consultas com o uso das técnicas de otimização foi realizada na versão 2016, utilizando o banco de dados AdventureWorks 2016CTP3, sendo analisado o plano de execução, estatísticas de tempo, e estatísticas de entrada e saída para obter os resultados. Em todas as consultas foram executados os comandos “DBCCDROPCLEANBUFFERS” e “DBCCFREEPROCCACHE” para limpar o cache do SQL Server, afim de obter um resultado mais preciso das estatísticas.

O plano de execução calcula o caminho mais eficiente para implementar uma requisição representada pelo T-SQL, possibilitando gerar um diagrama com o fluxo percorrido para obter os resultados solicitados, mostrando os índices utilizados, e informações que ajudam o gerenciador de banco no processo de otimização de consultas. Na Figura 1 é apresentado um exemplo de plano de execução.

A estatística de tempo é visualizada ao executar o comando “SET STATISTICS TIME ON”. Ela mostra o tempo em milissegundos necessário para analisar, compilar e executar cada instrução. A estatística possui o tempo de CPU que é o tempo gasto pela CPU do servidor para processar a consulta e o tempo decorrido que se refere ao tempo de execução da consulta. O valor do tempo da CPU pode variar de acordo com a execução, mas a diferença é mínima. Já o tempo decorrido pode ter uma grande variação pois depende de muitos fatores, como largura de banda de rede entre servidor e cliente, carga no servidor e carga IO. Neste artigo vamos analisar apenas o tempo de execução das consultas. Na Figura 2 é demonstrado o resultado da estatística de tempo de uma consulta.

A estatística de entrada e saída é visualizada ao executar o comando “SET STATISTICS OI ON”. Ela exibe os dados referentes à quantidade de atividades geradas em disco pelas consultas, onde temos:

1. Tabela: nome da tabela envolvida na consulta;
2. Número de verificações: número de buscas iniciadas para recuperar todos os valores para saída final;
3. Leituras lógicas: número de páginas lidas do cache de dados do SQL Server;
4. Leituras físicas: número de páginas lidas no disco do servidor;
5. Leituras read-ahead: número de páginas colocadas no cache para a consulta;
6. Leituras lógicas lob: número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) lidas do cache;
7. Leituras físicas lob: número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) lidas do disco do servidor;
8. Leituras read-ahead lob: número de colunas com valor grande: VARCHAR(MAX), VARCHAR(MAX) e VARBINARY(MAX) adicionadas no cache de dados.

As leituras de lob não foram analisadas no artigo pois em todas as consultas os resultados foram 0 por não possuírem colunas com valor grande. Na Figura 3 é apresentado um exemplo.

Banco de dados

Um banco de dados é definido como um conjunto de dados armazenados e relacionados para atender as necessidades dos usuários, possibilitando a consulta e manipulação dos dados, seja manual ou computadorizada. Com o passar do tempo e com o avanço tecnológico, os bancos de dados passaram a ser considerados o coração de muitos sistemas, pois oferecem segurança dos dados e ainda auxiliam no acesso rápido às informações para tomada de decisão.

Para gerenciar o grande volume de dados é necessário a utilização de um SGBD que oferece uma interface que possibilita a inclusão, alteração e consulta dos dados armazenados no banco de dados. Dentre os SGBDs mais utilizados podemos citar o Oracle, MySQL, Microsoft SQL Server, PostgreSQL e MongoDB.

Linguagem SQL

A linguagem SQL foi criada durante o processo de desenvolvimento dos bancos de dados relacionais. Em meados da década de 1970, o departamento de pesquisa da IBM criou a linguagem SQL denominada SYSTEM R. A linguagem recebeu o nome de SEQUEL (structured english query language). Em 1977 a linguagem passou a ser chamada de SQL (Structured Query Language). Em 1986 o American National Standard Institute – ANSI, junto com a Organização Internac ...