Você deveria usar stored procedures?

Há algum tempo, por ingenuidade, alguns programadores assumem que o uso de stored procedures é sinônimo de problemas em desempenho e, em alguns casos, “vazamento” do domínio, o qual deveria estar no código da aplicação. Entretanto, quando analisamos cenários em que o uso de stored procedures é relacionada a gargalo no banco de dados ou vazamento de domínio, percebemos que o gatilho comum é referente ao abuso do recurso e não da tecnologia em si. Utilizar stored procedures para consultar o banco de dados pode trazer uma série de benefícios comparado com consultas ad hoc.

Stored Procedures são mais rápidas pois reduzem o tráfego na rede, são seguras e podem encapsular código reutilizável. Além de tudo isso, possuem a vantagem de ter um plano de execução, criado na primeira execução e armazenado no plan cache, evitando futuras recompilações.

Stored procedures são recursos importantes e poderosos que podem melhorar significativamente o desempenho das aplicações. Não utilizá-las é sinal de despreparo ou de falta de cuidado disfarçado de preciosismo.

O Problema

CREATE OR ALTER PROCEDURE dbo.usp_GetPerson 
( 
    @BusinessEntityID INT = NULL, 
    @StateProvinceID INT = NULL, 
    @CountryRegionCode NVARCHAR(3) = NULL 
) 
AS 
BEGIN

SET NOCOUNT ON

SELECT
A.FirstName,
A.LastName
FROM Person.Person as A
LEFT OUTER JOIN Person.BusinessEntityAddress as B
  ON A.BusinessEntityID = B.BusinessEntityID
LEFT OUTER JOIN Person.Address as C
  ON B.AddressID = C.AddressID 
LEFT OUTER JOIN Person.StateProvince as D
  ON C.StateProvinceID = D.StateProvinceID 
LEFT OUTER JOIN Person.CountryRegion as E
  ON D.CountryRegionCode = E.CountryRegionCode
WHERE (@BusinessEntityID IS NULL OR A.BusinessEntityID = @BusinessEntityID) 
  AND (@StateProvinceID IS NULL OR C.StateProvinceID = @StateProvinceID) 
  AND (@CountryRegionCode IS NULL OR D.CountryRegionCode = @CountryRegionCode)

END

Recorrentemente vemos procedures que recebem uma série de parâmetros, representando diferentes filtros na aplicação. Considere como exemplo, a procedure abaixo, criada no banco “AdventureWorks”:

CREATE OR ALTER PROCEDURE dbo.usp_GetPerson 
( 
    @BusinessEntityID INT = NULL, 
    @StateProvinceID INT = NULL, 
    @CountryRegionCode NVARCHAR(3) = NULL 
) 
AS 
BEGIN

SET NOCOUNT ON

SELECT
A.FirstName,
A.LastName
FROM Person.Person as A
LEFT OUTER JOIN Person.BusinessEntityAddress as B
  ON A.BusinessEntityID = B.BusinessEntityID
LEFT OUTER JOIN Person.Address as C
  ON B.AddressID = C.AddressID 
LEFT OUTER JOIN Person.StateProvince as D
  ON C.StateProvinceID = D.StateProvinceID 
LEFT OUTER JOIN Person.CountryRegion as E
  ON D.CountryRegionCode = E.CountryRegionCode
WHERE (@BusinessEntityID IS NULL OR A.BusinessEntityID = @BusinessEntityID) 
  AND (@StateProvinceID IS NULL OR C.StateProvinceID = @StateProvinceID) 
  AND (@CountryRegionCode IS NULL OR D.CountryRegionCode = @CountryRegionCode)

END

Embora seja uma consulta simples, a mesma gera um plano de execução de certa forma complexo, como pode ser observado na imagem abaixo:

Cabe destacar que, mesmo não utilizando as tabelas de endereço, de acordo com os parâmetros da chamada, é processada a junção das mesmas. Outro aspecto importante é que, para a consulta acima, o Optimizer acaba dispensando o melhor índice, percorrendo todos registros da tabela (index scan no índice AK_person_rowguid) ao invés de uma busca direcionada, através de um index seek (índice PK_Person_BusinessEntityID).

Mas o que acontece se alterarmos os parâmetros passados para a procedure?

Constata-se que o plano de execução foi reutilizado, mesmo com parâmetros que levariam a uma consulta diferente. Trata-se de um problema bem conhecido: Parameter Sniffing. Para quem não sabe o que faz, o benefício de uma ferramenta converte-se, rapidamente, em um pesadelo.

Existem soluções paliativas para resolver o problema  do Parameter Sniffing  em produção, quando não possuímos tempo para uma solução definitiva.

Uma destas soluções  é identificar o plano de execução que está no cache e utilizar o comando DBCC FREEPROCCACHE <PLANO DE EXECUÇÃO> para removê-lo. Isto irá forçar que a próxima execução da procedure gere um novo plano de execução. CUIDADO, ao executar essa instrução sem informar parâmetros, serão removidos TODOS os planos de execução do seu plan cache, fazendo com que o SQL Server tenha mais trabalho, visto que irá recompilar todos os planos de execução quando as consultas forem novamente executadas. Isso pode consumir bastante recursos do servidor!

DBCC FREEPROCCACHE (0x060006000C99302500499EFC8801000001000000000000000000000000000000000000000000000000000000);

