Por que Querys similares podem possuir planos de execução tão diferentes ?
Por estranho que pareça esta pergunta, isso realmente pode acontecer. Quando duas querys simulares ou identicas possuem performance muito deferente, precisamos comparar os planos de execução das querys para resolver esse mistério.
Vamos ver um exemplo utilizando querys por data na tabela Orders do banco northwind, ora usando uma variável como argumento de busca, ora um valor literal. Veja as querys :
-- Query 1: Retorna 5 linhas, usando uma variável no argumento de busca (SARG)
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 (SARG)
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 (SARG)
-- argument (SARG)
SELECT * FROM Orders
WHERE OrderDate >= '19980506'
GO
As querys 1 e 2 usam uma variável local no SARG (Search Argument). Já a query 3 usa uma referencia ao mesmo valor que as querys 1 e 2 porém na forma de Hard-Code. As querys 1 e 3 devolvem o mesmo resultado, mas como você vê, cada uma contém um plano de execução diferente.
Execute as 3 querys anteriores no banco northwind para ter certeza que você conhece o retorno das 3 querys e como cada uma delas é diferente. Agora execute as querys novamente, mas ative o Statistics IO no inicio do Batch :
SET STATISTICS IO ON
Apesar da query 1 e query 3 retornarem o mesmo resultado, query 1, que usa uma variável local, requer 21 leituras lógicas, enquanto que a query 3, que usa o valor em hard code, requer apenas 10 leituras lógicas. Query 1 usa o mesmo número de leituras que a query 2, independente do fato da query 2 retornar muito mais registros.
Execute as querys mais uma vez e olhe para o SHOWPLAN para ver como o SQL Server executa cada query. Você pode ver o plano de execução a partir do query analyzer usando "Set showplan_text ON" no inicio do batch e fazendo "set showplan_text off" no final.
Você 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, que é a coluna orderID, que a clausula where não usa para nada. Já para executar a query 3 o SQL Server usa o índice OrderDate (definido na coluna orderdate) fazendo um index seek, o que 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 da query 1 quando ele otimiza a query, por isso tem que adivinhar que valor deve ser. Indices nonclustered tipicamente não são úteis se temos a necessidade de retornar uma grande porcentagem de linhas da tabela. E, apesar da query 1 retornar apenas 5 linhas, o SQL Server não sabe disso, por isso assume que a query vá retornar um terço da tabela, porque está sendo usando o operador > . O indice OrderDate não será eficiente se a query returnar muitas linhas, então o SQL Server não o utiliza. Na query 3, porém, o SQL Server conhece precisamente quantas linhas a query irá retornar porque o SARG é literal. E 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 possa assim 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 número de leituras de cada uma :
EXEC DateRangeTest '19980506'
-- returns 5 rows
EXEC DateRangeTest '19960101'
-- returns 830 rows
Você verá que a primeira chamada da procedure, que retorna apenas 5 linhas, consegue utilizar o índice OrderDate, enquanto que a segunda chamada continua a utilizar o scan no indice clustered porque retorna um volume de linhas muito grande.
