none
Stored Procedure lenta RRS feed

  • Discussão Geral

  • Boas senhores, estou rodando uma sp de um banco slqserver2005.

    Tenho uma SP, de um sistema antigo, quem criou não existe mais....

    O aplicativo que usa esta sp, começou a ter retornos de msg com timeout....

    Podendo considerar algo feito de forma errada, seria alguns lefts join e o resultado com distinct...de resto esta dentro do normal....

    A SP leva 1.50s para rodar o select....com retorno de 30 linhas

    Se rodo so o select fora da sp...leva 8s com as mesma linhas....

    Ja rodei EXEC SP_UPDATESTATS

    Ja rodei a sp forçando a compilação....

    Pesquisei aqui e internet,  vi caso de sp muito complexas....mas nada de solução....

    Alguém tem alguma dica?

    obrigado e t+

    Segue uma esboço da sp...

    USE [MEUBANCO]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[filtro] 
    @Empresa varchar(3),
    @DI varchar(20),
    @DF varchar(20)
    AS
    BEGIN
    SET NOCOUNT ON;

    DECLARE @dataFinal DATETIME
    DECLARE @dataInicial DATETIME

        SET @DI = @DI + ' 00:00:00'
        SET @DF = @DF + ' 23:59:59'

        SET @dataFinal = CONVERT(datetime, @DF)
    SET @dataInicial = CONVERT(datetime, @DI)

        SELECT DISTINCT  
    tb1.IDEmpresa,
    tb0.IDVer, 
    tb4.IDControle,
                tb0.dtentrada, 
                tb0.ser_dt_saida, 
    SUBSTRING(tb3.idNr, 1, 4) +
    SUBSTRING(tb3.idNr, 9, 13) as doc,  
    tb3.IDDoc, 
    tb2.numero
    from 
    tb0 
    left join tb4 on (tb4.IDEmpresa = tb0.IDEmpresa and tb4.IDSer = tb0.IDSer)
    inner join tb3 on (tb3.IDEmpresa = tb0.IDEmpresa and tb3.IDSer = tb0.IDSer)
    inner join tb2 on (tb2.IDEmpresa = tb3.IDEmpresa and tb2.IDLote = tb3.IDLote)
    inner join tb1 on (tb1.IDEmpresa = tb2.IDEmpresa and tb1.ben_id = tb2.cli_id)
    left  join tb5 on (tb5.IDEmpresa = tb3.IDEmpresa and tb3.IDLote = tb5.IDLote)
    join tb6 on tb6.IDEmpresa = tb3.IDEmpresa
    WHERE         
    tb1.IDEmpresa = @Empresa AND 
                tb0.dtentrada >= @dataInicial AND tb0.dtentrada <= @dataFinal
    ORDER BY 
    tb0.dtentrada
    END

    segunda-feira, 11 de maio de 2015 21:11

