Inquiridor
Procedure [Lentidão com Variáveis]

Pergunta
-
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?
Todas as Respostas
-
-
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? -
Deleted
- Sugerido como Resposta Junior Galvão - MVPMVP terça-feira, 8 de outubro de 2019 13:21
-
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]
- Sugerido como Resposta Junior Galvão - MVPMVP terça-feira, 8 de outubro de 2019 13:21