artigo SQL Magazine 11 - Ajustes de Desempenho em “Consultas Simples” na SQL

Ajustes de Desempenho em “Consultas Simples” na SQL

Neste artigo, falaremos sobre a otimização de consultas

simples baseada na sintaxe. Aprenderemos quais condições de pesquisa são melhores e, ciente dessas informações, decidiremos se será necessário alterar a ordem das expressões ou substituir uma expressão por outra que desempenhe a mesma função de forma mais eficiente.

Para otimizar uma consulta com base na sintaxe, devemos desconsiderar os fatores não sintáticos (por exemplo, índices, tamanhos de tabela, armazenamento).

Antes de começar, vale ressaltar que não adianta tentar otimizar grande parte da sintaxe SQL, pois somente algumas instruções SQL possuem opções que tornam isto possível. A sintaxe que oferece muitas possibilidades de otimização são as condições de consulta SQL. Veja aqui três exemplos de condições de pesquisa:

... WHERE title LIKE ‘The %’ OR title LIKE ‘A %’

... WHERE name <> ‘Smith’

... WHERE number = 5

Apesar das consultas mais lentas serem as que contêm junções e subconsultas, este artigo trata somente das consultas em tabela única. Além disso, apesar das condições de pesquisa poderem aparecer em cláusulas HAVING, IF ou ON, trataremos somente das que aparecem em cláusulas WHERE.

Ajuste geral

Nesta parte do artigo, trataremos de algumas idéias gerais que você deve ter em mente ao escrever condições de consultas simples.

Código para pontos

As melhores condições de pesquisa são as que trabalham com poucas linhas e comparações fáceis. As Tabelas 1 e 2 mostram listas típicas de tipos de condições de pesquisa, ordenadas da melhor para a pior. Cada componente da condição da pesquisa possui uma “contagem de pontos”: quanto melhor o componente, mais alta a pontuação. Você pode ver pelos pontos distribuídos mostrados nas Tabelas 1 e 2 que a melhor condição de pesquisa seria algo como:

... WHERE smallint_column = 12345

Esse exemplo obtém um total de 27 pontos, calculados da

seguinte maneira:

• 5 pontos para a coluna (smallint_column) sozinha à esquerda;

• 2 pontos pelo fato do tipo de dados operando (smallint_column)

ser numérico exato;

• 10 pontos para o operador de igualdade;

• 10 pontos para o literal (12345) sozinho à direita.

Veja outro exemplo:

... WHERE char_column >= varchar_column || ‘x’

A contagem de pontos para esse tipo de condição de pesquisa

é muito inferior: somente 13.

• 5 pontos para a coluna (char_column) sozinha à esquerda;

• 0 ponto par a o tipo de dados oper ando CHAR

(char_column);

• 5 pontos para o operador maior que ou igual a;

• 3 pontos para a expressão com vários operandos (varchar_column || ‘x’) à direita;

T1

Operador                                                      Pontos

   =                                                                  10

   >                                                                   5

  >=                                                                 5

  <                                                                    5

<=                                                                   5

LIKE                                                                  3

<>                                                                   0

Contagens de pontos da condição de pesquisa para operadores.

T2

Operando                                                     Pontos

Literal sozinho                                       10

Coluna sozinha                                       5

Parâmetro sozinho                                  5

Expressão com vários operandos               3

Tipo de dados numérico exato                  2

Outro tipo de dados numérico                   1

Tipo de dados temporal                           1

Tipo de dados caractere                          0

NULO 0

Contagens de pontos da condição de pesquisa para operandos.

• 0 ponto para o tipo de dados operando VARCHAR (varchar_column).

A contagem de pontos precisa para uma condição de pesquisa varia a depender do fornecedor. Então não adianta memorizar nada além da ordem e do conceito desta

técnica de otimização. Lembre-se apenas que a condição que leva menos tempo

obtém mais pontos.

Tendo conhecimento desse conceito, você pode decidir se irá mudar a ordem das expressões ou substituir uma expressão por

outra que desempenhe a mesma função. Apesar de um otimizador de um SGBD

moderno possuir muito mais regras que exigem informações fora do escopo da própria

instrução SQL, todos os SGBDs recorrem à contagem de pontos quando não há outras

informações disponíveis.

Uma outra maneira de otimizar uma condição de pesquisa é colocando várias

expressões na ordem correta. As expressões nesta cláusula WHERE já estão na ordem

ideal:

SELECT * FROM Table1

WHERE column1 = 5

AND column2 = 77.3

AND column3 = ‘Smith’

AND column4 < 117

AND column4 > column5

GANHO: 0/8

A observação na parte inferior deste exemplo nos informa que há um ganho

de 0/8. O ganho mostra quantas vezes a consulta é executada mais rapidamente

se comparada à pesquisa otimizada pelo SGBD (a pesquisa foi executada em SGBDs

de 8 fabricantes). Neste caso, nos 8 SGBDs que a consulta foi executada, não

houve ganho de desempenho. Esse valor varia conforme os dados e as máquinas, evidentemente.

