none
Procedure [Lentidão com Variáveis] RRS feed

  • 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?

    sexta-feira, 30 de agosto de 2019 13:04

Todas as Respostas

  • Deleted
    sexta-feira, 30 de agosto de 2019 13:14
  • 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?

    sexta-feira, 30 de agosto de 2019 13:28
  • Deleted
    sexta-feira, 30 de agosto de 2019 13:51
  • 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]

    sexta-feira, 30 de agosto de 2019 18:10