Um banco de dados com alto desempenho em todas as consultas é como a ciência de construir foguetes: difícil, inexata e cada caso merece uma atenção especial. Por isso neste tópico não é possível colocar o assunto em pauta, uma vez que demandaria um tempo muito grande. Por isso, serão tratados alguns casos que penalizam as consultas por não utilizarem corretamente a indexação do banco e que devem ser evitados ou mitigados sempre que forem encontrados.
No final, colocarei uma dica sobre como construir índices eficientes no SQL Server, porque, infelizmente, não basta que eles existam, tem de ser criados do jeito certo.
| Alerta sobre desempenho |
| A preocupação com o desempenho das consultas deve estar embutida no design do banco de dados. É muito mais difícil otimizar um banco com milhões de registros com gargalos de desempenho e vários usuários conectados o tempo todo. Verifique a previsão de crescimento do banco ao realizar seu projeto. (Ou peça que seu líder de projeto faça isso). |
Like
Esta instrução permite que colocando o caractere curinga ‘%’ no início, final ou em ambas as partes de uma expressão de busca na cláusula WHERE, o mecanismo faça uma busca parcial pelo conteúdo no início, final ou em qualquer parte, como demonstrado a seguir.
Busca no início da coluna
SELECT * FROM CUSTOMERS WHERE NAME LIKE ‘VLADIMIR%’
Busca no final da coluna
SELECT * FROM CUSTOMERS WHERE NAME LIKE ‘%VLADIMIR’
Busca em qualquer parte da coluna
SELECT * FROM CUSTOMERS WHERE NAME LIKE ‘%VLADIMIR%’
O problema reside neste último tipo. Caso a coluna NAME esteja indexada, nos dois primeiros exemplos a busca é feita usando-se o índice, porém, não é feito para o último exemplo simplesmente por não servir para nada, uma vez que todo conteúdo da coluna e da tabela terá de ser percorrido.
Não existe uma forma eficiente de se fazer isto. O melhor é evitar. Se não for possível, verifique a possibilidade de usar FULL-TEXT INDEXES no seu banco pois, assim, a busca é executada de forma mais eficiente.
Usando funções nas expressões
No exemplo do tópico sobre sub consultas (SUB CONSULTAS (SUBQUERIES)) para filtrar a soma dos totais das notas dentro de cada ano, foi aplicada a função YEAR() sobre um campo de data. Porém, isto não foi feito na consulta exemplificando o uso de FOR XML no tópico seguinte, mesmo sendo essencialmente a mesma consulta.
Isto ocorreu para demonstrar uma forma de substituir este tipo de consulta – que usa funções sobre condições dentro da cláusula WHERE. Ao usar funções de transformação de dados especialmente que alterem o tipo do dado da coluna novamente o mecanismo de busca do banco, ignora completamente o índice da tabela, se existir. Por que?
Porque no caso da consulta citada acima, o tipo da coluna é DateTime. O índice leva em conta o tipo de coluna para fazer a ordenação. Ao extrair o ano de uma data como o indexador irá comparar dois tipos de dados diferentes? De forma precária, precisará converter o valor da coluna para o tipo de dado usado na condição e então realizar a comparação.
A melhor maneira de otimizar é evitar usar funções de transformação sobre uma coluna em uma cláusula WHERE. Algumas alternativas:
- Para colunas do tipo DateTime ou Date, se for recuperar os dados referente a um determinado período (mês, ano, etc.), trate os parâmetros antes enviando duas datas – de início e de fim – em conjunto com BETWEEN.
- Para colunas do tipo texto, não é necessário realizar conversões para maiúsculas, minúsculas. Verifique o COLLATION do banco e da tabela e se necessário, faça ajustes (ou peça para o administrador do banco fazer).
- Ainda para colunas do tipo texto, não use SUBSTRING, RIGHT ou LEFT. Use LIKE, do jeito certo.
IS NULL
Este é um ponto polêmico de qualquer banco de dados. Há inúmeros casos onde campos permitem dados nulos (NULL) e mais casos ainda onde é necessário filtrar os resultados para trazer dados obedecendo a este critério:
SELECT <colunas> FROM <tabelas> WHERE <coluna> IS NULL | <coluna> IS NOT NULL
Quando este tipo de cláusula é usado, não tem jeito. Qualquer tipo de banco de dados irá ignorar “solenemente” qualquer índice que exista. Simples e direto assim, pode buscar na Internet.
A solução? Não tenha colunas que permitam valores nulos. (Igualmente simples, direto e decepcionante...).
Como criar índices eficazes no SQL Server
Este item fica como um lembrete, mais do que um ponto de nivelamento de conhecimentos. Existem dois tipos de índices no SQL Server quanto a forma de armazenamento dos dados para otimização de consulta:
Índices clusterizados
Normalmente representam o principal índice e que é usado para a chave primária da tabela. Neste tipo, além busca ser feita nas colunas que compõem a chave primária, todas as colunas da tabela são armazenadas pelo índice. Desta forma, ao realizar uma busca por este tipo de índice, apenas uma varredura é feita e qualquer que seja a coluna necessária para recuperar o dado estará armazenada no índice. As tabelas do SQL Server só podem ter um índice clusterizado por tabela (pelo menos até a versão 2014).
Índices não clusterizados
Via de regra, são todos os demais tipos de índice.
Neste tipo são armazenadas as colunas usadas para a busca e a chave primária.
Ao realizar a busca, caso o índice tenha sido feito da forma padrão, duas varreduras são feitas no banco de dados, a primeira passa pelas colunas indexadas comparando o valor passado por argumento. Uma vez encontrado, o valor da chave primária é armazenado. Caso as colunas que estejam sendo recuperadas pela cláusula SELECT não estejam armazenadas, o mecanismo percorre o índice clusterizado, buscando a chave primária para obter o valor das demais colunas.
Mas isto não precisa ser sempre deste jeito. No SQL Server é possível através da cláusula INCLUDE especificar quais colunas serão armazenadas para agilizar a busca. Caso estas estejam especificadas na cláusula SELECT, somente uma varredura é feita.
A sintaxe básica para se criar um índice deste tipo é a demonstrada na sequência:
CREATE INDEX <NOME> ON <TABELA> (Coluna1, coluna2, …, ColunaN) INCLUDE (Coluna1, coluna2, …, ColunaN)
Existem considerações a fazer sobre este tipo de índice:
- Quanto mais colunas, mais espaço de armazenamento será necessário
- É possível ter um grande número destes índices no banco
- É importante realizar um estudo de desempenho para criar apenas os índices necessários e adequados às consultas que são comumente executadas.
- Quanto maior o número de índices, menor o tempo de resposta das consultas e maior o tempo de atualização pois cada UPDATE, DELETE e INSERT dispara atualizações nos índices.
A criação de índices é um assunto que precisa ser profundamente estudado. Este conteúdo resumido serve apenas para alertar quais as principais causas de gargalos de desempenho nas consultas e por onde começar a fazer sua pesquisa. O principal aspecto a ser considerado para se ter um banco de dados com bom desempenho é incluir esta questão no momento de se fazer o projeto do banco e escolher de maneira mais correta qual arquitetura deverá ser usada.
Este post encerra a série de tópicos básicos para se usar consultas com o SQL Server. Espero ter colaborado de alguma forma para o seu aprendizado. Fique atento, sempre estou publicando atualizações neste blog. Deixo abaixo links para mais referências e o índice de todos os itens do treinamento.
Referências
Índice dos treinamentos desta série
- Introdução
- Elementos básicos
- JOINS
- JOINS e o desempenho
- LEFT JOINS
- Outros tipos de JOINS
- WHERE
- UNION
- FUNCTIONS
- CASE
- Sub consultas
- FOR XML
- Medidas de segurança iniciais
