Como otimizar consultas SQL

Afinal o que s�o as Consultas SQL

Este artigo tem a finalidade de demonstrar ao desenvolvedor como as decis�es de modelagem, indexaç�o e programaç�o comprometem o desempenho e a atividade do banco de dados e o servidor como um todo. Mesmo que se tenha um servidor com boas caracter�sticas de hardware, algumas consultas podem afetar o desempenho geral do servidor. Como elas podem consumir grande parte dos recursos alocados para o processo, este artigo ir� discutir algumas t�cnicas para identificar e melhorar as consultas mais demoradas ou usadas com maior frequ�ncia.

Muitos administradores de banco de dados atribuem a arquitetura f�sica do servidor como uma das causas diretas do rendimento da execuç�o de consultas e resolvem estes problemas ajustando as caracter�sticas pr�prias do servidor como aumento do tamanho da mem�ria RAM, n�mero de processadores, o tipo do sistema de arquivos, entre outros.

Relacionado: Guia completo de SQL Server

No entanto, a maioria dos problemas de rendimentos n�o est�o unicamente relacionados � arquitetura f�sica do servidor. Na maioria das vezes eles est�o na forma como desenvolvemos as consultas. Assim, sua an�lise para posterior ajuste � essencial.

Otimizar o servidor de banco de dados � muito importante, mas melhorar o desempenho de consultas individuais pode ser ainda mais satisfat�rio. Existem v�rias formas de otimizar o banco e as consultas. Muitas vezes os bancos n�o s�o bem projetados e/ou n�o est�o normalizados. Problemas de informaç�es redundantes ou relacionamentos mal definidos provenientes de bases n�o normalizadas podem afetar de forma significante o desempenho das consultas existentes.

Saiba mais S�rie SQL n�vel Jedi: Subqueries

Este artigo aborda algumas recomendaç�es que podem melhorar o tempo de resposta do mecanismo de banco de dados de forma significativa.

Processamento do SQL

Para ser eficaz em instruç�es SQL, o profissional de banco de dados deve ter uma compreens�o profunda sobre como o SQL � transformado a partir do c�digo fonte original em uma forma execut�vel.Em um n�vel alto, o processamento SQL � dividido em v�rias etapas:

1. An�lise do c�digo fonte para localizar erros de sintaxe;

2. Uso do otimizador SQL para obter um plano de execuç�o;

3. O script � executado com base no plano elaborado;

4. Busca do conjunto de resultados do banco de dados e resposta � consulta chamada.

Geralmente, a maior parte das linguagens de programaç�o executam suas instruç�es de cima para baixo. Por�m, o SQL Server executa em uma ordem �nica, que � conhecida como fase l�gica de processamento de consulta. Estas fases originam uma s�rie de tabelas virtuais com cada uma destas alimentando a fase seguinte (tabelas virtuais ocultas). Estas fases e suas ordens s�o dadas como observado na Listagem 1.

Listagem 1. Etapas do processamento l�gico do SQL


     (8) SELECT (9) DISTINCT (11) <TOP quantidade> <lista de campos de retorno>
     (1) FROM <tabela>
     (3) <tipo de junç�o> JOIN <tabela> ON (2) <condiç�es da junç�o>
     (4) WHERE <condiç�es where >
     (5) GROUP BY <lista de agrupamento>
     (6) WITH {CUBE | ROLLUP}
     (7) HAVING <condiç�es having>
    (10) ORDER BY <lista de campos>

A seguir s�o descritos os itens visualizados na listagem e o que ocorre em cada fase do processamento:

1. FROM: um produto cartesiano (cross join) � realizado entre as duas primeiras tabelas na cl�usulafrome como resultado a tabela virtual VT1 (tabela virtualizada) � gerada;

2. ON: � aplicado em VT1. Somente as linhas para as quais a <condiç�es da junç�o> � verdadeira ser�o inseridas em VT2:

2.1. OUTER (junç�o): se for especificado umouter join(ao contr�rio de umcross joinou um inner join), as linhas da tabela preservada ou a tabela que n�o foi encontrada uma correspond�ncia s�o adicionados �s linhas de VT2 como linhas exteriores, gerando o VT3. Se mais de duas tabelas aparecem na cl�usulafrom, as etapas 1 a 3 s�o aplicadas v�rias vezes entre o resultado da �ltima associaç�o e a pr�xima tabela na cl�usulafrom, at� todas as tabelas serem processadas.

3. WHERE: � aplicado a VT3. Apenas as linhas para as quais a <condiç�es where> � verdadeira s�o inseridas a VT4;