Então “GANHO: 0/8” significa: “você perderia o seu tempo se reorganizasse

esta cláusula WHERE em outra ordem pois o SGBD faz isso para você”. Todos os

fabricantes de SGBD possuem o conhecimento básico da contagem de pontos,

por isso a reorganização é automática. Isso significa que, em situações comuns, você

não ganhará nada fazendo sua própria otimização baseada na sintaxe. No entanto,

há muitas exceções a essa regra. No restante deste artigo, analisaremos algumas

dessas situações.

Propagação constante

Formalmente, a Lei da Transitividade afirma que:

IF

(A B) IS TRUE AND

(B C) IS TRUE

THEN

(A C) IS TRUE AND NOT

(A C) IS FALSE

Onde o operador de comparação (comparison operator) é um dos seguintes: = ou

> ou >= ou < ou <= mas nenhum destes: <> ou LIKE.

A Lei da Transitividade leva à observação simples de que podemos substituir B por C sem alterar o significado de uma expressão. Quando uma substituição dessas “variáveis” envolve a substituição de um valor constante, o processo é chamado de propagação constante.

As duas expressões a seguir possuem o mesmo significado, mas a segunda possui uma melhor contagem de pontos porque ela substitui o nome de coluna (column1) por um literal (5):

Expressão #1

... WHERE column1 < column2

AND column2 = column3

AND column1 = 5

Expressão #2

... WHERE 5 < column2

AND column2 = column3

AND column1 = 5

GANHO: 2/8

Expressão #2 é chamada de transformação da Expressão #1 (escrever uma transformação significa reescrever uma instrução SQL

para produzir o mesmo resultado, mas com sintaxe diferente. Quando duas instruções

SQL possuem sintaxes diferentes, mas que produzirão de forma previsível e regular

as mesmas saídas, elas são chamadas de transformações uma da outra). A maioria

dos bons SGBDs efetuam essa ação automaticamente. Mas alguns não tentarão transformações quando a expressão contiver vários parênteses e NOTs. Por exemplo, esta instrução SELECT pode ser lenta:

SELECT * FROM Table1

WHERE column1 = 5 AND

NOT (column3 = 7 OR column1 = column2)

Aplicando nós mesmos as transformações, chegamos a esta instrução:

SELECT * FROM Table1

WHERE column1 = 5

AND column3 <> 7

AND column2 <> 5

GANHO: 5/8

A instrução transformada é mais de 50% mais rápida. Em outras palavras, às vezes vale a pena fazer suas próprias transformações.

         Às vezes, a propagação constante não funcionará com flutuantes, pois pode haver ao mesmo tempo “maior que” e “igual a” quando ocorrem comparações numéricas aproximadas. Se funcionar, porém, espere GANHO: 5/8.

Na prática, você verá muitos operandos semiconstantes, como parâmetros ou funções de programa. São exemplos as funções nulas como CURRENT_DATE (uma função nula é uma função sem argumentos). Como o uso de um valor constante sempre acelera os acessos, tente uma transformação para agilizar esses casos. Veja aqui um exemplo:

Query #1 se transforma em Query #2:

Query #1:

SELECT * FROM Table1

WHERE date_column = CURRENT_DATE

AND amount * 5 > 100.00

Query #2:

SELECT * FROM Table1

WHERE date_column = DATE ‘2002-01-01’

AND amount * 5 > 100.00

GANHO: 5/8

Se você estiver pensando em transformar esse tipo de expressão, lembre-se de que

(devido à constante DATE) precisará alterar a consulta todos os dias. Isso só é prático

quando um programa gera as consultas no servidor.

Eliminação de código morto

(Dead Code)

Em alguns programas SQL antigos, você encontrará literais dos dois lados do operador

de comparação, como neste exemplo:

SELECT * FROM Table1

WHERE 0 = 1

AND column1 = ‘I hope we never execute this’

Antes de serem permitidos os /* comentários */ em uma instrução SQL, essa era uma maneira de incluir uma seqüência de comentário em linha. Como a expressão

0 = 1 é sempre falsa, essa consulta não retornará linha alguma; portanto, os SGBDs

poderão ignorar a cláusula WHERE inteira. Mas alguns não a ignorarão. Testamos isso

removendo a cláusula WHERE e obtivemos um ganho!

SELECT * FROM Table1

GANHO: 5/8

É óbvio que essas duas consultas não são equivalentes. A questão é simplesmente que se deve levar menos tempo para recuperar

zero linhas devido a uma condição sempre falsa do que se levaria para fazer

uma leitura de tabela inteira, desde que o SGBD não avaliasse a condição sempre

falsa. Esse exemplo mostra que os SGBDs nem sempre rejeitam condições sempre

falsas e todos os seus dependentes no estágio de PREPARAÇÃO. Mas eles rejeitam

condições sempre verdadeiras. Então, você pode usar condições sempre verdadeiras

para um equivalente SQL de compilação condicional. Por exemplo, se você teme que

um SGBD não ofereça alta precisão para resultados de divisão, inclua uma condição

