0

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.

  • You don't use the schema prefix for the sproc. Is there another a sproc with same name? You do use the same -2 as qtdDias when executing just the query as such? – vonPryz Jan 9 '15 at 12:25
  • Hello. No there's no other proc with the same name. i dont think thre is something with the code, because i can run it from the ssms...the problem is when i use EXEC PR_BUSCALOG. i cant think in something. – Racer SQL Jan 9 '15 at 15:40
  • 1
    In order to test the proc, comment out the exec sp_executeSQL @cmd, uncomment the Select @cmd that is right below it, change the Select @cmd to be PRINT @cmd;, and add a PRINT '------------'; just after the PRINT @cmd; (to provide better separation of the output so it is easier to read). The issue is probably dealing with the value of @dataLimite, which is controlled by the input param. Also, @banco should be declared as sysname instead of varchar(100). And, using a cursor for the database name query would be better than a custom loop. – Solomon Rutzky Jan 9 '15 at 17:19
  • srutzky, Thank you very much...this is what i was trying to do to analize the results, but, since i'm DBA JR, it was kicking my head ( im not good at programming yet haha ). I will test this, and ask for help about the cursor. Thanks – Racer SQL Jan 9 '15 at 17:46
4

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.
  • Thanks for the answer ! i'm trying to help the programmer with this query But thise are some awesome advices because i'm learning about it. and it didn't work. it shows that "binary or string would be truncated". i managed to fix it but its still running in loop ( or something ). I will try to see how the Execution Plan works ( with exec and runnning the query itself ) – Racer SQL Jan 9 '15 at 18:30
0

The select statement Select count(1) from #tmp in the while loop is effectively a constant value. In other words, it doesn't change each time round the loop.

I'm guessing you really want to loop once per database, so you'll need to modify your while loop.

  • Hey , i will try this. But, When i run the query by itself i found no problems. The issue is, when i run it by "exec pr_buscalog". this is driving me crazy. – Racer SQL Jan 9 '15 at 12:07
  • 2
    @RafaelPiccinelli and ColdMachine: while the COUNT(1) is not as good as EXISTS(SELECT 1..., it is not the problem here and is not a "constant value" as you describe. It does give the rowcount in the table and will allow the WHILE to end when all rows have been removed. – Solomon Rutzky Jan 9 '15 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.