Construindo códigos T-SQL eficientes (sargable)

Já te falaram que cláusula WHERE com vários conectores OR torna a consulta lenta? Ou ainda que com o operador LIKE também?

Até onde tais afirmações possuem base técnica ou são somente achismo?

Se ainda não ouviu falar de predicado sargable, é hora de conhecer o significado deste adjetivo. E, mesmo que conheça, prepare-se para algumas surpresas neste artigo sobre como otimizar seu código T-SQL.

1. Introdução

Sargable é resultado da contração de Search e argument acrescido do sufixo able. Significa que as construções existentes possibilitam a utilização de operações seek em índices (caso existam) para acelerar as buscas na tabela.

Supondo a existência de uma tabela de promissórias e que se queira obter relação de promissórias que tenham sido pagas nos últimos 10 dias. Considerando-se os seguintes códigos:

-- código #1.1
declare @Hoje date;
set @Hoje= cast (current_timestamp as date);

SELECT Nome, DataPag, Valor
  from dbo.tbPromissoria
  where datediff (day, DataPag, @Hoje) < 10;

e

-- código #1.2
declare @Hoje date;
set @Hoje= cast (current_timestamp as date);

SELECT Nome, DataPag, Valor
  from dbo.tbPromissoria
  where DataPag > dateadd (day, -10, @Hoje);

o código #1.1 possui predicado que não é sargable

datediff (day, DataPag, @Hoje) < 10

mas o predicado do código #1.2 é sargable:

DataPag > dateadd (day, -10, @Hoje)

Mas como identificar se um predicado é (ou não) sargable? Há algumas situações que geralmente tornam o predicado non sargable:

  • coluna da tabela utilizada dentro de uma função;
  • coluna da tabela faz parte de uma expressão;
  • presença do conector OR para indicar valores diversos de uma mesma coluna

mas elas não podem ser seguidas com rigor, conforme veremos ao longo deste artigo.

Recentemente li artigo que empregava a seguinte frase para auxiliar na identificação: se o predicado não pode ser indexado, então provavelmente ele não é sargable. Sendo assim, no SQL Server é possível criar o índice abaixo?

CREATE INDEX I2_tbPro on dbo.tbPromissoria ( datediff (day, DataPag, @Hoje) );

Há no mínimo dois motivos que impedem: o primeiro é que não é possível criar índices utilizando funções. O segundo é que há uma variável cujo conteúdo possui valor diferente a cada dia, o que significaria regerar o índice a cada novo dia! Então, o predicado do código #1.1 não é sargable.

Já para o código #1.2 é possível criar o índice

CREATE INDEX I3_tbPro on dbo.tbPromissoria (DataPag);

Esta é uma forma simples de avaliar se um predicado é ou não sargable, mas não confie cegamente nela.

2. Predicado

No capítulo anterior foi citado o termo “predicado” várias vezes, mas sem qualquer explicação a respeito. Na documentação da cláusula WHERE temos a seguinte sintaxe

[ WHERE <search_condition> ]

onde

<search_condition> ::= 
{ [ NOT ]  <predicate> | ( <search_condition> ) } 
[ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ]

<search_condition> define a condição a ser atendida para que as linhas sejam retornadas. Ou seja, é o filtro aplicado no conjunto de linhas. Consultando a documentação sobre <search_condition>, consta que “É uma combinação de um ou mais predicados que usam os operadores lógicos AND, OR e NOT”.

Mas afinal, o que é predicado?

<predicate> ::=
{ expression { = |  | ! = | > | > = | ! > | < | < = | ! < } expression
| string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ]
| expression [ NOT ] BETWEEN expression AND expression
| expression IS [ NOT ] NULL
| CONTAINS ( { column | * } , '<contains_search_condition>' )
| FREETEXT ( { column | * } , 'freetext_string' )
| expression [ NOT ] IN ( subquery | expression [ ,...n ] )
| expression { = |  | ! = | > | > = | ! > | < | < = | ! < }
{ ALL | SOME | ANY} ( subquery )
| EXISTS ( subquery ) }

Ih! Mais confundiu do que explicou, ?