à parte que só seja usada quando necessário – como neste exemplo:

... WHERE (77 / 10 = 7.7

AND column1 / 10 = 7.7)

OR (77 / 10 = 7 AND column1 * 10 = 77)

GANHO: 5/8

No entanto, devido ao aspecto de pouca confiabilidade, não é uma boa idéia usar código redundante. Suponha que uma coluna, indexed_column, seja uma coluna NOT NULL indexada. Você pode transformar essa instrução SQL:

SELECT * FROM Table1

na seguinte instrução:

SELECT * FROM Table1

WHERE indexed_column > 0

Essa é uma maneira de forçar o SGBD a pesquisar pelo índice. Vale ressaltar que isso só funciona com alguns SGBDs. Em termos gerais, não inclua condições redundantes em cláusulas WHERE.

Certifique-se de usar o SGBD correto

Existem várias maneiras de garantir que um SGBD específico execute uma expressão. Veja aqui três exemplos, todos usando extensões SQL não-padrão:

Exemplo 1:

... WHERE :variable = ‘Oracle’

AND /* código específico para o Oracle

aqui */

Exemplo 2:

SELECT /* ! HIGH_PRIORITY */ ...

/* todos SGBDs com excessão do MySQL

ignoram isto */

Exemplo 3:

... WHERE AND /* código

ODBC */

O Oracle permite a inclusão de um comentário que indique qual índice deseja

usar. Seria mais ou menos assim:

SELECT /*+ INDEX(Widgets Widget_index) */

column1, column2, column3

FROM Widgets

WHERE column1 <> 7;

GANHO: apenas 1/8 por causa das funcionalidades específicas do Oracle

As otimizações específicas ao Oracle não são boas idéias quando prendem você a ele.

Nesse caso, a dica está em um comentário e por isso, outros SGBDs irão ignorá-la.

Utilizar mecanismos específicos de cada banco para otimizar a consulta é uma boa

desde que não entre em conflito com os demais SGBDs.

Resumos óbvios

Todos que estejam acostumados com C, sabem que a expressão x=1+1-1-1 é

resumida em x = 0 durante a compilação. Então, pode ser surpresa para você

que muitos SGBDs não resumam esses cinco candidatos à transformação aparentemente óbvios:

... WHERE column1 + 0

... WHERE 5 + 0.0

... WHERE column1 IN (1, 3, 3)

... CAST(1 AS INTEGER)

... WHERE ‘a’ || ‘b’

Porém, se você encontrar expressões como essas em código antigo, nossa dica

é: deixe-as isoladas. Elas estão lá por razões históricas, como forçar o SGBD

a ignorar índices, alterar o tipo de dados de resultado, permitir a diferença

entre SMALLINT e INTEGER ou fugir de um limite de tamanho de linha. Sinto

muito, mas os casos aparentemente óbvios são precisamente aqueles em que

você deve parar e se perguntar se o programador original teve algum motivo para

a estranha escolha de sintaxe. Contudo, recomendamos que você transforme esta condição de pesquisa:

... WHERE a - 3 = 5

em:

... WHERE a = 8 /* a - 3 = 5 */

GANHO: 6/8

Pesquisas que não diferenciam maiúsculas de minúsculas

 O Access Jet da Microsoft considera as seqüências ‘SMITH’ e ‘Smith’ iguais, ou seja, ele não diferencia maiúsculas de minúsculas. O Oracle, por outro lado, normalmente faz essa distinção (o mecanismo diria que ‘SMITH’ e ‘Smith’ são seqüências diferentes). O Sybase permite que você decida sobre esse tipo de distinção quando você faz a instalação, e um SGBD padrão SQL permitirá que você altere essa capacidade de distinção em tempo de execução. Já vimos muitos programadores tentar garantir que não haverá essa distinção usando a função UPPER, como em:

... WHERE UPPER(column1) = ‘SMITH’

Isso pode ser um erro quando se lida com seqüências que contêm letras que não sejam estritamente latinas. Com alguns SGBDs, quando você traduz determinadas

seqüências francesas ou alemãs em letras maiúsculas, as informações são perdidas.

Por exemplo, a função:

... UPPER(‘rÈsumÈ’)

retorna RESUME, ou seja, as marcas de acento são perdidas, alterando o significado

da palavra de “curriculum vitae” para “reiniciar”. Como as informações não se perdem

se for feito de outra maneira, é melhor usar a função LOWER, como aqui:

... WHERE LOWER(column1) = ‘rÈsumÈ’

Uma maneira ainda melhor é eliminar totalmente a função de resumo se for

possível. Tanto o manual da Microsoft como o da Oracle dizem: “Evite funções

em colunas”. Estamos certos de que eles querem dizer “evite funções em colunas

quando houver outra maneira de obter o resultado necessário”. Por exemplo, para

garantir que não haverá distinção entre maiúsculas e minúsculas, o melhor método

é usar uma collation case-insensitive em vez de uma função de resumo.

Uma pesquisa um pouco mais rápida pressupõe que os dados estejam limpos e

solicita somente combinações previsíveis, como esta:

