none
Stored Procedure demora demais no ASP RRS feed

  • Pergunta

  • Pessoal,

    Tenho uma stored procedure que demora míseros 560ms ao ser executada pelo SQL Management Studio ("na mão"), mas quando faço a mesma chamada no ASP, demora em torno de 2 minutos!!!! (o.O)

    Já tentei executar utilizando a dll da empresa, e nada. Fiz a chamada utilizando ADO direto no banco, e nada. Já verifiquei se está chamando do bd certo, e está.

    Até agora o DBA da empresa não soube me falar o que pode ser. Ele apenas insistiu em colocar SET DATEFORMAT antes da chamada, mas isso, como eu já imaginava, não resolveu nada também.

    Alguém tem alguma idéia do que pode estar acontecendo?


    Editado:
      SQL Server 2005
      Linguagem ASP (infelizmente, não é .Net Big Smile)
    segunda-feira, 5 de janeiro de 2009 15:19

Respostas

  • Dropei a constraint unique da tabela de metas e criei como unique index. Pelo o que o DBA daqui da empresa disse, muda apenas a forma de armazenar os dados, e que o funcionamento nos dois casos é o mesmo, certo? Também dropei e recriei todas as functions e procedures que são utilizadas nas consultas.

    Não me pergunte o que aconteceu, mas tudo está funcionando da forma mais rápida possível! \o/

    Obrigado pela atenção de todos!
    terça-feira, 6 de janeiro de 2009 18:56
  • LSCampos,

     

    Quando você utilizar um índice do tipo unique, ele trabalha de forma parecida com as constrainst do tipo PK e organiza os dados da mesma forma que um índice clustered.

     

    Quando eliminamos um índice e criamos novamente, o SQL Server esta gerando uma nova estrutura de armazenamento de dados, as estatísticas de são atualizadas e isso melhorar o acesso as informações.

     

    Se você excluiu todas as functions e stored procedures, recriou novamente, este procedimento indicou ao SQL Server que ele deveria atualizar os planos de execução e caches das procedures, sendo que, todas as vezes que o plano de execução é atualizado o SQL Server vai a consulta sobre as estatísticas.

    terça-feira, 6 de janeiro de 2009 19:06
  • Boa Tarde LSCampos,

     

    A constraint unique e o índice unique são praticamente equivalentes e não há diferença por conta "da forma de armazenar os dados". Quando você cria uma constraint unique, nos bastidores é criado também um índice unique para garantir que a constraint tenha efeito. Esse índice unique fica vinculado a constraint impossibilitando que o mesmo seja eliminado acidentalmente.

     

    Criar um índice unique naturalmente vai produzir um índice unique só que nesse caso é algo mais explícito. Nas duas situações um índice unique será criado. Sou adepto de utilizar constraints uniques e não índices uniques. Se um DBA visualizar um índice Unique que não é utilizado para consultas ou precisar de espaço ele se sentirá muito à vontade para excluí-lo (e poderá fazê-lo). Se um DBA visualizar uma constraint unique, as coisas são diferentes, porque independente de desempenho, o DBA saberá que ela está ali para garantir unicidade e dificilmente irá eliminá-la.

     

    [ ]s,

     

    Gustavo

     

    terça-feira, 6 de janeiro de 2009 19:08

