Você sabe a diferença entre uma consulta Sargable e Non-Sargable?

Olá pessoal tudo certo?

No post de hoje vou compartilhar com vocês a respeito de um conceito muito comum no SQL Server, que é o conceito de SARG (“S“ search argument) e Non-SARGable cuja a tradução livre pode ser “Argumento de busca“.

O termo SARG nada mais é que a coluna que você está utilizando como “predicate” na cláusula WHERE se ela pode ser utilizada em uma operação de “Index Seek“. Já quando o nosso “predicate” NÃO permite a operação de “Index Seek”, podemos dizer que estamos utilizando um “predicate Non-Sargable” e consequentemente teremos um custo maior para execução da query.

Vamos observar na prática como isso funciona. Eu utilizei os scripts abaixo no banco AdventureWorks.

--Vamos retornar todos os funcionários com o nome "Paul"

SELECT B.FirstName,B.LastName,A.BirthDate
FROM HumanResources.Employee A
JOIN Person.Person B ON B.BusinessEntityID = A.BusinessEntityID
WHERE B.FirstName = 'Paul'

--Custo de execução
Table 'Employee'. Scan count 0, logical reads 18
Table 'Person'. Scan count 1, logical reads 117
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 36 ms.

Observando o plano de execução, podemos ver que o próprio SQL, nos sugere a criação de um índice na coluna FirstName.

Então vamos criar o índice para ver como que a consulta vai ficar.

CREATE INDEX IX_Person ON Person.Person
(FirstName)
WITH(FILLFACTOR=90)

--Custo de execução
Table 'Person'. Scan count 1, logical reads 11
Table 'Employee'. Scan count 1, logical reads 9

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 32 ms.

Como podemos ver o custo da execução reduziu para 32 milissegundos, enquanto o numero de leituras na tabela “Person.Person” reduziu de 117 para apenas 9 reads. Podemos observar também que no plano de execução o SQL deixou de fazer um “Index Scan” na tabela “Person.Person” para realizar um “Index Seek“. Com isso podemos dizer que a coluna “FirstName” para essa consulta é um predicado Sargable.

Mas atenção dependendo da consulta a coluna “FirstName” pode deixar de ser Sargable, principalmente quando utilizamos funções ou utilizamos alguns operadores. Vejamos a query abaixo.

SELECT B.FirstName,B.LastName,A.BirthDate
FROM HumanResources.Employee A
JOIN Person.Person B ON B.BusinessEntityID = A.BusinessEntityID
WHERE LEFT(B.FirstName,5) = 'Paul'

--Custo de execução
Table 'Employee'. Scan count 0, logical reads 18
Table 'Person'. Scan count 1, logical reads 100
 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 345 ms.

Neste primeiro exemplo “Non-Sargable“, estamos utilizando a função “LEFT“, com isso nós impossibilitamos o otimizador de consulta a utilizar o índice para fazer a operação de “Index Seek“.

Nessa segunda consulta vamos realizar um SELECT que vai nos retornar todos os funcionários nascidos em 1980, para isso vamos criar um índice na coluna “BirthDate” da tabela “HumanResources.Employee“.

CREATE INDEX IX1_Employee ON HumanResources.Employee
(BirthDate)
WITH(FILLFACTOR=90)

SELECT B.FirstName,B.LastName,A.BirthDate
FROM HumanResources.Employee A
JOIN Person.Person B ON B.BusinessEntityID = A.BusinessEntityID
WHERE A.BirthDate BETWEEN '1980-01-01' AND '1980-12-31'

--Custo da execução
Table 'Person'. Scan count 0, logical reads 21
Table 'Employee'. Scan count 1, logical reads 2

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 450 ms.

Como podemos observar, o SQL utilizou o índice para realizar uma operação de “Index Seek“, então o predicado “BirthDate” nesta consulta é Sargable.

Mas você pode estar pensando “Pô Tiago” da para deixar esse código melhor utilizando a função “Year“, Ok…, vamos ver o que acontece se utilizarmos a função Year.

SELECT B.FirstName,B.LastName,A.BirthDate
FROM HumanResources.Employee A
JOIN Person.Person B ON B.BusinessEntityID = A.BusinessEntityID
WHERE YEAR(A.BirthDate) = '1980'

--Custo da execução:
Table 'Person'. Scan count 0, logical reads 21
Table 'Employee'. Scan count 1, logical reads 88

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 554 ms.

Novamente com a utilização da função “Year” no predicado “BirthDate“, o SQL NÃO conseguiu realizar um Index Seek.