... WHERE column1 = ‘SMITH’

OR column1 = ‘Smith’

GANHO: 8/8

que ainda é lenta. Nossa dica aqui é: tire proveito da eliminação de código

morto para que a pesquisa ‘Smith’ ocorra somente quando o SGBD fizer distinção entre maiúsculas e minúsculas. Veja como:

... WHERE column1 = ‘SMITH’

OR (‘SMITH’ <> ‘Smith’ AND column1 =

‘Smith’)

GANHO: 3/8

SARGabilidade (Sargability)

A condição de pesquisa SQL ideal possui a forma geral1:

No início, os pesquisadores da IBM chamavam esses tipos de condições de pesquisa de “sargable predicates” (“predicados com argumentos pesquisáveis”), pois SARG é uma contração de Search ARGument (argumento de pesquisa). Depois, a Microsoft e a Sybase redefiniram “sargable” como “que pode ser pesquisado pelo índice”. Bom, quando uma mesma palavra possui dois significados diferentes, ela não

deve mais ser usada como palavra! Por isso intitulamos a seção de “Sargability” só de

brincadeira. Mas, apesar de a palavra estar morta, a idéia vive nesta regra: o lado esquerdo de uma condição de pesquisa deve ser um nome de coluna simples; o lado direito deve ser um valor fácil de pesquisar. Para reforçar essa regras, todos os SGBDs

irão transpor a expressão:

5 = column1

em:

column1 = 5

Quando houver aritmética envolvida, somente alguns SGBDs farão a transposição. Por exemplo, testamos a seguinte transformação:

... WHERE column1 - 3 = -column2

é transformado em:

... WHERE column1 = -column2 + 3

GANHO: 4/8

O ganho mostra que o fato de nós mesmos termos feito a transformação foi de

considerável ajuda. Em um computador de 32 bits, a aritmética é mais rápida se

todos os operandos são INTEGERs (pois INTEGERs são números de 32 bits) e não

SMALLINTs, DECIMALs ou FLOATs. Assim, esta condição:

... WHERE decimal_column * float_column

é mais lenta que:

... WHERE integer_column * integer_column

GANHO: 5/8

Resumindo os ajustes gerais

• O lado esquerdo de uma condição de pesquisa deve ser um nome de coluna simples;

o lado direito deve ser um valor fácil de pesquisar. Cada componente de uma

condição de pesquisa possui uma contagem de pontos. Quanto mais altos os

pontos, mais rápido o componente. A condição que leva menos tempo obtém

mais pontos.

• Coloque as várias expressões na ordem correta.

• Use a Lei da Transitividade e o conceito de propagação constante para substituir um

literal de um nome de coluna ou expressão de coluna toda vez que puder fazer isso sem alterar o significado de uma expressão.

• Alguns SGBDs não resumirão a maioria das expressões aparentemente óbvias. De

qualquer forma, não use esse princípio como o motivo para sempre transformar

expressões como essa quando encontrá-las em código antigo. Normalmente, elas estão lá por razões históricas. Lembre-se sempre de entender o código antes de alterá-lo.

• Evite funções em colunas. Se não puder evitar funções, não use UPPER para garantir

que não haja distinção entre maiúsculas e minúsculas. Em vez disso, use LOWER.

Ajustes específicos

Até aqui, falamos sobre o ajuste geral das condições de pesquisa. Agora vamos analisar como aprimorar o seu código usando operadores SQL específicos.

AND

Quando tudo o mais for igual, os SGBDs avaliarão uma série de expressões com

AND da esquerda para a direita (exceto o Oracle, que avalia da direita para a esquerda

quando o otimizador cost-based está em operação). Você pode tirar proveito

desse comportamento colocando a expressão menos provável primeiro ou – se

ambas forem igualmente prováveis – colocando a expressão menos complexa primeiro. Depois, se a primeira expressão for falsa, o SGBD não perderá tempo avaliando a segunda. Por exemplo (a menos que esteja usando o Oracle), você

deve transformar:

... WHERE column1 = ‘A’ AND column2 =

‘B’

em:

... WHERE column2 = ‘B’ AND column1 =

‘A’

GANHO: 6/7 assumindo a ocorrência de

column2 = ‘B’ menos provável

Nota: O Oracle com o otimizador rule-based também obtém ganho, mas não faça isso para o Oracle que está executando o otimizador

cost-based.

T3

Linha                          column1                     column2

   1                             3                             A

   2                             2                             B

   3                             1                             C

Tabela para uma consulta AND mais OR.

OR

Ao escrever expressões com OR, coloque a expressão mais provável à esquerda. Essa é uma recomendação oposta à fornecida para AND porque OR gera testes adicionais se a primeira expressão for falsa enquanto AND só gera testes adicionais se a primeira expressão for true. Portanto, transforme Expressão #1 em

Expressão #2:

Expressão #1:

... WHERE column2 = ‘B’ OR column1 = ‘A’

Expressão #2:

... WHERE column1 = ‘A’ OR column2 = ‘B’