Pois é, um predicado é um comando ou expressão cujo resultado “ou é ou não é”. Um exemplo de predicado é Estado = ‘MG’. Ao avaliar um predicado para uma instância de entidade específica (cliente, por exemplo), a expressão representa uma proposição. Suponha que o cliente 43 seja de Aiuruoca (Minas Gerais, MG) e o cliente 42 seja de Janiru (São Paulo, SP). A proposição de que o estado do cliente 42 é igual a Minas Gerais (‘SP’ = ‘MG’) é falsa. Já a proposição de que o cliente 43 é igual a Minas Gerais (‘MG’ = ‘MG’) é verdadeira. Em outras palavras, você pode pensar em um predicado como uma forma geral da proposição mais específica, ou como uma proposição parametrizada. O predicado pode ser verdade para algumas proposições, mas falso para outras.

O modelo relacional baseia-se em predicados para várias finalidades: como uma estratégia de modelagem; para definir a integridade de dados; e para filtrar linhas em consultas. O uso de predicados como uma estratégia de modelagem envolve a listagem de exemplos para proposições que você precisa representar em seu banco de dados, retirando os dados e mantendo os cabeçalhos (predicados) e definindo as relações com base nos predicados. Um exemplo do uso de predicados para impor a integridade de dados ocorre nas restrições CHECK. Exemplos de uso de predicados para filtrar linhas em consultas incluem as cláusulas de consulta ON, WHERE e HAVING que o T-SQL suporta.

O que foi explicado resumidamente nos dois parágrafos anteriores sobre predicado e proposição foi baseado no artigo “Predicate-Based Query Filters” (vide capítulo Referências, ao final). O tema pode ser aprofundado na leitura de livros de lógica matemática ou ainda em verbetes da Wikipedia (neste caso de forma simplificada) tais como: lógica matemática, lógica proposicional, lógica de predicados, proposição, asserção etc. Vide capítulo Referências, ao final.

No caso da cláusula WHERE, quando o predicado é sargable o otimizador de consultas pode escolher índice que exista para realizar operações de busca rápida (seek). Caso contrário, pode ocorrer a leitura sequencial (scan) completa no índice (ou na tabela, se não houver índice). Deve-se ter em mente que mesmo que o predicado seja sargable e que exista índice que atenda à cláusula WHERE, ainda assim há outros fatores que o otimizador de consultas analisa para decidir se o índice será ou não utilizado para busca rápida (seek).

No SQL Server um predicado “é”, “não é” ou “não se sabe”. Ou seja, há um terceiro estado: “É uma expressão avaliada como TRUE, FALSE ou UNKNOWN. Os predicados são usados no critério de pesquisa das cláusulas WHERE e HAVING, nas condições de junção das cláusulas FROM e em outras construções em que um valor lógico é necessário”.

3. Predicados na cláusula WHERE

Para facilitar a compreensão, este artigo inicia pela análise de casos de predicados na cláusula WHERE.

3.1. Funções

3.1.1. inversão do predicado

No código #1.1 temos exemplo do impacto no uso de funções nos predicados, quando envolvendo coluna da tabela. O predicado torna-se não sargable. A forma de evitar isso é geralmente inverter o predicado, da forma que for possível.

-- código #3.1
CREATE TABLE tbFunc (
       Nome varchar(40) collate Latin1_General_CI_AI
                        not null primary key,
       DataNasc date not null,
       HoraNasc time(0) not null,
       DataAdmissão date not null,
       SitFunc char(1) not null
);

CREATE nonclustered INDEX I1_tbFunc 
                       on tbFunc (DataNasc) include (HoraNasc);
CREATE nonclustered INDEX I2_tbFunc on tbFunc (SitFunc);

set dateformat dmy;
INSERT tbFunc (Nome, DataNasc, HoraNasc, DataAdmissão, SitFunc)
 values
 ('Maria das Dores', '14/12/1980', '8:00', '1/1/2018', 'A'),
 ('João Maria da Silva', '29/2/1984', '3:00', '1/6/2017', 'B'),
 ('Maria Angelina Silva', '12/4/1982', '23:15', '12/10/2015', 'D'),
 ('Ernesto Varela', '30/1/1980', '19:12', '12/1/2018', 'C'),
 ('Mariangela Nascimento', '1/10/1977', '0:55', '9/11/2015', 'A');