4. GROUP BY: as linhas da VT4 s�o organizadas em grupos com base na lista de colunas especificada na cl�usulagroup by, ent�o VT5 � gerado;

5. CUBE | ROLLUP: supergrupos (grupos de grupos) s�o adicionados �s linhas da VT5 gerando VT6;

6. HAVING: � aplicado a VT6. Apenas os grupos para os quais a
<condiç�es having>� verdadeira s�o inseridos � VT7;

7. SELECT: � processada gerando VT8;

8. DISTINCT: linhas duplicadas s�o removidas da VT8 gerando a VT9;

9. ORDER BY: as linhas da VT9 s�o classificadas de acordo com a lista de coluna especificada na cl�usulaorder by. Um cursor � gerado (VC10);

10. TOP: o n�mero especificado ou porcentagem de linhas s�o selecionadas a partir da
VC10. A tabela VT11 � gerada e retorna ao executor.

As etapas b�sicas descritas para o processamento de uma instruç�o select se aplicam a outros comandos SQL como insert, update e delete.O processo de identificaç�o dessas linhas � semelhante ao procedimento utilizado para identificar as linhas de origem que colaboram para o conjunto de resultados de uma instruç�o select.As instruç�es update e insert podem conter instruç�es select, incorporadas, que fornecem os valores de dados a serem atualizados ou inseridos.

�ndices

Se suas consultas realizam buscas em tabelas grandes, uma boa recomendaç�o � o uso de �ndices. Um �ndice � uma estrutura em disco associada a uma tabela ou exibiç�o que agiliza a recuperaç�o de linhas. Us�-los � uma tomada de decis�o adequada para resolver a maior parte dos problemas de consultas com tempo demorado. Todas as chaves prim�rias precisam de �ndices para realizar as junç�es com outras tabelas de forma mais eficaz. Isso faz com que todas as tabelas precisem de uma chave prim�ria.

�ndices s�o normalmente criados em colunas que s�o acessadas com maior frequ�ncia de modo que a informaç�o possae ser recuperada mais rapidamente. Os �ndices podem ser criados em uma �nica coluna ou em um grupo delas. Quando um �ndice � criado, ele primeiro classifica os dados e, em seguida, atribui um Rowid (chave �nica e sequencial) para cada linha.

�ndices clusterizados e n�o clusterizados

A diferença b�sica entre �ndices de cluster e sem cluster � que os registros de um �ndice de cluster s�o classificados e armazenados sequencialmente com base em sua chave. O banco cria automaticamente �ndices quando uma restriç�o primary key ou unique em uma tabela � criada:

� �ndice clusterizados: um �ndice clusterizado determina a sequ�ncia de armazenamento dos registros em uma tabela. Eles s�o usados para campos em que s�o realizadas buscas frequentes ou que s�o acessados de forma ordenada. Uma tabela pode ter somente um �ndice clusterizado (pela necessidade de ordenaç�o do campo indexado);

� �ndice n�o-clusterizado: os dados s�o armazenados em local diferente do �ndice e os ponteiros indicam o armazenamento de objetos indexados na tabela. Este tipo de �ndice � utilizado quando se realizam buscas em campos onde os dados sejam �nicos. Os campos dos tipos text, ntext, image n�o podem receber esse tipo de �ndice. Para resolver esse problema, pode-se aplicar um �ndice em uma view. De uma forma geral, os �ndices do tipo n�o-clusterizados devem ser utilizados quando os campos s�o: frequentemente usados nos crit�rios de pesquisa; usados para se juntar a outras tabelas; usados como campos de chave estrangeira ou na cl�usula order by.

Views indexadas

As views indexadas podem ser usadas pelo banco de dados de duas maneiras diferentes. Primeiro, a view pode ser chamada a partir de uma consulta (que � usada convencionalmente). Basicamente, o comando � executado, utilizando um �ndice agrupado para mostrar os resultados da view quase que imediatamente. Em segundo lugar, em qualquer consulta que � executada, o gerenciador do banco de dados automaticamente avalia se existem �ndices relacionados � view. Se assim for, o otimizador de consulta usa o �ndice existente, mesmo que n�o tenha sido especificado na consulta, para assim aumentar a velocidade de execuç�o.

O primeiro �ndice criado em uma view deve ser um �ndice clusterizado exclusivo.Depois que este tipo for criado, voc� poder� criar �ndices n�o-clusterizados.Criar um �ndice clusterizado exclusivo em uma view melhora o desempenho da consulta porque a view � armazenada no banco de dados da mesma forma que uma tabela com um �ndice clusterizado � armazenada.