GANHO: 4/7 assumindo que a ocorrência de

column1 = ‘A’ é mais provável

Essa é uma transformação recomendada especialmente para sistemas Microsoft. Mais uma vez, os usuários Oracle devem ignorar essa recomendação porque os sistemas Oracle calculam da direita para a esquerda ao fazerem a operação com o

otimizador cost-based.

Os operadores OR também funcionam mais rapidamente se todas as colunas forem idênticas porque isso reduz o número de colunas e índices que o SGBD precisa ler. Por isso, em uma série longa com operadores

OR, as expressões para uma mesma coluna devem estar juntas. Por exemplo, você deve transformar Expressão #1 em Expressão #2:

Expressão #1:

... WHERE column1 = 1

OR column2 = 3

OR column1 = 2

Expressão #2:

... WHERE column1 = 1

OR column1 = 2

OR column2 = 3

GANHO: 1/8

AND mais OR

A Lei Distributiva diz o seguinte:

A AND (B OR C)

É a mesma coisa que

(A AND B) OR (A AND C)

Suponha que você tenha uma tabela idêntica à Tabela 3, em que seja necessário executar uma consulta onde os operadores

AND vêm primeiro:

SELECT * FROM Table1

WHERE (column1 = 1 AND column2 = ‘A’)

OR (column1 = 1 AND column2 = ‘B’)

Quando o SGBD efetua pesquisas em índices na ordem da consulta, é possível que ele siga estas etapas:

• Pesquisa de índice: column1=1. Result set = {row 3}

• Pesquisa de índice: column2=’A’. Result set = {row 1}

• AND para mesclar os conjuntos de resultados. Result set = {}

• Pesquisa de índice: column1=1. Result set = {row 3}

• Pesquisa de índice: column2=’A’. Result set = {row 1}

• AND para mesclar os conjuntos de resultados. Result set = {}

• OR para mesclar os conjuntos de resultados. Result set = {}

Agora, vamos trocar a consulta usando a Lei Distributiva:

SELECT * FROM Table1 WHERE column1 = 1

AND (column2 = ‘A’ OR column2 = ‘B’)

GANHO: 2/8

Fazendo as pesquisas na nova ordem, o SGBD seguiria estas etapas:

• Pesquisa de índice: column2=’A’. Result set = {row 1}

• Pesquisa de índice: column2=’B’. Result set = {row 2}

• OR para mesclar os conjuntos de resultados. Result set = {row 1, 2}

• Pesquisa de índice: column1=1. Result set = {row 3}

• AND para mesclar os conjuntos de resultados. Result set = {}

Esse teste gerou um ganho para apenas dois dos oito SGBDs testados. Os outros SGBDs tendem a aplicar eles mesmos a Lei Distributiva de modo que estarão sempre trabalhando com a mesma consulta canônica. No entanto, a evidência mostra que, para condições simples de pesquisa, a construção

A AND (B OR C)

é melhor que

(A AND B) OR (A AND C)

NOT

Evite a utilização da expressão NOT transformando-a em uma condição mais

simples. Para isto, inverta a operação de comparação. Por exemplo,

... WHERE NOT (column1 > 5)

transforma-se em

... WHERE column1 <= 5

Uma condição mais complexa requer mais cuidado. Nestes casos, pode-se aplicar o Teorema DeMorgan, que diz o seguinte:

NOT (A AND B) = (NOT A) OR (NOT B)

e

NOT (A OR B) = (NOT A) AND (NOT B)

Dessa forma, por exemplo, a condição de pesquisa

... WHERE NOT (column1 > 5 OR column2 = 7)

transforma-se em

... WHERE column1 <= 5 AND column2 <> 7

Se após a transformação você possuir um operador de desigualdade, o resultado será

mais lento. Em qualquer conjunto de valores distribuídos de modo uniforme, quando

existem mais de duas linhas, os valores diferentes sempre excedem os valores iguais.

Por isso, alguns SGBDs não usarão um índice para comparações de desigualdade, mas

usarão para comparações de maior que ou menor que. Assim, você pode transformar

o tipo de condição

... WHERE NOT (bloodtype = ‘O’)

em

... WHERE bloodtype < ‘O’

OR bloodtype > ‘O’

GANHO: 3/8

O ganho desse exemplo é 3/8 se praticamente todo mundo tiver o tipo sangüíneo O. Mas acontece justamente o contrário se a maioria das pessoas tiver um tipo sangüíneo diferente. Por isso, essa transformação deve ser feita apenas se você souber como os valores estão distribuídos e se a possibilidade de haver mudança na distribuição for mínima. Se o SGBD mantiver estatísticas, ele terá essas informações e procederá da forma mais apropriada.

IN

Muitas pessoas acham que não existe muita diferença entre as duas condições, OR e IN, porque ambas retornam o mesmo conjunto de resultados:

Condição #1:

... WHERE column1 = 5

OR column1 = 6

Condição #2:

... WHERE column1 IN (5, 6)

GANHO: 2/8

Essas pessoas estão ligeiramente enganadas. O IN (2/8) é mais rápido que OR.