Todas as Respostas

  • +- o que a proc faz ? ela usa tabela temporaria, esta parecendo que sua proc quando chamada do asp nao esta aproveitando o plano de execucao ( aparentemente ), veja se o problema tambem nao esta no trafego de rede entre o servidor iis e seu banco, pode ser um problema tambem. vc. pode executar um profile e verificar como a proc esta sendo executada o comando que ela esta executando e verificar o plano de execucao, habilite o cache miss tambem no profiler para ver se ao achamar pelo asp nao esta forcando recompilacao, o comando set dateformat na pratica ira forcar a recompilacao da proc entao nao deve ajudar em nada nao.

     

    qq coisa retorne.

     

    segunda-feira, 5 de janeiro de 2009 15:25
  • O procedure faz o somatório de metas de todas as lojas da empresa. Dentro dessa proc há algumas chamadas de functions para saber quais são os atuais gestores de cada loja, qual a meta para cada serviço de cada loja (somando de acordo com o relatório), função para buscar o que chamamos de "pacotes de serviços" (modo de visualização para análise) e algumas coisas mais. No total, são 49.600 registros existentes na tabela de metas para 01/2009 até 12/2009 (número de lojas x número de serviços x 12 meses).

    Existem vários tipos de relatórios: Empresa, Gestores, Filiais, Pacotes e Serviços

    Quanto ao tráfego, imposível, pois a chamada da proc para o relatório de visão geral da EMPRESA possui 57 bytes, onde apenas 1 registro é retornado contendo 7 colunas
    (1 int, 1 varchar(20) e 5 numeric(9, 2)).

    Vou ver se consigo alguma informação pelo profile então.
    Qualquer coisa, volto a postar aqui.

    Editado:
      A procedure não usa tabelas no TEMPDB (#), apenas tabelas variáveis (@) com poucos dados (somatórios). Eu sempre procuro fazer um select que retorne tudo calculado, sem precisar utilizar cursores. Sempre tive problema com eles, da mesma forma que já tive problemas de performance com alguns LEFT JOINs, DATEPART(), YEAR(), MONTH(). rsrs

    Obrigado!
    segunda-feira, 5 de janeiro de 2009 15:51
  • Ahhh... Só faltou falar uma coisa...

    Eu "abri" toda a procedure em uma única consulta gigante de 249 linhas (considerando linhas com apenas 1 abre ou fecha parênteses), substituindo todas as chamadas de funções pelo seu código. Fiz isso até não ter mais nenhuma chamada de função dentro do select. As variáveis utilizadas pelas funções foram declaradas e setadas antes do select monstro. Detalhe, tudo rodou com 1500ms. O aumento do tempo já era esperado, pelo fato de nao estar compilado e ter que fazer o parser.

    Ainda não verifiquei o profile. Decidi fazer esse teste e fui alimentar a minha lumbriga. Big Smile Agora eu vou importar os dados da base de produção na minha base de desenv para fazer novos testes e verificar o profile.
    segunda-feira, 5 de janeiro de 2009 17:34
  • Boa Tarde,

     

    A utilização de variáveis do tipo TABLE em stored procedures podem aumentar o tempo de resposta em relação a tabelas temporárias com #. Essa não é uma afirmação, visto que há prós e contras e nenhuma das implementações é a melhor para todos os casos. Se há desconfiança do plano de execução, sugiro algumas alternativas (elas são independentes):

     

    - Utilizar a cláusula WITH RECOMPILE para medir os tempos sem considerar planos pré-compilados

    - Substituir as tabelas do tipo TABLE por tabelas com #

     

    Verifique no SQL Server a coluna Duration para o evento StoredProcedure:RPCCompleted para ver o tempo de execução do comando. Se o tempo for aceitável, o resto provavelmente estará entre o ADO e o SQL Server. Nesse caso, seria interessante verificar questões referentes a bloqueios e níveis de isolamento.

     

    [ ]s,

     

    Gustavo

    segunda-feira, 5 de janeiro de 2009 19:46
  • Um dos motivos por também ter utilizado variáveis TABLE, foi por estarem dentro de funções. Costumo utilizar variáveis (@) no lugar de tabelas no TEMPDB (#) quando a quantidade de dados é pouca, para não consumir muita memória do servidor. Vendo que o acesso à memória é mais rápido do que em disco, acho que vale à pena em alguns casos. Já vi várias perguntas e discursões sobre isso em fóruns de BD, e sei que isso dá é pano pra manga. rsrs

    Mas vamos lá, o problema é o nosso tempo na procedure. ;D

    Executei:

    SET STATISTICS TIME ON
    EXEC dbo.stp_MINHAPROC @NR_MES = 1, @NR_ANO = 2009 WITH RECOMPILE

    Saída:

    SQL Server parse and compile time:
       CPU time = 15 ms, elapsed time = 15 ms

    [Apenas para exemplo do retorno]
    Código da Empresa: 1 [int]
    Nome da Empresa: Teste Ltda [varchar(20)]
    Meta: 1234.56 [numeric(9,2)]
    Meta Proporcional: 123.45
    [numeric(9,2)]
    Realizado: 100.00 [numeric(9,2)]
    % de Atingimento: 81.00 [numeric(9,2)]
    Desvio: -23.45 [numeric(9,2)]

    (1 row(s) affected)


    SQL Server Execution Times:
       CPU time = 735 ms,  elapsed time = 763 ms.

    SQL Server Execution Times:
       CPU time = 750 ms,  elapsed time = 778 ms.




    Quanto ao
    StoredProcedure:RPCCompleted, executei o Profiler para o SQL Express (baixei aqui: http://sqlprofiler.googlepages.com) e me mostrou os seguintes resultados:

      
    TextData: exec stp_MINHAPROC 1,2009,default,default,default,default,default
       Duration: 178867 (isso é em nanosegundos? [178ms])
       Reads: 55113 (tenho 50.000 metas para 80 lojas e 130 serviços)
       Writes: 57
       CPU: 156


    Fiz um teste para o relatório do ano de 2008 e funciona normalmente. A diferença é que não existem metas cadastradas para 2008. No início eu tinha a seguinte seqüência de chamadas (estou simplificando parte da procedure):

         Página ASP <--> Proc_1 <--> Func_1
    <--> Func_2 <--> BD

    Testei assim, sem qualquer meta cadastrada, e o relatório era exibido tanto para 2008 quanto para 2009. Depois eu tive que alterar a forma de cadastro de metas. Com isso, uma nova tabela foi criada, a Func_2, que lia a tabela de metas antiga, foi dropada e criei a Func_3, responsável pela nova tabela. A Func_1, que chamava a Func_2, foi alterada para chamar a Func_3. Ficou da seguinte forma:

         Página ASP <--> Proc_1 <--> Func_1 <--> Func_3 <--> BD

    Em seguida, foram inseridas 50.000 para o ano inteiro. Fui testar e não funcionou com a tabela de metas populada. Puxei relatório do ano de 2008, sem metas, e retorna no tempo previsto. 2009, nem pensar! rsrs


    Pensei em algo referente à base de dados pelo fato de todo o sistema estar funcionando normalmente. Só adicionei uma página ASP de teste que faz a chamada dessa procedure, a forma de acesso continou a mesma coisa, mas nada de resposta com menos de 2 minutos para algo que mal demora meio segundo.

    Eu sou brasileiro e não desisto nunca! \o/
    segunda-feira, 5 de janeiro de 2009 20:44
  • Olá LSCampos,

     

    De fato # x @ dá muito pano pra manga, mas adianto que os requisitos de memória e disco são praticamente os mesmos (há uma discussão muito sólida sobre esse assunto em Inside SQL Server 2005 - T-SQL Programming).

     

    Gostaria de saber se é possível executar a SP com o WITH RECOMPILE na aplicação da mesma forma que você utilizou no SET STATISTICS TIME. A julgar pelas métricas do Profiler ela está lendo uma quantidade incrível de páginas e demorando mais do que o previsto. Possivelmente há problemas com o plano de execução.

     

    [ ]s,

     

    Gustavo

    segunda-feira, 5 de janeiro de 2009 22:11
  • se vc. criar a proc com a opcao with recompile ele sempre vai forcar a recompilacao, nas funcoes que retornan um table vc. consegue especificar uma chave na criacao ? se consegue vc. pode usar ela como index nas consultas.

     

    Abs;

     

    terça-feira, 6 de janeiro de 2009 12:16
  • Colla,

     

    Você acredito que utilizar estas chaves como índices para as consultas terá uma performance superior?

     

    terça-feira, 6 de janeiro de 2009 12:20
  •  

    ajuda sim veja estes testes ( olhe o plano de execucao das duas rotinas )

     


    Declare  @Tabela1 table (Chave int , nome varchar(10))

    Insert into @Tabela1 (Chave, Nome) Values (1, 'MArcelo 1')
    Insert into @Tabela1 (Chave, Nome) Values (2, 'MArcelo 2')
    Insert into @Tabela1 (Chave, Nome) Values (3, 'MArcelo 3')
    Insert into @Tabela1 (Chave, Nome) Values (4, 'MArcelo 4')
    Insert into @Tabela1 (Chave, Nome) Values (5, 'MArcelo 5')


    Go

    Declare  @Tabela1 table (Chave int primary key, nome varchar(10))

    Insert into @Tabela1 (Chave, Nome) Values (1, 'MArcelo 1')
    Insert into @Tabela1 (Chave, Nome) Values (2, 'MArcelo 2')
    Insert into @Tabela1 (Chave, Nome) Values (3, 'MArcelo 3')
    Insert into @Tabela1 (Chave, Nome) Values (4, 'MArcelo 4')
    Insert into @Tabela1 (Chave, Nome) Values (5, 'MArcelo 5')

     

    Abs;

    terça-feira, 6 de janeiro de 2009 12:29
  • Colla,

     

    Eu sei que melhora, só não estava conseguindo visualizar a sua idéia!!!

    terça-feira, 6 de janeiro de 2009 13:12
  • Dropei a constraint unique da tabela de metas e criei como unique index. Pelo o que o DBA daqui da empresa disse, muda apenas a forma de armazenar os dados, e que o funcionamento nos dois casos é o mesmo, certo? Também dropei e recriei todas as functions e procedures que são utilizadas nas consultas.

    Não me pergunte o que aconteceu, mas tudo está funcionando da forma mais rápida possível! \o/

    Obrigado pela atenção de todos!
    terça-feira, 6 de janeiro de 2009 18:56
  • LSCampos,

     

    Quando você utilizar um índice do tipo unique, ele trabalha de forma parecida com as constrainst do tipo PK e organiza os dados da mesma forma que um índice clustered.

     

    Quando eliminamos um índice e criamos novamente, o SQL Server esta gerando uma nova estrutura de armazenamento de dados, as estatísticas de são atualizadas e isso melhorar o acesso as informações.

     

    Se você excluiu todas as functions e stored procedures, recriou novamente, este procedimento indicou ao SQL Server que ele deveria atualizar os planos de execução e caches das procedures, sendo que, todas as vezes que o plano de execução é atualizado o SQL Server vai a consulta sobre as estatísticas.

    terça-feira, 6 de janeiro de 2009 19:06
  • Boa Tarde LSCampos,

     

    A constraint unique e o índice unique são praticamente equivalentes e não há diferença por conta "da forma de armazenar os dados". Quando você cria uma constraint unique, nos bastidores é criado também um índice unique para garantir que a constraint tenha efeito. Esse índice unique fica vinculado a constraint impossibilitando que o mesmo seja eliminado acidentalmente.

     

    Criar um índice unique naturalmente vai produzir um índice unique só que nesse caso é algo mais explícito. Nas duas situações um índice unique será criado. Sou adepto de utilizar constraints uniques e não índices uniques. Se um DBA visualizar um índice Unique que não é utilizado para consultas ou precisar de espaço ele se sentirá muito à vontade para excluí-lo (e poderá fazê-lo). Se um DBA visualizar uma constraint unique, as coisas são diferentes, porque independente de desempenho, o DBA saberá que ela está ali para garantir unicidade e dificilmente irá eliminá-la.

     

    [ ]s,

     

    Gustavo

     

    terça-feira, 6 de janeiro de 2009 19:08
  • Vivendo e aprendendo!!! Por isso que eu gosto de ficar lendo as discursões desse fórum, mesmo que eu não possa opinar pela falta de conhecimento aprofundado, mas é sempre de grande valor.

     

    Se eu descobrir que algum ele excluiu um dos meus índices, eu racho a cabeça dele no meio! hauhauhau Brincadeiras á parte!

     

    Peguei "Microsoft SQL 2005 - Implementation and Meintenance" para começar a estudar mais e mais. Não é a minha área foco, mas como temos que saber programar, banco de dados, BI, conceitos administrativos, conceitos jurídicos aplicados à empresas e blah, blah, blah, não nos resta outra saída a não ser viver lendo tudo o que der conta. :-)

     

    Sistema funcionando e cliente muito mais feliz! \o/

    Obrigado pela atenção de todos!

    segunda-feira, 12 de janeiro de 2009 21:37