Sql-server – Stored procedure runs forever, but inner query runs fine

performancequery-performancesql-server-2008stored-procedurest-sql

If I run exec pr_BuscaLog -2, it will run forever (days, without end).

But, if I just copy the query and run it on its own, it runs in seconds.

Updating statistics did not help.

--pr_BuscaLog -1  
CREATE procedure [dbo].[pr_BuscaLog] (@qtdDias int)          
as          
begin          
declare @cmd as nvarchar(4000)          
declare @banco as varchar(100)          
declare @dataLimite as datetime

UPDATE: When I run EXEC PR_BUSCALOG, it start to run, and then stops.

Best Answer

It is difficult to tell just from examining the code if it is running forever because of an infinite loop or blocking - my guess is the latter. But here is a slightly different approach that avoids some of the things that could be problematic:

CREATE PROCEDURE dbo.pr_BuscaLog
  @qtdDias INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @cmd NVARCHAR(MAX) = N'', @dataLimite DATETIME = CONVERT(SMALLDATETIME, 
      DATEADD(DAY, @qtdDias, GETDATE()));

  SELECT name INTO #tmp FROM sys.databases WHERE database_id > 4;

  -- delete the ones that don't have that view    
  SELECT @cmd += N'DELETE #tmp WHERE name = N''' + name 
      + ''' AND NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name) 
      + '.sys.views WHERE name = N''vwLogProjetos'');' FROM #tmp

  EXEC sys.sp_executesql @cmd;
  SET @cmd = N'';

  SELECT @cmd += N'INSERT PainelControle..tblLogProjetos(...columns...)  
    select ...columns... FROM ' + QUOTENAME(name) 
      + N'.dbo.vwLogProjetos as T1 with (nolock)  
          where DtTrabalho >= @dt and not exists
          (
            select 1 from PainelControle.dbo.tblLogProjetos T2 
             Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS 
             = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS 
             and t2.codlog = t1.codlog
          );' FROM #tmp;

  BEGIN TRY
    EXEC sys.sp_executesql @cmd, N'@dt DATETIME', @dataLimite;
  END TRY
  BEGIN CATCH
    insert into dbo.tblLogProjetosErro (rotina, comando,Data) 
      values ('pr_BuscaLog', @cmd + ERROR_MESSAGE(), GETDATE());  
  END CATCH
END

Some things I've changed (or that you should):

  • No explicit loops. Contrary to popular belief, a while loop is not faster than a cursor (see this post), but neither is needed here.
  • Don't count when you don't have to - see this post.
  • I parameterized the date value instead of dealing with messy string concatenation (see this post and this post). This is harder to avoid with an entity, but should always be done with variables.
  • Please stop using lazy shorthand; e.g. use DATEDIFF(SECOND instead of DATEDIFF(s - see this post.
  • Use the schema prefix. Always.
Related Question