Outra possível solução para o problema em questão, é utilizar o hint OPTION (RECOMPILE) ao final da instrução SELECT. Embora essa solução resolva o problema de forma mais persistente e efetiva do que a remoção do plano do cache, irá fazer com que o SQL Server descarte qualquer plano de execução salvo para a consulta e crie um novo plano de execução sempre que a procedure for chamada. Isso pode ocasionar um consumo alto de recursos no seu servidor, caso a stored procedure seja executada com muita frequência:

CREATE OR ALTER PROCEDURE dbo.usp_GetPerson
( 
    @BusinessEntityID  INT = NULL,
    @StateProvinceID   INT = NULL,
    @CountryRegionCode NVARCHAR(3) = NULL
)
AS
BEGIN
 
SET NOCOUNT ON 
 
SELECT
A.FirstName, 
A.LastName
FROM Person.Person as A 
LEFT OUTER JOIN Person.BusinessEntityAddress as B 
       ON A.BusinessEntityID = B.BusinessEntityID 
LEFT OUTER JOIN Person.Address as C 
  ON B.AddressID = C.AddressID 
LEFT OUTER JOIN Person.StateProvince as D 
  ON C.StateProvinceID = D.StateProvinceID 
LEFT OUTER JOIN Person.CountryRegion as E 
  ON D.CountryRegionCode = E.CountryRegionCode 
WHERE (@BusinessEntityID IS NULL OR A.BusinessEntityID = @BusinessEntityID) 
  AND (@StateProvinceID IS NULL OR C.StateProvinceID = @StateProvinceID) 
  AND (@CountryRegionCode IS NULL OR D.CountryRegionCode = @CountryRegionCode) 
<strong>OPTION (RECOMPILE)</strong>
 
END

Ao chamar novamente a procedure, com parâmetros diferentes, um plano de execução mais performático é gerado:

A Solução

Dynamic SQL são consultas que são construídas sob demanda antes de serem executadas, sendo que o funcionamento da consulta pode variar de acordo com os parâmetros de entrada informados. Utilizando Dynamic SQL pode-se resolver os problemas de otimização de código e parameter sniffing de uma forma mais eficaz:

CREATE   PROCEDURE [dbo].[usp_GetPersonOptimized]
(
@BusinessEntityID INT = NULL,
@StateProvinceID INT = NULL,
@CountryRegionCode NVARCHAR(3) = NULL
)
AS
BEGIN

SET NOCOUNT ON

declare @query nvarchar(max) = '
SELECT
A.FirstName,
A.LastName
FROM Person.Person as A ' +
IIF(@StateProvinceID IS NULL AND @CountryRegionCode IS NULL, '', '
INNER JOIN Person.BusinessEntityAddress as B ON A.BusinessEntityID = B.BusinessEntityID 
INNER JOIN Person.Address as C ON B.AddressID = C.AddressID
INNER JOIN Person.StateProvince as D ON C.StateProvinceID = D.StateProvinceID ') +
IIF(@CountryRegionCode IS NULL, '', '
INNER JOIN Person.CountryRegion as E ON D.CountryRegionCode = E.CountryRegionCode ') + '
WHERE 1 = 1 ' +
IIF (@BusinessEntityID IS NULL, '', '
  AND A.BusinessEntityID = @BusinessEntityID ') +
IIF (@StateProvinceID IS NULL, '', '
  AND C.StateProvinceID = @StateProvinceID ') +
IIF (@CountryRegionCode IS NULL, '', '
  AND D.CountryRegionCode = @CountryRegionCode ')

EXEC sp_executesql
@stmt = @query,
@params = N'@BusinessEntityID INT, @StateProvinceID INT, @CountryRegionCode NVARCHAR(3)',
@BusinessEntityID = @BusinessEntityID,
@StateProvinceID = @StateProvinceID,
@CountryRegionCode = @CountryRegionCode

END
GO

As vantagens em utilizar Dynamic SQL nas procedures para resolver esse tipo de problema, são:

• Utiliza um plano de execução ideal de acordo com os parâmetros passados.
• O uso da sp_executesql faz com que o plano de execução seja colocado em cache e reutilizado pelo SQL Server posteriormente, sempre que uma consulta semelhante vier a ser executada.
• Faz com que somente os objetos realmente necessários para sua consulta (de acordo com os parâmetros passados), sejam utilizados, otimizando a performance dela.
• Com a alteração da procedure foi possível trocar o LEFT OUTER JOIN por INNER JOIN, outro pequeno ganho de performance.
• Evita sql injection visto que os parâmetros da sua procedure são repassados como parâmetros, também para sua consulta dinâmica, evitando assim utilizar a concatenação dos valores

Para ilustrar os benefícios desse recurso, ao efetuar a chamada da procedure simultaneamente, porém passando parâmetros diferentes, percebe-se que os planos de execução mudaram para cada consulta, tendo o SQL Server optado pelo plano ideal para ambos os casos:

Conclusão

Embora utilizar essa abordagem tenha a desvantagem de serem criados e armazenados em cache mais planos de execução para a mesma procedure (a cada configuração diferente de parâmetros deverá ser criado um novo plano em cache), o ganho de performance frequentemente compensa a sua utilização, uma vez que os planos de execução gerados pelo SQL Server serão planos ideais.

A ideia de que utilizar stored procedure não é uma boa prática no desenvolvimento de software, é uma visão simplista, que limita a solução ideal. Quando utilizamos esse recurso de maneira adequada, podemos proporcionar ganhos significativos de performance em comparação a consultas AD HOC.