Algo muito comum quando se manipulam datas é utilizar, por exemplo, a função YEAR() para obter o ano de determinada data. Supondo a existência da tabela tbFunc onde estão cadastradas as informações de funcionários, inclusive a data de nascimento. Caso se queira saber os funcionários que nasceram no ano de 1980 o usual é construirmos algo como

-- código #3.2 
SELECT Nome, DataNasc
  from tbFunc 
  where year(DataNasc) = 1980;

Mas, pelo fato da coluna DataNasc ser utilizada dentro de função, o predicado deixa de ser sargable.

p008_codigo #3.2

Para tornar o código sargable temos que retirar a função e alterar o parâmetro de pesquisa; algo que seja o mesmo que 1980. Uma forma é

-- código #3.3
set dateformat dmy;
SELECT Nome, DataNasc
  from tbFunc
  where DataNasc >= cast('1/1/1980' as date)
        and DataNasc <= cast('31/12/1980' as date);

O plano de execução

p008_codigo #3.3

mostra agora que o acesso aos dados ocorre através da busca direta (seek). Pronto: transformamos um predicado non sargable em dois predicados sargable. Só que o código #3.2 é de compreensão e manutenção bem mais fácil do que o código #3.3.

Procurando as Marias. Outro exemplo é de consulta em que deseja conhecer todas as funcionárias cujo nome seja “Maria”.

-- código #3.4
SELECT Nome, DataNasc
  from tbFunc
  where left (NOME, 6) = 'Maria ';

O plano de execução do código anterior é

p008_codigo #3.4

Como a coluna NOME foi utilizada dentro de uma função, então o código torna-se non sargable. Mas há algumas formas de tornar esse código sargable, sendo que uma delas é

-- código #3.5
SELECT Nome, DataNasc
  from tbFunc
  where NOME >= 'Maria '
        and NOME < 'Maria!';

Não é lá algo usual de se ver mas funciona, pois ao analisar o plano de execução percebe-se que o código ficou sargable.

p008_codigo #3.5

A estratégia do código anterior foi utilizar pesquisa por segmento, usando ‘Maria ‘ como ponto de partida e indo até ‘Maria!’, que é o valor imediatamente seguinte a ‘Maria ‘ (pela ordenação ascii). Observe que essa estratégia é dependente do agrupamento da coluna (collate) e considera que a coluna é case insensitive.

Outra forma de tornar sargable a pesquisa por nomes é o uso de LIKE, caso que é tratado no item 3.2.

3.1.2. CAST (coluna as date)

Uma das poucas funções que pode ser utilizada sem que o código se torne não sargable é a função CAST, na forma

CAST (coluna as date)

Essa construção é útil quando é necessário truncar coluna que contenha data e hora, mantendo somente a data. Há alguns textos na web que explicam o motivo desse caso específico garantir que o predicado se mantenha sargable e também alertam para o comportamento interno específico.

Para fins de demonstração será utilizada tabela e dados seguintes:

-- código #3.6
CREATE TABLE T (
     DateTimeCol datetime2 primary key,
     Info char(8000) default 'X'
);
with 
Nums(Num) as (
SELECT number
  from master..spt_values
  where type = 'P'
        and number between 1 and 1440
),
Datas(Dia) as (
SELECT {d '2012-12-30'} union all
SELECT {d '2012-12-31'} union all
SELECT {d '2013-01-01'} union all
SELECT {d '2013-01-02'} union all
SELECT {d '2013-01-03'}
)
INSERT into T (DateTimeCol)
    SELECT distinct dateadd(minute, N.Num, D.Dia)
      from Nums as N
           cross join Datas as D;

O código acima gera 7.200 linhas, sendo 1.440 linhas por dia no período de 30/12/2012 a 3/1/2013.

A seguir serão obtidas as linhas cuja data seja 1/1/2013. Como a coluna DateTimeCol é do tipo datetime2, então é necessário desconsiderar o horário. Uma solução é:

-- código #3.7
declare @Dia datetime2, @DiaSeguinte datetime2;
set dateformat dmy;
set @Dia= cast('1/1/2013' as datetime2);
set @DiaSeguinte= cast('2/1/2013' as datetime2);

set statistics io on;
set statistics time on;

SELECT *
  from T
  where DateTimeCol >= @Dia
       and DateTimeCol < @DiaSeguinte;

set statistics io off;
set statistics time off;

O plano de execução gerado é o seguinte:

p008_codigo #3.7

A mesma consulta, mas utilizando a função CAST(), tem o seguinte código:

-- código #3.8
declare @Dia date;
set dateformat dmy;
set @Dia= cast('1/1/2013' as date);

set statistics io on;
set statistics time on;

SELECT *
  from T
  where cast (DateTimeCol as date) = @Dia;

set statistics io off;
set statistics time off;

cujo plano de execução gerado é o seguinte:

p008_codigo #3.8

Em ambos os planos de execução se observa que a leitura na tabela T começa por seek no índice. Ou seja, os dois códigos são sargable.

Entretanto, ao analisarmos as estatísticas percebemos uma ligeira diferença entre os dois códigos:

-- código #3.7
Tabela 'T'. Número de verificações 1, leituras lógicas 1448, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.
-- código #3.8
Tabela 'T'. Número de verificações 1, leituras lógicas 2892, leituras físicas 0, leituras read-ahead 0, leituras lógicas lob 0, leituras físicas lob 0, leituras read-ahead lob 0.

O número de leituras lógicas no código #3.8 é quase o dobro das leituras lógicas realizadas no código #3.7. Qual o motivo de que no segundo código tenha ocorrido mais leituras se ambos retornaram 1.440 linhas?

A resposta é simples: no código #3.8 internamente ao invés de ler

>= 1/1/2013 ... < 2/1/2013

o que ocorre é a leitura de

> 31/12/2012 ... < 2/1/2013'

e após são descartadas todas as linhas de 31/12/2012.

A explicação deste comportamento anormal está além do nível proposto para este artigo mas quem se interessar em aprofundar nas causas basta ler a resposta de Martin Smith no tópico “Cast to date is sargable but is it a good idea?” e o artigo “Dynamic Seeks and Hidden Implicit Conversions”, de Paul White. Ambos estão no capítulo Referências, ao final.

3.2. LIKE

Em vários artigos se encontra a afirmação de que o uso da cláusula LIKE deve ser evitado, pois torna a consulta lenta. Mas o uso dele é prático quando necessitamos de encontrar determinado texto no conteúdo de uma coluna do tipo string, através do uso do coringa (wildcard). Por exemplo, para pesquisar todos os funcionários que tenham o sobrenome “Varela” podemos utilizar algo semelhante a

-- código #3.9
SELECT Nome, DataNasc
  from tbFunc
  where Nome like '% Varela%';

Simples, não?
Mas há um problema: o código acima não é sargable:

p008_codigo #3.9

Desta forma, dependendo da quantidade de linhas da tabela tbFunc bem como da quantidade de usuários utilizando o mesmo tipo de consulta, ao mesmo tempo, o servidor de banco de dados pode ficar lento nas respostas.

Entretanto, há um caso em que o LIKE é eficiente: quando procuramos por algo que inicie por determinado texto. Por exemplo, para encontrar as funcionárias cujo nome inicie por “Maria”:

-- código #3.10
SELECT Nome, DataNasc
  from tbFunc
  where Nome like 'Maria %';

Ao analisar o seguinte plano de execução

p008_codigo #3.10

observamos que temos um código que é sargable! Isso mesmo. Se alguém te disser que não pode utilizar LIKE porque é lento, já sabe a resposta: depende!

COLLATE. Aproveito para mencionar método de otimizar predicados com
LIKE %texto%
que utiliza a alteração do agrupamento (COLLATE). Embora o método não torne o predicado sargable, ele pode reduzir consideravelmente o tempo de processamento; está na forma de vídeo realizado pelo Fabrício Lima, com o título “Melhorando a performance de uma consulta com like ‘%String%’”. Vide capítulo Referências, ao final.

3.3. Conversão implícita

