Procedure [Lentidão com Variáveis]

Question

Bom dia! Eu tenho um caso meio curioso.

Tenho a seguinte procedure:

alter procedure [dbo].[SP_LISTA_ESTOQUE_GERAR_BLOCO_K_TESTE]
@dataInicio datetime
as
begin
declare @cnpj varchar(14)
set @cnpj = '00000000000000'
SELECT [COD_ITEM]  FROM [dbo].[VW_Estoque_Proprio]  where dt_Referencia= FORMAT(@dataInicio,'MM/yyyy') and CNPJ_Destinatario=@cnpj 
END

Se eu executar normalmente: 

execute [dbo].[SP_LISTA_ESTOQUE_GERAR_BLOCO_K_TESTE] '06/30/2019'

A procedure demora em torno de 5 minutos.

Só que se eu colocar fixo:

SELECT [COD_ITEM]  FROM [dbo].[VW_Estoque_Proprio]  where dt_Referencia= '06/2019' and CNPJ_Destinatario='00000000000000'

A consulta demora menos de 1 segundo.

Qual a lógica disso e existe uma luz para resolver?

Answer 1

Deleted

Answer 2

José, tudo bem? Muito obrigado por disponibilizar seu tempo para ajudar.

Eu fiz uns testes aqui e criei da seguinte forma:

alter procedure [dbo].[SP_LISTA_ESTOQUE_GERAR_BLOCO_K_TESTE]
@dataInicio datetime, @cnpj varchar(14)
as
begin

SELECT [COD_ITEM]  FROM [dbo].[VW_Estoque_Proprio]  where dt_Referencia= FORMAT(@dataInicio,'MM/yyyy') and CNPJ_Destinatario=@cnpj 
END

Ou seja, ao invés de criar uma variável e setar, eu já envio como parâmetro.
Dai funciona normalmente! tem algum sentido isso?

Answer 3

Deleted

Answer 4

Paulo,

Esta é uma das situações que podem ocorrer normalmente no SQL Server quando utilizamos variáveis em conjunto com nossas Stored Procedures, conhecido este comportamento como parameter sniffing, o qual o SQL Server acaba gerando um plano de execução que tem tratar as variáveis de forma independente ao que esta sendo processado isso impacta diretamente na obtenção dos dados.

Umas das possibilidades que se pode aplicar neste cenário sem exigir muitas alterações no Código fonte da stored procedure se relaciona com o uso da query hint  OPTIMIZE FOR () recurso que nos possibilitar alterar um pouco o comportamento da query, direcionando que as variáveis que ela utilizando em conjunto com a stored procedure seja otimizadas e anexadas na mesma estrutura do plano de execução definido para toda Stored Procedure.

Vou compartilhar um exemplo que eu utilizo bastante em minhas aulas, o qual é de minha autoria:

-- Criando o Banco de Dados 
Create Database ParameterSniffing
Go

-- Criando a TabelaDados --
Create Table TabelaDados
(Id Int Identity Primary Key,
 Descricao Char(100),
 Localal Char(6),
 DataCriacao DateTime Default Getdate(),
 Status char(2) default 'AC')
Go

-- Criando Índice na coluna Local para TabelaDados --
Create Index IND_Local on TabelaDados(Local)
Go

-- Inserindo massa de dados --
Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalA')
GO 100000

Insert Into TabelaDados (Name,Local) VALUES ('Test1','LocalB')
GO 10

-- Selecionando dados na TabelaDados com Local = 'LocalA' --
Select * From TabelaDados
Where Local = 'LocalA'
Go

-- Selecionando dados na TabelaDados com Local = 'LocalA' --
Select * From TabelaDados
Where Local = 'LocalB'
Go

-- Criando a Stored Procedure 
Create Procedure ObterDadosPorLocal (@Local Char(6))
As
Begin

 Set NoCount ON

 Select * From TabelaDados
 Where Local = @Local
End


-- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
ObterDadosPorLocal 'LocalA'
Go

-- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
ObterDadosPorLocal 'LocalB'
Go

-- Obtendo informações sobre Plano de Execução de Stored Procedures --
Select OBJECT_NAME(s.object_id) SP_Name,
       eqp.query_plan
From sys.dm_exec_procedure_stats s CROSS APPLY sys.dm_exec_query_plan (s.plan_handle) eqp
Where DB_NAME(database_id) = 'ParameterSniffing'

-- Recompilando a Stored Procedure --
SP_Recompile 'ObterDadosPorLocal'
Go

-- Alterando a Stored Procedure ObterDadosPorLocal adicionando a opção Recompile --
Alter Procedure ObterDadosPorLocal (@Local as Char(6)) With Recompile
As
Begin

 Set NoCount On

 Select * From TabelaDados
 Where Local = @Local
End

-- Executando novamente a Stored Procedure ObterDadosPorLocal, descartando o Cache do Plano de Execução --
Exec ObterDadosPorLocal @Local= N'LocalA' 
Exec ObterDadosPorLocal @Local= N'LocalB' 
Go

-- Executando novamente a Stored Procedure ObterDadosPorLocal, recompilando e utilizando o do Plano de Execução, sem fazer o armazenamento --
Exec [dbo].[ObterDadosPorLocal] @Local = N'LocalA' WITH RECOMPILE
Go

-- Alterando a Stored Procedure ObterDadosPorLocal removendo a opção Recompile e adicionando a Query Hint Optimize --
Alter Procedure ObterDadosPorLocal (@Local CHAR(6))
As
Begin

 Set NoCount On

 Select * From TabelaDados
 Where Local = @Local
 OPTION (OPTIMIZE FOR (@Local = 'LocalA'))

End

-- Executando a Stored Procedure ObterDadosPorLocal = LocalA --
ObterDadosPorLocal 'LocalA'
Go

-- Executando a Stored Procedure ObterDadosPorLocal = LocalB --
ObterDadosPorLocal 'LocalB'
Go

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]