Para exemplificar o uso desse tipo de recurso, na Listagem 2 � criada uma tabela de log com um milh�o de registros. Esta tabela cont�m o identificador do visitante, a data da visita e do valor da operaç�o que informa se o usu�rio fez uma compra durante a visita.

Listagem 2. Criaç�o da estrutura do exemplo de utilizaç�o da view indexada


    CREATE TABLE dbo.LOG(
      ID_LOG int NOT NULL IDENTITY(1,1)
      COOKIE int NOT NULL,
      DATA_VISITA date NOT NULL,
      VALOR money NOT NULL
     )
     
    CREATE CLUSTERED INDEX IDX_LOG_DATA_VISITA 
      ON dbo.LOG (DATA_VISITA ASC) 
      WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
    DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
     
    --insere 1 milh�o de registros
    DECLARE @i int = 0, @total int = 1000000
    WHILE (@i < @total)
    BEGIN
      INSERT INTO dbo.LOG (COOKIE,DATA_VISITA,VALOR)
      SELECT RAND()*@total/100 --COOKIE
          , DATEADD(day,CONVERT(INT,(RAND()*500)),"20130101") �-DATA VISITA
          , CASE WHEN RAND()<0.01 THEN RAND()*10.0 ELSE 0.0 END --VALOR
     
      SET @i+=1
    END

Na Listagem 3 executa-se um exemplo de consulta para informar o n�mero de visitas, o valor total de compras e a receita por visita de cada cookie.

Listagem 3. Consulta de exemplo


    SELECT COOKIE
      , <span class="lf-badge">count</span>(*) AS FREQUENCIA
      , <span class="lf-badge">sum</span>(VALOR) AS VALOR_TOTAL
      , AVG(VALOR) AS VALOR_MEDIA
    FROM DBO.LOG
    GROUP BY COOKIE

Em m�dia, esta consulta levou 577 milissegundos nos testes executados, mas pode-se aceler�-la usando um �ndice n�o clusterizado como observado no comando a seguir:


    <p align="left"> 
      CREATE NONCLUSTERED INDEX [IDX_LOG_VW_LOG] ON [LOG] ([COOKIE]) 
      INCLUDE ( [VALOR]) ON [PRIMARY]

Ao executar novamente a consulta apresentada, ela � executada agora 212 milissegundos, uma melhoria de 63,25% no desempenho. Entretanto, pode-se aumentar esse ganho de desempenho usando uma view indexada em vez de criar o �ndice anterior, como demonstrado na Listagem 4.

Listagem 4. Criaç�o da view indexada.


    CREATE VIEW [VW_LOG] WITH SCHEMABINDING AS
      SELECT COOKIE
        , <span class="lf-badge">sum</span>(VALOR) AS VALOR
        , <span class="lf-badge">count</span>_BIG(*) AS FREQUENCIA
      FROM DBO.LOG
      GROUP BY COOKIE
     
    CREATE UNIQUE CLUSTERED INDEX IDX_VW_LOG ON [VW_LOG] (COOKIE);

Executando novamente a consulta da Listagem 3, ela roda agora em 56 milissegundos em m�dia, um ganho de desempenho de 90,29%.

Mesmo que a m�dia agregada (avg) n�o esteja definida na view, o otimizador de consulta � capaz de obter o resultado utilizando os valores do count e do sum. Caso a view tenha uma quantidade grande de dados, pode-se tamb�m criar �ndices n�o clusterizados para aumentar a velocidade de acesso �s informaç�es.

Views indexadas tamb�m s�o uma �tima forma de melhorar o desempenho de inner joins. Quando duas ou mais tabelas se relacionam em uma view indexada, o otimizador de consulta pode escolher recuperar os dados diretamente da view em vez de executar uma custosa operaç�o de junç�o.

Outras pr�ticas com melhor performance de execuç�o

Existem outras particularidades que podem ser adotadas para que as consultas tenham uma melhor performance.

Uso de Union

Um comando union equivale a fazermos a junç�o de dois conjuntos eliminando, em seguida, os elementos duplicados (o que poderia ser feitos atrav�s de um comando distinct). Se sabemos que existem registros duplicados e isso representa um problema para a aplicaç�o, ent�o devemos utilizar o union para elimin�-los. Por outro lado, se n�o haver� linhas duplicadas ou se n�o � um problema t�-las, utiliza-se o union all em vez de union. A vantagem do union all � que ele n�o realiza o distinct, evitando o desperd�cio de recursos do servidor SQL.

Para exemplificar, veja a Listagem 5. Imagine que se quer realizar uma consulta para mesclar dois conjuntos de dados.

