none
Problema com Stored Procedure extremamente simples RRS feed

  • Pergunta

  • Boa noite.

    Estou usando SqlServer Express 2014 no Windows 10, e hoje começou a dar um problema estranho.

    Tenho a seguinte Stored Procedure:

    Create PROCEDURE sp_Proximo 
    	-- Add the parameters for the stored procedure here
    	@Ultimo int output
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	set @Ultimo=(select max(campo) as ult from tabela)
    	if @Ultimo is null 
    		set @Ultimo=1
    	else
    		set @Ultimo=@Ultimo+1

    Muito básica, só que está demorando cerca de 2,5 minutos para ser executada dentro do SQL. Quando executo no software da empresa, ocorre erro de tempo de execução ou seja, me diz que o Tempo Limite da Consulta foi expirado...

    Na tabela, não existem nem tantos registros que justifique tal problema. Normalmente esta consulta gastaria aproximadamente 1 segundo.

    Tentei substituir a Stored Procedure por código diretamente no software e me gera o mesmo problema, estoura o tempo limite da consulta...

    Alguém sabe me dizer o que pode estar ocorrendo?

    Desde já, Obrigado.

    segunda-feira, 12 de setembro de 2016 21:54

Respostas

Todas as Respostas

  • Deleted
    segunda-feira, 12 de setembro de 2016 21:57
  • Concordo...

    Uma das opções é usar a Sequence (já que é compatível com sua versão de sql)

    CREATE SEQUENCE Proximo START WITH 1 INCREMENT BY 1 ; GO

    SELECT NEXT VALUE FOR Test.CountBy1 AS FirstUse;

    Referência: https://msdn.microsoft.com/pt-br/library/ff878370.aspx

    Outra opção (além do campo Identity), é usar uma tabela: Imagine assim, você cria uma tabela chama Serie

    e uma Stored Procedure que ao executar grava o último número usado nesta tabela, e não a que está de fato na sua tabela de registro, assim você garante que se alguém pegar o número e ficar 30min parado, ninguém mais vai usar o mesmo número.

    Create Table Serie (nr_serie char(3), numero int)

           

    Create PROCEDURE sp_Proximo
        -- Add the parameters for the stored procedure here
         @nr_serie char(3)
        ,@Ultimo int output
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        IF NOT EXISTS(SELECT 0 FROM SERIE WHERE NR_SERIE = @NR_SERIE)
        INSERT INTO SERIE VALUES (@NR_SERIE,0)

        -- Insert statements for procedure here
        SELECT @Ultimo = MAX(NUMERO) + 1 FROM Serie

    exec sp_Proximo  ('NFE', OUTPUT)


    Isco Sistemas José Luiz Borges

    terça-feira, 13 de setembro de 2016 12:00
  • Deleted
    terça-feira, 13 de setembro de 2016 12:22
  • José Luiz, o uso de uma tabela para conter a última numeração utilizada é uma opção interessante. Mas deve-se ter cuidado ao pesquisar qual é o último número, tendo-se em mente a concorrência de processos.

    Por exemplo, no trecho
    10.   IF NOT EXISTS(SELECT 0 FROM SERIE WHERE NR_SERIE = @NR_SERIE)
    11.       INSERT INTO SERIE VALUES (@NR_SERIE,0)

    pode ocorrer que dois processos em execução simultânea tenham executado a linha 10 e retornem False. Então, o sistema operacional (SO) interrompe o processo P1, mas o processo P2 continua. Este, P2, executa a linha 11 e insere uma linha na tabela SERIE, com o valor 0 e é interrompido pelo SO. A seguir, o SO reativa o processo P1. Ele executa a linha 11, com P1 inserindo uma nova linha na tabela SERIE, com o valor 0, para a mesma série. Ou seja, teremos duas linhas com o valor 0 para uma mesma série.

    Outro problema, novamente considerando-se a concorrência de processos, está na linha
    13.   SELECT @Ultimo = MAX(NUMERO) + 1 FROM Serie

    Antes de mais nada, acho que o código da linha seria
    13.   SELECT @Ultimo = MAX(NUMERO) + 1 FROM Serie WHERE nr_serie = @nr_serie

    Supondo que o processo P2 esteja suspenso pelo SO mas o P1 continue ativo. Este, P1, executa a linha 13 e, tão logo obtenha o valor de @ULTIMO, por algum motivo o SO suspende P1 e reativa P2. O processo P2 executa a linha 13 e continua. Ou seja, retorna o valor 1. E, após P1 ser reativado, ele retornará o mesmo valor: 1.

    Considerando-se a concorrência de processos, a implementação do procedimento sp_Proximo precisa ser revista.

    PS: é preciso também considerar o isolation level, que pode alterar o comportamento de um procedimento.


        José Diz     Belo Horizonte, MG - Brasil
    (Se encontrou a solução nesta resposta, ou se o conteúdo foi útil, lembre-se de marcá-la)


    "Antes de mais nada, acho que o código da linha seria
    13.   SELECT @Ultimo = MAX(NUMERO) + 1 FROM Serie WHERE nr_serie = @nr_serie"

    Isso Mesmo errei ali...

    É claro que deve ser feito uma avaliação ao uso, quanto a concorrência de processos, isolate level, e até caraga em rede para uso...

    Só deixei uma opção (alternativa) para nosso amigo...

    Qual seria a melhor opção na sua visão para esta implementação ? Sequence ? Identity ?


    Isco Sistemas José Luiz Borges

    terça-feira, 13 de setembro de 2016 12:54
  • Olá,

    execute este código e veja se o tempo de retorno melhore.

    DECLARE @ULTIMO INT
    SET @ULTIMO = (SELECT TOP 1 CAMPO FROM TABELA ORDER BY CAMPO DESC)
    IF @ULTIMO IS NULL
    SET @ULTIMO = 1
    ELSE
    SET @ULTIMO = @ULTIMO + 1

    PRINT @ULTIMO

    Caso o tempo tenha melhorado aconselho a mudar algumas coisas.

    1. Nome da Procedure -> PROC_PROXIMO, porque a palavra sp_ faz com que o banco procure por esta procedure primeiro na base MASTER antes de ir para a base especificada.

    2. Caso queira procurar o último registro de uma tabela use o SELECT TOP 1 com ORDER BY DESC pois a otimização será melhor.

    Abs.


    Leandro de Agostini MCTS - Web Application, Framework 4

    terça-feira, 13 de setembro de 2016 13:06
  • Deleted
    terça-feira, 13 de setembro de 2016 13:32
  • Deleted
    terça-feira, 13 de setembro de 2016 13:43
  • Olá José Diz,

    obrigado pela informação, principalmente com relação ao CAMPO possuir índice. Estou acostumado a colocar índices nos campos que as aplicações fazem referência para melhorar o tempo de resposta, e através de sua informação pude constatar que estou fazendo o correto.

    Obrigado.


    Leandro de Agostini MCTS - Web Application, Framework 4

    terça-feira, 13 de setembro de 2016 14:07
  • Boa tare, pessoal.

    Primeiramente agradeço a todos pela discussão.

    Acontece o seguinte: após a minha postagem, que foi em um final de semana, dei um tempo e deixei para pensar no problema na inicio da próxima. Aconteceu que na segunda feira tudo estava funcionando sem nenhum problema, isso até hoje, quando o problema voltou e hoje pude fazer mais uma batelada de testes e até o momento em vão.

    Antes de mais nada, gostaria de dizer que tenho Stored Procedures similares rodando no SQLServer 2005 express sem problemas.

    Bom, meus testes de hoje:

    * tentar buscar o ultimo registro da tabela com stored procedure através da aplicação - erro de timeout;

    * simulação da stored procedure diretamente no sql server - ok

    * na aplicação substituí a sp por uma consulta sql ao banco (max(campo)) - erro de timeout

    * na aplicação executando uma consulta com todos os registros da tabela e tentando me mover para o ultimo registro - erro de timeout

    * o campo em questão esta indexado (clusterizado)

    * supondo que poderia se tratar de um erro na tabela, recriei a tabela e abasteci com os dados originais, ao executar a consulta pela aplicação - erro de timeout

    A questão é não tinha este tipo de problema com o SQL 2005 express e agora usando o SQL 2014 express estou tendo este incomodo.

    Desculpem minha ignorância mas para uma instrução tão besta, extremamente simples, o que poderia estar gerando este problema? O pior é que como disse, parou de apresentar o problema e agora voltou...

    Obrigado a todos pela atenção.

    sábado, 1 de outubro de 2016 18:57