Todas as Respostas

  • Bom dia,

    Levando em consideração que nos seus testes existe uma diferença muito grande de tempo de execução da SP em comparação com o Select separadamente, acredito que as operações em cima das variáveis que estejam causando um gasto excessivo, ou seja, as concatenações e conversões.

    Se possível, faça testes eliminando trechos da SP onde existem as atribuições de valores as variáveis, para identificar exatamente onde ocorre o maior custo.

    Por favor, coloque aqui o plano de execução dessa SP para que possamos tentar enxergar o gargalo.


    Att, Bruno Silva.

    terça-feira, 12 de maio de 2015 14:41
  • Bruno,

    Vou na mesma linha de raciocínio seu, em diversos cenários no SQL Server o uso de variáveis em Stored Procedure acaba fazendo com que o SQL Server tenha que mudar de forma drástica a forma de execução da Stored Procedure o que leva em muitos casos na perda de performance.

    Existe uma query hint chamado Optimizer For que podemos declarar no Select para fazer com que o SQL Server trabalhe com as variáveis em conjunto com a Stored Procedure sem impactar tanto no plano de execução.

    Na verdade quando este query hint esta declarado, todo processo de recompilação do Plano de Execução da Stored Procedure acrescenta os valores das variáveis dentro do plano, isso faz com que a mudança de estratégia leve em consideração estes valores.

    Veja este link: http://blogs.msdn.com/b/sqlprogrammability/archive/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature.aspx


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    terça-feira, 12 de maio de 2015 17:37
  • Junior,

    Pensando rapidamente talvez seja a table hint OPTIMIZE FOR FOR UNKNOWN, quem sabe ela não ajuda.


    Att, Bruno Silva.

    terça-feira, 12 de maio de 2015 17:44
  • Junior,

    Pensando rapidamente talvez seja a table hint OPTIMIZE FOR FOR UNKNOWN, quem sabe ela não ajuda.


    Att, Bruno Silva.

    Bruno,

    Isso mesmo, eu lembre assim que terminei o post, após isso eu atualizei.


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 12 de maio de 2015 18:36
  • Deleted
    terça-feira, 12 de maio de 2015 21:48
  • Senhores obrigado pelo retorno....

    José Diz, na execução manual não criei as variáveis e agora com toda a pesquisa e comentários de vcs, vi que faz diferença....para apagar o fogo rapidamente....mexi nas datas, pois ficava pulando de variável ...Modifiquei as datas mudando o tipo para data e usei elas direto e rodou rapido.

    Mas amanhã terei que analisar melhor, pois não sei se outros locais usam esta sp, e como mudei o tipo ja viu ne....

    Amanhã retorno....

    abç a todos....

     

    terça-feira, 12 de maio de 2015 22:24
  • Bom dia senhores, fiz mais um teste....

    Vcs que são especialistas, vejam se estou fazendo besteira....

    No banco de dados as datas aparecem no seguinte formato: 2015-01-26 18:37:20.000

    A SP recebe as datas como varchar e são passadas como '01/25/2015' e '01/31/2015', (exemplo de um período)

    Se não me engano, este ja é o formato certo....portanto usei direto a variável da SP e o retorno ficou rápido....

    entenderam?

    quarta-feira, 13 de maio de 2015 14:19
  • Bom dia,

    No seu teste então você colocou os valores na variável dessa forma:

    SET @dataFinal = '01/31/2015'
    SET @dataInicial = '01/25/2015'


    Att, Bruno Silva.

    quarta-feira, 13 de maio de 2015 14:33
  • Bruno usei direto o @DI e @DF no lugar de @dataInicial e @ dataFinal...ou seja, não usei as variáveis declaradas

    E no formato que vc mostrou....

    quarta-feira, 13 de maio de 2015 16:37
  • Então provavelmente temos um custo alto nessa operação:

     SET @dataFinal = CONVERT(datetime, @DF)

    SET @dataInicial = CONVERT(datetime, @DI)

    Você já tentou dar uma olhada no plano de execução como foi sugerido anteriormente?


    Att, Bruno Silva.

    quarta-feira, 13 de maio de 2015 16:56
  • Novidades...

    Sobre o plano de execução, se colocar aqui poderei ter problemas, por conta de aparecer nomes dos artefatos.

    Os planos nos 2 casos tem os gráficos bem grandes. O que tenho que me atentar ao plano?

    A parte ruim do negocio...trocaram o período na pesquisa e voltou a ficar lento....

    quinta-feira, 14 de maio de 2015 14:29
  • Ao moderador...não percebi e minha pergunta ficou como uma "Discussão Geral" se quiser mudar para pergunta fique a vontade ok...
    quinta-feira, 14 de maio de 2015 14:33
  • Sem o plano de execução fica difícil, até por que sua complexidade é enorme e muito sensível a muitos fatores.

    Nessa troca de período aumentou a quantidade de registros retornados? Mesmo executando o Select a parte ficou lento?


    Att, Bruno Silva.

    quinta-feira, 14 de maio de 2015 14:36
  • Sim, aumentou a quantidade de registros e nos 2 casos ficaram lentas...

    Vou passar para o pessoal de banco de dados dar uma olhada.....

    Qq coisa aviso....

    Obrigado Bruno

    quinta-feira, 14 de maio de 2015 15:08
  • Ok.

    Quando tiver noticias relate aqui no fórum para podermos concluir a thread.


    Att, Bruno Silva.

    quinta-feira, 14 de maio de 2015 16:55
  • Sem retorno ainda da equipe de banco, tive que fazer um ajuste na aplicação controlando o timeout...

    Com esta ação, provavelmente irão esquecer e lembrar somente quando o banco estiver abrindo o bico....:-)

    Obrigado a todos


    • Editado DET1 sexta-feira, 15 de maio de 2015 16:51 ajuste
    sexta-feira, 15 de maio de 2015 16:50
  • Também já passei por isso rsrsrsr

    Caso alguma resposta tenha ajudado, não se esqueça de marcá-la para que possamos concluir a thread.


    Att, Bruno Silva.

    sexta-feira, 15 de maio de 2015 16:59
  • Nossa área ou ficamos loucos ou relaxamos...estou na fase de relaxar (no bom sentido)...

    então como a lista de buchas esta cheia, passei esta para o final da fila e bora pra próxima....:-)

    Marquei como útil, não localizei outra opção...marquei a mais relevante de todos os colegas....

    abç....

    sexta-feira, 15 de maio de 2015 19:39