Listagem 5. Exemplo do comando Union.


  (1)
  SELECT nome_columa1, nome_columa2 
     FROM tabela1 
     WHERE nome_columa1 = value 
     UNION 
     SELECT nome_columa1, nome_columa2 
     FROM tabela1 
     WHERE nome_columa2 = value
      
  (2)
  SELECT DISTINCT nome_columa1, nome_columa2 
     FROM tabela1 
     WHERE nome_columa1 = value OR nome_columa2 = value

A consulta marcada com o n�mero (2) � executada de forma mais r�pida do que a (1). E isso pode ser melhorado (sabendo que a uni�o desses dois conjuntos de dados cont�m elementos duplicados) removendo o comando distinct.

Relaç�o entre tabelas

� bastante comum realizar uma comparaç�o e relaç�o entre tabelas. Na Listagem 6 s�o exemplificadas tr�s formas de executar essa operaç�o.

Listagem 6. Exemplo de relaç�o entre tabelas.


  (1)
  SELECT a.nome_columa1
     FROM tabela1 a 
     WHERE NOT EXISTS (SELECT b.nome_columa2 FROM tabela2 b WHERE b.nome_columa2   = a.nome_columa1)
      
  (2)
  SELECT a.nome_columa1
     FROM tabela1 a 
       LEFT JOIN tabela2 b ON b.nome_columa2   = a.nome_columa1
     WHERE b.nome_columa2 IS NULL
      
  (3)
  SELECT nome_columa1
     FROM tabela1
     WHERE nome_columa1 NOT IN (SELECT nome_columa2 FROM tabela2)

Em cada uma das consultas apresentadas o resultado � o mesmo. Por�m, qual delas tem a melhor performance? Assumindo que todo o resto � igual, a vers�o que tem o melhor desempenho � a primeira (1) e a �ltima (3) � a pior. O comando not exists (ou exists) � o mais eficiente.

Uso do comando Group By

A cl�usula group by pode ser usada com ou sem uma funç�o agregada (max, sum, count, avg, ...). Para obtermos um melhor desempenho, n�o devemos utiliz�-la sem uma funç�o agregada. Observe a Listagem 7.

Listagem 7. Utilizaç�o do comando Group by.


  (1)
  SELECT nome_columa1, nome_columa2 
     FROM tabela1 
     WHERE nome_columa1 > value
     GROUP BY nome_columa1, nome_columa2
      
  (2)
  SELECT DISTINCT nome_columa1, nome_columa2 
     FROM tabela1 
     WHERE nome_columa1 > value

Ambas as consultas retornam os mesmos resultados, por�m a segunda obt�m um melhor desempenho. Para melhorar a performance ao utilizarmos a cl�usula group by, deve-se considerar as seguintes recomendaç�es:

� O n�mero de linhas de retorno a partir da consulta deve ser o menor poss�vel;

� Manter o n�mero de agrupamentos o mais limitado poss�vel;

� N�o agrupar colunas redundantes;

� Se existe um join na mesma instruç�o select que tem um group by, tente reescrever uma consulta utilizando uma subconsulta em vez de usar o join. Se for poss�vel fazer isso, o desempenho ser� melhor. Se for necess�rio usar um join, utilize as colunas do group by com a mesma coluna da tabela em que a funç�o est� sendo usada;

� Considere adicionar um order by para a(s) mesma(s) coluna(s) existente(s) no group by. Isso pode fazer com que ele tenha um melhor desempenho.

A otimizaç�o de consultas � um trabalho extremamente importante para a gest�o e manutenç�o de uma base de dados. A finalidade de se otimizar consultas e gerenciar as estruturas e �ndices de um banco � livrar os usu�rios de suas complicaç�es e das exig�ncias indispens�veis para se obter consultas eficientes.

Links �teis

Saiba mais sobre SQL Server ;)

Processamento de consultas no SQL Server:
Este artigo apresenta como funciona o processamento de uma consulta no SQL Server, identificando os principais mecanismos e componentes desse sistema.
Performance no SQL Server: eliminando o operador Sort:
Veja neste artigo como otimizar a performance de consultas no SQL Server atrav�s da remoç�o do operador Sort.
Schemas no SQL Server:
Veja neste artigo informaç�es sobre Schemas, o que �? Como criar? Como usar ? Como alterar ? Os Schemas s�o uma coleç�o de objetos dentro de um determinado database.

Otimiza��o de Consultas SQL

Será discutido neste artigo como podemos proceder para aumentar a performance de consultas SQL. Serão apresentadas algumas técnicas para identificar e melhorar as consultas mais demoradas ou usadas com maior frequência.