Então, quando possível, transforme OR em IN. Entretanto, vale uma ressalva aqui: quando um operador IN tem uma série formada apenas por número inteiros, é melhor não utilizá-lo. Assim, a condição

... WHERE column1 IN (1, 3, 4, 5)

deveria ser transformada em

... WHERE column1 BETWEEN 1 AND 5

AND column1 <> 2

GANHO: 7/8

Ganhos semelhantes podem ocorrer quando for possível representar uma série usando uma expressão aritmética.

LIKE

A maioria dos SGBDs usará um índice para um padrão LIKE que comece com

um caractere real, mas evitará um índice para um padrão LIKE que comece com

um caractere curinga (seja ele % ou _). Por exemplo, se a condição de pesquisa for

... WHERE column1 LIKE ‘C_F%’

os SGBDs irão resolvê-la localizando todas as chaves de índice que comecem com C e filtrando apenas as que contiverem F na terceira posição. Em outras palavras,

não é necessário transformar a condição de pesquisa

... WHERE column1 LIKE ‘C_F%’

em

... WHERE column1 >= ‘C’

AND column1 < ‘D’

AND column1 LIKE ‘C_F%’

GANHO: -5/8

Nota: De fato, com IBM, Informix, Microsoft,

Oracle e Sybase, a expressão transformada será

mais lenta

!

Se quiser acelerar o operador LIKE com um parâmetro (LIKE ?) e souber que o padrão começa com um caracter, faça você mesmo a transformação. Veja como:

... WHERE column1 LIKE ?

transforma-se em

... WHERE column1 > SUBSTRING(? FROM 1

FOR 1)

AND column1 LIKE ?

GANHO: 4/8

Outra transformação interessante de LIKE com um parâmetro é usar o operador

de igualdade, em vez do operador LIKE, se o parâmetro não contiver um caractere curinga. Por incrível que pareça, esse procedimento pode realmente ajudar.

Por exemplo, é possível transformar a condição

... WHERE column1 LIKE ‘ABC’

Em

... WHERE column1 = ‘ABC’

GANHO: 5/8

A armadilha aqui é que LIKE ‘A’ e = ‘A’ não são exatamente as mesmas condições. Em uma instrução SQL padrão, a comparação LIKE considera os espaços à direita. Por outro lado, a comparação de igualdade os ignora. Além disso, os operadores LIKE e de igualdade, por padrão, não usam necessariamente

os mesmos collates. Por isso, não faça a transformação em colunas VARCHAR e

certifique-se de forçar o mesmo collate, se necessário.

Se uma coluna contiver apenas dois ou três caracteres, provavelmente você

tenderia a usar SUBSTRING em vez de LIKE, mas, como não é bom usar funções

em colunas, o operador LIKE sempre produzirá melhores resultados que vários

operadores SUBSTRING. Isso quer dizer que você deve transformar Expressão #1

em Expressão #2:

Expressão #1:

... WHERE SUBSTRING(column1 FROM 1 FOR

1) = ‘F’

OR SUBSTRING(column1 FROM 2 FOR 1) =

‘F’

OR SUBSTRING(column1 FROM 3 FOR 1) =

‘F’

Expressão #2:

...WHERE column1 LIKE ‘%F%’

GANHO: 5/6

Nota: Ingres e InterBase não suportam SUBSTRING;

o ganho é para apenas seis SGBDs.

SIMILAR

Se duas expressões que estiverem sendo unidas com OR estiverem em colunas definidas como CHAR ou VARCHAR, o novo

operador SIMILAR, do SQL-99, pode ser mais rápido que o operador OR (ver SQL Magazine 4 para maiores informações). Esta é a sintaxe básica de SIMILAR:

... SIMILAR TO <’pattern’>

Para o operador SIMILAR, string é o

nome de uma coluna ou uma expressão de coluna. Os caracteres curinga a seguir

podem ser incluídos no pattern (padrão):

• % ou _ significa o mesmo que os caracteres curinga usados com LIKE.

• * ou + significa "o anterior se repete indefinidamente": zero para infinitas vezes

no primeiro caso e um para o infinito no segundo.

•[A-F] significa qualquer caractere entre A e F.

• [AEK] significa A, E ou K.

• [^AEK] significa qualquer coisa diferente de A, E ou K.

• [:ALPHA:] significa qualquer coisa que seja uma letra latina. Outras opções para esta enumeração incluem [:UPPER:] (somente para letras maiúsculas), [ :

LOWER:] (somente para letras minúsculas), [:DIGIT:] (para qualquer dígito entre 0 e 9) e [:ALNUM:] (para qualquer dígito ou letra latina).

• | e || significam, respectivamente, o operador lógico OR e concatenação.

Dessa forma, por exemplo, a condição de pesquisa

... WHERE column1 SIMILAR TO ‘[AF] [AEK]_’

será verdadeira para as seqüências

DEN

FAB

e será falsa para as seqüências

GIB

AKRON

Como o operador SIMILAR aceita o operador lógico OR no pattern, às vezes