Outra recomendação que se lê com certa frequência para quem programa em T-SQL é para evitar a conversão implícita. Ou seja, certifique-se de igualar os tipos de dados, principalmente nos predicados. Um exemplo das consequências de ignorar tal regra consta em post do Vinícius Fonseca, em seu blog.

O código em questão é o seguinte:

CREATE TABLE tab_prova (
     id int identity(1,1), 
     campochar varchar(10)
);

INSERT into tab_prova (campochar) values 
     ('123abc'), ('aeiou'), ('vinicius');
SELECT * from tab_prova where campochar = 100;

Ao executar o código acima a seguinte mensagem de erro é exibida:

p008_Msg 245

Ora, se a coluna campochar está declarada como varchar porque o literal 100 não foi convertido implicitamente para ‘100’, realizando então uma comparação de tipos de dados string? A resposta está na precedência de tipos de dados: “Quando um operador combinar duas expressões com tipos de dados diferentes, as regras de precedência do tipo de dados especificam que aquele com a precedência mais baixa será convertido para o tipo de dado de maior precedência”. Como o tipo de dados int possui precedência maior do que o tipo de dados varchar, o conteúdo da coluna campochar é convertido implicitamente para tipo de dados int. Mas como é impossível converter ‘123abc’ para o tipo de dados int é então emitida a mensagem de erro.

No exemplo acima, se na tabela tab_prova os valores fossem sempre numéricos não ocorreria o erro na conversão implícita. Entretanto, como a conversão implícita ocorreu na coluna da tabela, caímos no caso uso de funções, descrito no item 3.1:

SELECT * from tab_prova where cast (campochar as int) = 100;

Ou seja, a conversão implícita tornou o predicado não sargable. Por isso que é importante sempre garantir que as expressões de um predicado tenham o mesmo tipo de dados.

Se tiver interesse em aprofundar neste caso sugiro a leitura do artigo “Os perigos da conversão implícita”. Vide capítulo Referências, ao final.

3.4. Conector OR

3.4.1. (A = 1 OR A = 18 OR A = 22 … OR A = 2018)

E então, o que acontece quando no predicado temos uma série de condições unidas pelo conector OR? O código fica não sargable?

Na tabela tbFunc do código #3.1 temos uma coluna que identifica a situação do funcionário: SitFunc. Essa coluna é de preenchimento obrigatório e contém um caractere. Supondo que o usuário necessita saber quais os funcionários estão com situação funcional ‘B’ ou ‘C’, temos o seguinte código

-- código #3.11
SELECT Nome, SitFunc
  from tbFunc
  where SitFunc = 'B'
        or SitFunc = 'C';

O resultado esperado é que ocorra leitura sequencial (scan), por causa do conector OR. Ao executar o código acima, obtemos o seguinte plano de execução:

p008_codigo #3.11

Ora, se temos o conector OR na cláusula WHERE, o que tornaria o predicado não sargable, o que ocorreu que a pesquisa foi através de busca direta (seek)? A explicação para o que aconteceu é bem simples: o predicado de busca (seek predicate) é quebrado em n chaves de busca. No caso do código #3.11 seria algo semelhante (mas não igual) a

-- código #3.12
SELECT Nome, SitFunc
  from tbFunc
  where SitFunc = 'B'
union all
SELECT Nome, SitFunc
  from tbFunc
  where SitFunc = 'C';

É claro que há um limite para essa quebra do predicado de busca chaves de busca individuais, acima do qual teremos o otimizador de consultas opta pela leitura completa (scan). Mas mesmo nesses caso há como tornar o código sargable, assunto do próximo item.

Como percebemos, conector OR na cláusula WHERE nem sempre torna a consulta lenta.

3.4.2. Uso de tabela temporária

Supondo que no código #3.11 houvesse uma quantidade enorme de condições unidas pelo conector OR e que o otimizador de consultas tenha optado por não utilizar a quebra do predicado de busca mas sim em usar a leitura sequencial (scan). Uma solução de contorno quando se tem uma extensa lista de valores unidos pelo conector OR é colocar os valores a serem pesquisados em uma tabela temporária e utilizá-la na cláusula WHERE. Algo assim:

-- código #3.13
CREATE TABLE #Filtro_SitFunc (Valor char(1) primary key);
INSERT into #Filtro_SitFunc values ('B'), ('C');

SELECT Nome, SitFunc
  from tbFunc
  where SitFunc in (SELECT Valor from #Filtro_SitFunc);

que gera o seguinte plano de execução

p008_codigo #3.13

Ao analisar o plano de execução do código #3.13 o que se observa é que o otimizador de consultas (query optimizer) transformou o código em algo semelhante a

-- código #3.14
CREATE TABLE #Filtro_SitFunc (Valor char(1) primary key);
INSERT into #Filtro_SitFunc values ('B'), ('C');

SELECT T1.Nome, T1.SitFunc
  from tbFunc as T1
       inner join #Filtro_SitFunc as T2 on T1.SitFunc=T2.Valor;

Inclusive recomendo tal técnica em tópicos do fórum TechNet de SQL Server, com ganhos expressivos citados pelos autores dos tópicos. No tópico “Limitar Where” o autor do tópico cita que na cláusula WHERE de determinada consulta “tem pessoas colocando 4400 códigos de produtos no where de uma vez, fazendo nosso servidor virar uma carroça”. Foi sugerido então que armazenasse os códigos de produto a pesquisar em uma tabela temporária e os resultados positivos foram imediatos: nos testes que ele fez, o tempo reduziu de 1h30min (ou 90 minutos) para 10 minutos. Com uma simples alteração no código obteve redução de 89% no tempo de execução!

Outro caso semelhante ocorreu no tópico “Muita demora em Consulta em tabela de movimento grande”. Ao perceber o ganho que obteve ao utilizar a técnica acima, o autor do tópico pergunta: “Por que motivo fica mais rápido com a tabela temporária ao invés dos OR?”. A resposta você já sabe: sargability!

Caso tenha interesse em ler na íntegra os tópicos citados acima, eles estão registrados no capítulo Referências, ao final.

4. Forçando código non sargable em sargable

4.1. Predicado de busca

Nos itens anteriores deste capítulo foram explicadas algumas técnicas para tornar predicado non sargable em sargable. Entretanto, tais técnicas não são gerais havendo casos em que, aparentemente, não há como tornar o predicado sargable. É quando entra em ação o jeitinho.

No tópico “Between em 2 campos data e hora” há uma situação em que temos um predicado non sargable, pois o predicado utiliza duas colunas para compor uma expressão do tipo data&hora que então será comparada com um intervalo. Trazendo aquela situação para nossa tabela de funcionários, temos então duas colunas para registrar o momento de nascimento do funcionário: DataNasc, que contém a data de nascimento, e HoraNasc, com o horário de nascimento. Supondo que seja necessário conhecer os funcionários que nasceram de 30/1/1980 às 20h até 29/2/1984 às 2h, como você construiria código para obter o relatório?

Uma primeira opção é

-- código #4.1
declare @Data1 datetime, @Data2 datetime;
set dateformat dmy;
set @Data1= '30/1/1980 20:00';
set @Data2= '29/2/1984 2:00';

SELECT Nome, DataNasc, HoraNasc
  from tbFunc
  where cast(DataNasc as datetime) + cast(HoraNasc as datetime)
                    between @Data1 and @Data2;

cujo plano de execução é

p008_codigo #4.1

e com as seguintes propriedades para o operador Index Scan:

p008_codigo #4.1 prop1

É um predicado non sargable em que aparentemente não há como reescrevê-lo para torná-lo sargable. Então, como fazer com que a consulta realize busca rápida (seek) no lugar de leitura sequencial (scan)? O truque é acrescentar um predicado sargable para funcionar como predicado de busca, mantendo o predicado já existente para refinar a busca.

-- código #4.2
declare @Data1 datetime, @Data2 datetime;
set dateformat dmy;
set @Data1= '30/1/1980 20:00';
set @Data2= '29/2/1984 2:00';

SELECT Nome, DataNasc, HoraNasc
  from tbFunc
  where DataNasc between cast(@Data1 as date) 
                         and cast(@Data2 as date)
        and cast(DataNasc as datetime) + cast(HoraNasc as datetime)
                         between @Data1 and @Data2;

O primeiro predicado é sargable e o segundo não é; o otimizador de consultas utiliza então o primeiro predicado como predicado de busca. Eis o plano de execução para comprovar:

p008_codigo #4.2

Na ilustração a seguir vemos o filtro adicional acrescentado como predicado de busca:

p008_codigo #4.2 seek

Eu costumo utilizar a técnica de acrescentar um filtro para atuar como predicado de busca sempre que me deparo com predicados (geralmente complexos) que são todos non sargable. É óbvio que para montar este tipo de filtro é necessário conhecer os índices existentes para a tabela.

O código #4.2 não é a única forma de se obter o relatório de funcionários nascidos em determinado período; há várias outras. Por exemplo, caso optemos por separar as datas de limite em campos de data e de hora, podemos ter algo simples como:

-- código #4.3
declare @Data1 date, @Hora1 time(0), @Data2 date, @Hora2 time(0);
set dateformat dmy;
set @Data1= '30/1/1980';
set @Hora1= '20:00';
set @Data2= '29/2/1984';
set @Hora2= '2:00';

SELECT Nome, DataNasc, HoraNasc
  from tbFunc
  where DataNasc between @Data1 and @Data2
    and IIF(DataNasc = @Data1, IIF(HoraNasc >= @Hora1, 1, 0), 1) = 1
    and IIF(DataNasc = @Data2, IIF(HoraNasc <= Hora2, 1, 0), 1) = 1;

O código acima utilizará leitura sequencial (scan) ou busca rápida (seek)? Descubra você mesmo…

4.2. Coluna calculada indexada

Outra técnica para forçar código non sargable em sargable é criar uma coluna calculada que contenha a função presente no predicado e então criar índice por essa coluna calculada. Óbvio que o índice deve ser de cobertura, pois caso contrário talvez não venha a ser utilizado. Na primeira consulta deste artigo, no código #3.2, são listados os funcionários que nasceram no ano de 1980. Ao usar a função YEAR() na data de nascimento, o predicado tornou-se non sargable. Empregando então a solução proposta neste item, temos o seguinte código para criar a coluna calculada e o respectivo índice:

-- código #4.4
ALTER TABLE tbFunc
     ADD AnoNasc as year(DataNasc) persisted;

CREATE nonclustered INDEX I3_tbFunc 
                  on tbFunc (AnoNasc) include (DataNasc);

Observe que o índice I3_tbFunc é de cobertura, considerando-se obter as colunas Nome e DataNasc. A simples criação desse índice torna o código #3.2 sargable sem nem mesmo precisar modificar o predicado para utilizar a coluna calculada AnoNasc!

p008_codigo #3.2 sarg

No plano de execução anterior observa-se que o índice utilizado foi o I3_tbFunc, que é pela coluna calculada AnoNasc. Nenhuma modificação foi realizada no código #3.2!

Esta técnica é útil em casos em que não temos acesso ao código fonte da aplicação (software de terceiros, por exemplo). Inclusive é recomendada por Brent Ozar: “indexed (not just persisted) computed columns are an awesome trick to have in your performance tuning bag”.

É óbvio que há um custo adicional no uso desta técnica, pois envolve a criação de coluna calculada e de índice adicional, o que demanda mais espaço físico de armazenamento e principalmente a necessidade do mecanismo de banco de dados (database engine) mantê-lo atualizado. Felizmente a data de nascimento não é alterada…

5. Considerações finais

A tabela de dados utilizada como base para exemplos foi criada com poucas linhas somente para fins de testes. É óbvio que os planos de execução variam de acordo com o volume de dados, por causa da cardinalidade, seletividade e outros fatores. Mas para os objetivos deste artigo bastaram essas poucas linhas de dados.

Para reduzir o tamanho do artigo optou-se por uma lista ampla de referências, onde você pode aprofundar no assunto.

6. Referências


PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para acessar.

Construindo códigos T-SQL eficientes: Sargability

Uma consideração sobre “Construindo códigos T-SQL eficientes (sargable)”

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s