Mas uma coisa a se observar é que nem sempre é ruim você “pagar”, usar uma função vai ter um momento que a diferença no custo será tão irrelevante que não vai justificar o trabalho de modificar toda a query. Outro ponto de atenção com o predicado Sargable, está relacionado a conversão implícita. Quando o SQL faz uma conversão implícita o otimizador de consulta NÃO consegue realizar a operação de Index Seek, tornando ele um predicado non-sargable, como podemos observar na consulta a seguir.

CREATE INDEX IX1_Customer ON sales.Customer
(AccountNumber)
WITH (FILLFACTOR = 90)

SELECT b.FirstName,
       b.LastName,
       a.AccountNumber
FROM sales.Customer a
    JOIN Person.Person b
        ON b.BusinessEntityID = a.PersonID
WHERE a.AccountNumber = 'AW00029594';

--Custo execução:
Table 'Person'. Scan count 0, logical reads 3
Table 'Customer'. Scan count 1, logical reads 4

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 301 ms.

Na consulta acima criamos um índice na tabela “Sales.Customer” utilizando a coluna “AccountNumber” como chave, ao executar a consulta podemos observar que o SQL utilizou o índice para fazer uma operação de Index Seek.

Porém é normal quando o desenvolvedor utiliza algum framework até mesmo por costume colocar o parâmetro “N” no varchar, quando fazemos isso estamos informando ao SQL que vamos trabalhar com  o tipo de dados NVarchar, que neste caso vai gerar a conversão implícita, que vai fazer com que o SQL deixe de realizar o Index Seek no predicado e também consuma mais CPU para efetuar a consulta.

SELECT b.FirstName,
       b.LastName,
       a.AccountNumber
FROM sales.Customer a
    JOIN Person.Person b
        ON b.BusinessEntityID = a.PersonID
WHERE a.AccountNumber = N'AW00029594';

--Custo de execução:
Table 'Person'. Scan count 0, logical reads 3
Table 'Customer'. Scan count 1, logical reads 39

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 609 ms.

Como podemos observar o SQL gerou um “warning” informando que o otimizador de consulta teve que fazer uma conversão implícita, e por consequência ele nem utilizou o índice que criamos na coluna “AccountNumber“, realizou um Index Scan no índice “IX_Customer_TerritoryID”. Além disso o custo de execução na CPU aumentou praticamente 50%, então quando ocorre uma conversão implícita no predicado temos um predicado non-sargable.

Por isso que eu sempre recomendo a observar se no plano de execução não tem nenhum warning referente a conversão implícita.

Por fim… Existe um mito bastante comum que é falar que quando utilizamos a cláusula LIKE no predicado ele deixar de ser Sargable. Isso é verdade, porém somente se você utilizar o coringa “%” no começo e no fim da sua string de busca. Vejamos no exemplo a seguir.

A query abaixo é a mesma que utilizamos no primeiro exemplo, porém, desta vez queremos que o SQL nos retorne todos os clientes que tenha %Paul% no nome.

SELECT b.fullname,
       a.AccountNumber
FROM sales.Customer a
    JOIN Person.Person b
        ON b.BusinessEntityID = a.PersonID
WHERE b.FullName LIKE ('%Paul%');

--Custo:
Table 'Customer'. Scan count 1, logical reads 123
Table 'Person'. Scan count 1, logical reads 111

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 451 ms.

Como utilizamos o no predicado FullName “%Paul%“, o otimizador de consulta NÃO consegue efetuar um operação de Index Seek no índice “IX02_Person“. Então nesta consulta o predicado é Non-Sargable, e até ai o mito que a cláusula “LIKE” é Non-Sargable é verdade, mas……

Se você sabe quer somente o clientes que se chamam Paulo, Paul ou Paula, você concorda que podemos deixar essa consulta um pouco diferente, removendo o coringa “%” inicial, deixando apenas FullName LIKE “Paul%”. Vamos ver o que o otimizador de consulta vai fazer nessa query.

SELECT b.fullname,
       a.AccountNumber
FROM sales.Customer a
    JOIN Person.Person b
        ON b.BusinessEntityID = a.PersonID
WHERE b.FullName LIKE ('Paul%');

--Custo:
Table 'Customer'. Scan count 1, logical reads 123
Table 'Person'. Scan count 1, logical reads 2

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 225 ms.

Uauuuu agora o SQL conseguiu fazer uma operação de Index Seek utilizando o índice “IX02_Person“, derrubando o mito que o LIKE SEMPRE fará que o predicado seja Non-Sargable.

O Fabricio Lima tem um vídeo no que mostra dicas de como deixar o seu SELECT utilizando o LIKE mais performático.

Bom pessoal, por hoje é isso espero ter contribuído com algo novo para vocês.

Deixe um feedback e nos ajude a melhorar.

Abraços,

Tiago Neves