OR não será necessário. Por exemplo, você poderia transformar Expressão #1 em Expressão #2:

Expressão #1:

... WHERE column1 = ‘A’

OR column1 = ‘B’

OR column1 = ‘K’

Expressão #2:

... WHERE column1 SIMILAR TO ‘[ABK]’

GANHO: 1/1

Nota: Apenas o Informix suporta o comando

SIMILAR como descrito no SQL-99.

UNION

Em SQL, uma união de duas tabelas é o conjunto de todos os valores de dados

existentes em cada uma das duas tabelas eliminando as repetições, ou seja, UNION

retorna linhas não duplicadas de duas ou mais consultas. Essa pode ser uma ótima

maneira de mesclar dados. Mas será que é a melhor? Para testar isso, executamos duas instruções SELECT diferentes: Query #1 e

Query #2:

Query #1

SELECT * FROM Table1

WHERE column1 = 5

UNION

SELECT * FROM Table1

WHERE column2 = 5

Query #2

SELECT DISTINCT * FROM Table1

WHERE column1 = 5

OR column2 = 5

GANHO: 7/7

Nota: O MySQL não suporta UNION. O

ganho é para apenas 7 SGBDs.

Nos nossos testes, nem column1 nem column2 foram indexados. Observe que

Query #1 é maior, usa uma construção SQL relativamente rara e, é inválida como

parte de uma instrução CREATE VIEW. Se Query #2 sempre for executada mais rapidamente, como nesse exemplo, poderíamos recomendar que Query #1 sempre fosse transformada em Query #2. No entanto, em alguns SGBDs, isso poderia resultar

em execuções mais lentas. Para saber o motivo, precisamos considerar duas falhas

do otimizador.

A primeira delas é que muitos otimizadores

funcionam apenas em uma cláusula WHERE em uma instrução SELECT simples.

Por isso, acabam sendo executadas duas operações SELECT em Query #1.

Primeiro, o otimizador encontra todas as linhas em que a condição column1 = 5 é

verdadeira. Depois, encontra todas as linhas em que column2 = 5 em uma passagem

separada, ou seja, ele faz um table scan duas vezes! Então, se column1 não estiver indexado, Query #1 deverá levar exatamente

o dobro de tempo para ser executada que

Query #2.

Se column1 estiver indexada, a pesquisa dupla continuará a ocorrer, mas uma falha pouco comum no otimizador, observada em alguns SGBDs, compensa isso. Quando esses otimizadores percebem que uma condição de pesquisa contém OR, eles se recusam a usar índices. Por isso, nesse caso, e apenas nesse caso, UNION produz um resultado melhor que OR. Como esse é um conjunto de circunstâncias bastante limitado, recomendamos que OR seja utilizado em vez de UNION quando as colunas em questão não estiverem indexadas.

EXCEPT

Qualquer expressão A AND NOT B pode ser transformada com EXCEPT. Veja um exemplo a seguir onde Query #1 é transformada

em Query #2:

Query #1:

SELECT * FROM Table1

WHERE column1 = 7 AND

NOT column2 = 8

Query #2:

SELECT * FROM Table1

WHERE column1 = 7

EXCEPT

SELECT * FROM Table1

WHERE column2 = 8

GANHO: -2/3

Nota: Informix, Ingres, InterBase, Microsoft,

e MySQL não suportam EXCEPT.

O ganho negativo mostra que essa transformação não é uma boa idéia! Somado ao fato de que o suporte a EXCEPT é raro, recomendamos o seguinte: use AND NOT; evite EXCEPT.

INTERSECT

Embora existam muitas maneiras de transformar expressões formadas por AND usando INTERSECT, observamos que nenhuma delas produz ganhos. Como muitos SGBDs não aceitam o operador INTERSECT em hipótese alguma, não entraremos em detalhes sobre o assunto.

CASE

Suponha que uma condição de pesquisa tenha mais de uma referência a uma rotina lenta:

... WHERE slow_function(column1) = 3

OR slow_function(column1) = 5

Para evitar que slow_function seja executada

duas vezes, transforme a condição

utilizando CASE:

... WHERE 1 =

CASE slow_function(column1)

WHEN 3 THEN 1

WHEN 5 THEN 1

END

GANHO: 4/7

Nota: InterBase não suporta Case

.

Resumindo os ajustes específicos

• Quando tudo o mais for igual, os SGBDs avaliarão uma série de expressões

com AND da esquerda para a direita (exceto o Oracle, que avalia da direita

para a esquerda). Você pode tirar proveito desse comportamento colocando a expressão menos provável primeiro ou, se ambas forem igualmente prováveis, colocando a expressão menos complexa primeiro.

• Em uma série de expressões com OR, coloque primeiro a expressão mais provável,

a menos que você esteja usando um sistema Oracle.

• Em uma série de expressões com OR,

coloque as colunas idênticas juntas.

• Aplique a Lei Distributiva para escrever

condições de pesquisa simples com o formato

A AND (B OR C) em vez de (A AND B)

OR (A AND C).

