Vamos ver alguns exemplos utilizando querys por data na tabela Orders do banco de dados Northwind, dois dos exemplos usam uma variável como argumento de busca, o outro um valor literal.
Exemplos:
— Query 1: Retorna 5 linhas, usando uma variável no argumento de busca
DECLARE @odate AS DATETIME
SET @odate = ‘19980506’
SELECT * FROM Orders
WHERE OrderDate >= @odate
GO
— Query 2: Retorna todas as linhas, usando uma variável no argumento de busca
DECLARE @odate AS DATETIME
SET @odate = ‘19960101’
SELECT * FROM Orders
WHERE OrderDate >= @odate
GO
— Query 3: Retorna 5 linhas, usando um valor literal no argumento de busca
SELECT * FROM Orders
WHERE OrderDate >= ‘19980506’
GO
As querys 1 e 2 usam uma variável local no argumento de busca. Já a query 3 usa uma referência ao mesmo valor que as querys 1 e 2 porém na forma de Hard-Code.
Execute as 3 querys no banco northwind para ter certeza que você conhece o retorno das 3 querys e como cada uma delas é diferente. Podemos observer que as querys 1 e 3 retornam o mesmo resultado, no entanto, ao verificarmos o plano de execução, veremos que cada uma contém um plano de execução diferente.
Quantidade de IOs efetuado pelas querys:
Agora execute as querys novamente, mas adicione o Statistics IO no inicio do Batch :
SET STATISTICS IO ON
GO
Resultado de IOs:
Query 1: Table ‘Orders’. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0.
Query 2: Table ‘Orders’. Scan count 1, logical reads 40, physical reads 0, read-ahead reads 0.
Query 3: Table ‘Orders’. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0.
Apesar das querys 1 e 3 retornarem a mesma quantidade de registros (4 registros), a query 1, que usa uma variável local, requer 40 leituras lógicas, enquanto que a query 3, que usa o valor umavalor literal, requer apenas 10 leituras lógicas. É possível notar também que a query 1 usa o mesmo número de leituras que a query 2, embora a query 2 retorne muito mais registros (830 registros).
Analisando o plano de Execução:
Agora execute as querys novamente, mas adicione o SHOWPLAN inicio do Batch.
Você pode ver o plano de execução (como o SQL Server executa cada query) a partir do query analyzer usando “SET SHOWPLAN_TEXT ON” no inicio do batch e usando “SET SHOWPLAN_TEXT OFF” no final.
Resultado do ShowPlan:
Query 1:
StmtText
————————————————————————
|–Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Orders].[OrderDate]>=[@odate]))
Query 2:
StmtText
—————————————————————————-
|–Clustered Index Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]), WHERE:([Orders].[OrderDate]>=[@odate]))
Query 3:
StmtText
——————————————————————————-
|–Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Orders]))
|–Index Seek(OBJECT:([Northwind].[dbo].[Orders].[OrderDate]), SEEK:([Orders].[OrderDate] >= Convert([@1])) ORDERED FORWARD)
Podemos ver que as querys 1 e 2 possuem planos de execução identicos. Para executar essas querys, o SQL Server faz um scan na primary key (coluna orderID), que a clausula where não usa para nada. Já para executar a query 3 o SQL Server usa o índice não cluster OrderDate (definido na coluna orderdate) fazendo um index seek, isso explica a diferença no número de leituras lógicas entre as querys 1 e 3.
Mas afinal, por que o SQL server escolhe planos de execução tão diferentes para querys que parecem identicas ? O SQL Server não conhece o valor da variável local das querys 1 e 2 quando ele otimiza as querys, por isso ele tem que adivinhar o valor. Lembre-se, Indices nonclustered tipicamente não são úteis se temos a necessidade de retornar uma grande porcentagem de linhas de uma tabela, e apesar da query 1 retornar apenas 5 linhas, no momento de otimizar a query o SQL Server não sabe disso, então assume que a query irá retornar um terço da tabela, porque está sendo usando o operador >. Sendo assim, o índice nonclustered OrderDate não será eficiente pois a query returna muitas linhas, então o SQL Server não o utiliza.
Na query 3, o SQL Server conhece precisamente quantas linhas a query irá retornar porque o argumento de pesquisa (SARG) é literal. Sabendo que a query vai retornar apenas 5 linhas, o SQL server utiliza o índice nonClustered.
Uma fora de garantir que o SQL Server conheça o valor da variável em tempo de compilação e assim possa usar o índice correto, é encapsular a query em uma stored procedure. Você precisa utilizar a opção With Recompile para criar a stored procedure se o melhor query plan pode variar conforme os valores de entrada:
CREATE PROC DateRangeTest
@odate AS DATETIME
WITH RECOMPILE
AS
SELECT * FROM Orders
WHERE OrderDate >= @odate
GO
Agora rode as seguintes instruções e compare o plano de execução de cada uma :
SET SHOWPLAN_TEXT ON
GO
EXEC DateRangeTest ‘19980506’
— returns 5 rows
EXEC DateRangeTest ‘19960101’
— returns 830 rows
Podemos ver que a primeira chamada da procedure, que retorna apenas 5 linhas, consegue utilizar o índice nonclustered OrderDate (fazendo um Index Seek), enquanto que a segunda chamada continua a fazer scan no indice clustered (PK_Orders) porque retorna um volume de linhas muito grande.