Pular para o conteúdo principal

Treinamento para consultas com SQL Server - Parte 10 (Final) - Introdução a índices e otimização

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

  1. Introdução
  2. Elementos básicos
  3. JOINS
  4. JOINS e o desempenho
  5. LEFT JOINS
  6. Outros tipos de JOINS
  7. WHERE
  8. UNION
  9. FUNCTIONS
  10. CASE
  11. Sub consultas
  12. FOR XML
  13. Medidas de segurança iniciais

Postagens mais visitadas deste blog

Como gerar scripts para exportar dados no SQL Server 2008

Uma das tarefas mais comuns no trabalho com desenvolvimento de software que consome dados em bancos como o SQL Server 2008 é a necessidade de em algum momento precisarmos exportar os dados de um banco para outro. Quer seja para realizar testes ou fazer simulações existem várias maneiras de se fazer isto. Neste post eu quero demonstrar um recurso do SQL Server Management Studio (SSMS) que permite realizar esta tarefa rapidamente.Para os que estão acostumados a usar esta ferramenta, já devem saber que é possível gerar scripts para o schema e também transferir os dados entre dois bancos distintos. Isto pode ser feito se o SSMS puder conectar-se com as duas bases, de origem e destino. No exemplo que vou dar, o objetivo é gerar o script apenas para uma tabela do banco de dados de exemplo da Microsoft – Northwind.1. Iniciando o assistenteO assistente deve ser iniciado clicando com o botão direito do mouse sobre o banco onde se encontra a tabela a qual iremos gerar o script. Deve se clicar n…

Pivot dinâmico com SQL Server

Passo a passo para usar pivoteamento dinâmicoOs bancos de dados bem configurados e definidos armazenam os dados de forma a otimizar o acesso, evitando duplicidade e garantindo a integridade. Porém, em muitas situações isto pode dificultar a apresentação de forma adequada sendo necessário preparar os dados usando vários recursos entre os quais, fazer o pivoteamento.Se você não precisou ainda usar ou não sabe o que é consiste em transformar cada linha de uma determinada coluna em colunas de uma nova consulta.Assim, considere uma tabela que armazene as notas bimestrais de um boletim. Uma possível estrutura para esta tabela seria algo assim:ColunaTipo de dado/TamanhoDisciplinanvarchar(50)BimestreIntNotanumeric(5,2)Uma consulta select nesta tabela com alguns dados traria um resultado parecido com o abaixo:Porém pode ser que para apresentar estes dados em um relatório seja necessário transformar cada bimestre em uma coluna e agrupar as notas nestas colunas para que fique dessa forma:Isto po…

Desabilitando o auto commit no SSMS (SQL Server Management Studio)

(Ou, como prevenir desastres e manter o emprego a salvo…)Neste post vai uma pequena mas tremendamente útil dica para desabilitar o auto commit da aplicação SQL Server Management Studio (SSMS) que é usada por dez entre dez usuários do banco de dados SQL Server para fazer consultas, alterações e executar scripts no banco de dados. (Preferências à parte, realmente muita gente usa),A primeira e mais importante notícia é que, diferentemente da ferramenta do Oracle, este editor de scripts do SQL Server vem com o recurso de auto commit ativado por padrão, assim, qualquer instrução DML (alteração dos dados com update, insert e delete) ou DDL (alteração no banco como create, drop, alter, etc.) será imediatamente enviada ao banco e persistida.Isto pode ser altamente crítico pois se estiver executando as instruções em um banco de dados de produção não haverá muitas formas de desfazer se é que haverá.Inicialmente, pode se evitar muitos acidades executando estas instruções dentro de um bloco BEGIN…