• Transforme a expressão NOT em algo mais legível. Em uma condição simples,

inverta o operador de comparação; em uma condição mais complexa, aplique o

Teorema DeMorgan.

• Quando estiver familiarizado com a distribuição de um conjunto de valores, você

poderá acelerar o processo transformando pesquisas de desigualdade em pesquisas de

maior que ou de menor que.

• Transforme uma série de expressões com OR na mesma coluna em IN.

• A maioria dos SGBDs usará um índice para um padrão LIKE que comece com um

caractere real, mas evitará um índice para um padrão que comece com um caractere

curinga. Não transforme condições LIKE em comparações com >=, < e assim por

diante a menos que o padrão LIKE seja um parâmetro (por exemplo LIKE ?).

• Nos casos em que o parâmetro não tiver um caractere curinga, acelere o processamento de LIKE ? substituindo o operador de igualdade de LIKE, desde que os espaços à direita e agrupamentos diferentes não sejam um fator relevante.

• LIKE sempre será melhor que vários operadores SUBSTRING; portanto, evite a transformação.

• Transforme UNION em OR.

• Coloque uma condição de pesquisa em uma expressão CASE se o resultado for uma

redução no número de referências.

Notas de estilo

Ao executar várias instruções SQL seqüencialmente, é importante usar um

estilo consistente. Veja como executar as instruções SQL na Listagem 1

L1

SELECT column1*4 FROM Table1 WHERE COLUMN1

= COLUMN2 + 7

select Column1 * 4 FROM Table1 WHERE

column1=(column2 + 7)

execute estas duas:

SELECT column1 * 4 FROM Table1 WHERE

column1 = column2 + 7

SELECT column1 * 4 FROM Table1 WHERE

column1 = column2 + 7

GANHO: 2/8

Você deve estar pensando: “Mas elas são idênticas!”. Bem, semanticamente, todas as quatro expressões SELECT são idênticas. O truque é que alguns SGBDs armazenam os resultados analisados de consultas anteriores e os reutilizam se as consultas forem exatamente as mesmas, inclusive os espaços e a disposição de letras maiúsculas e minúsculas. Por isso, um estilo firme e consistente criará instruções que sejam fáceis de serem lidas, mas também que sejam mais rapidamente executadas!

Não forneceremos um guia de estilo aqui porque não é essa a finalidade deste artigo. No entanto, mostraremos que a transformação no exemplo usou algumas regras comuns e fáceis de serem lembradas:

• Coloque as palavras-chave em letras maiúsculas e os nomes de colunas em letras

minúsculas.

• Use os nomes de tabelas com a inicial maiúscula.

• Use espaços simples ao redor de cada palavra e de cada operador aritmético.

Considerações finais

Suponha que você tenha uma coluna de preços, definidos como DECIMAL(7,2), e precise responder à pergunta: Quais preços são dólares redondos?

Para iludir você, aqui estão duas citações presentes na documentação on-line do Microsoft SQL Server 2000:

• Citação no1: “Avoid data conversion functions” (Evite funções de conversão de dados).

• Citação no2: “If the same column is on both sides of the comparison operator, the

expression is not sargable” (Se a mesma coluna estiver nos dois lados do operador de

comparação, a expressão não é SARGable).

Agora, aqui estão as três condições de pesquisa que respondem à pergunta:

Search condition #1

... WHERE MOD(decimal_column, 1) = 0

Search condition #2

... WHERE CAST(decimal_column AS

CHAR(7)) LIKE ‘%.00%’

Search condition #3

... WHERE decimal_column = CAST(decimal_

column AS INTEGER)

Qual delas é a melhor? Se você tiver lido este artigo até aqui, existe uma resposta.

• A condição de pesquisa no 1 é a pior. Embora não exista um CAST, ela depende

da conversão de dados de números decimais para números inteiros porque as operações em módulos trabalham com números inteiros.

Existe ainda uma operação implícita de divisão.

• A condição de pesquisa no 2 é a intermediária. Alguns SGBDs armazenam valores

decimais como seqüências de caracteres. Por isso, a conversão de dados não é difícil.

Entretanto, LIKE é mais lento quando o padrão começa com um caractere curinga.

• A condição de pesquisa no 3 é a melhor. Ela quebra as regras definidas na citação no

1 e na citação no 2, mas dissemos que essas citações eram ilusórias (a Microsoft adota

uma acepção incomum para “sargable”). Se você comparar todas as três condições

usando as contagens de pontos mostradas nas tabelas 1 e 2 no início deste artigo, perceberá que a condição no 3 é a que alcança a maior pontuação porque usa o operador de igualdade e tem um número menor de expressões. Além disso, é mais próximo do ideal de “sargable” porque começa com

Conclusão

Neste artigo vimos um conjunto de dicas sobre otimização. Façam bom proveito

delas. Mas não se esqueça que isto é apenas parte do problema da otimização de

consultas SQL.

Comente essa matéria em:

www.sqlmagazine.com.br/sql11

Peter Gulutzan e Trudy Pelzer são autores

do livro SQL Performance Tuning