0

I have created a stored procedure that check against 2 and then delete from the 3. When I execute the stored procedure on Management Studio I get the exception as mentioned in this question: StackExchange Question However what my question is how to optimize the SP so it will not take that long time to run? My code is as following:

 CREATE PROCEDURE [dbo].[Clean] ( 
 @Deletion date
  ) AS
   BEGIN

BEGIN TRANSACTION Cleaning
DECLARE @id int
Declare @ErrorCode int =1


 DECLARE cursorE CURSOR local fast_forward FOR
        select distinct m.ID
        from Member m
        left join (
            select *, row_number() over (PARTITION BY rid order by ceid 
desc) as rn
            from TypeA
        ) x on m.ID = x.ID and x.rn = 1
        where (
            (x.ceid is null and m.LastChangedDateTime < @Deletion) 
            or x.Resignation < @Deletion
        )                   

    OPEN cursorE
    FETCH NEXT FROM cursorE INTO @erID

     WHILE ( @@FETCH_STATUS = 0 )  
  DELETE FROM Errn WHERE erid = @id
  FETCH NEXT FROM cursorE INTO @rID
  COMMIT TRANSACTION Cleaning
  RETURN 1
  END         
 CLOSE cursorE
DEALLOCATE cursorE
ERRORHANDLER:
-- Rollback the transaction if there were any errors
 ROLLBACK TRANSACTION Cleaning
 RETURN @ErrorCode

GO

  • Try adding a BEGIN after the WHILE and an END after the FETCH. I expect your proc would then run faster by avoiding the infinite loop. Adding SET NOCOUNT ON might avoid the out of memory issue with SSMS. A better solution is a set-based delete query instead of cursor. – Dan Guzman Oct 20 at 19:03
  • You could just do this as a single delete statement. Is there a reason why you don’t? – Philᵀᴹ Oct 20 at 19:05
2

You can do it in a single statement.

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION;

DELETE FROM 
    Errn 
WHERE 
    erid IN (
             select 
                 distinct m.ID
             from 
                 Member m
             left join 
                 (select 
                      *, row_number() over (PARTITION BY rid order by ceid desc) as rn
                  from TypeA) x 
                 on m.ID = x.ID and x.rn = 1
             where 
                 (
                     (x.ceid is null and m.LastChangedDateTime < @Deletion) 
                     or 
                     x.Resignation < @Deletion
                 );

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    RAISERROR('ERR MSG', 16 1);

END CATCH
  • thanks for the answer. So what you suggest is to do the delete inside the transaction? and drop the While loop? I also need the error handler to check if the commit in case something goes wrong. do you have any suggestions? – Mindan Oct 20 at 19:22
  • 1
    @Mindan, you don't need an explict transaction with a single delete statement since it will run in an all-or-none autocommit transaction. No need for the try/catch block with a single statement either. – Dan Guzman Oct 20 at 19:42
  • @DanGuzman it is just an example – McNets Oct 20 at 19:50

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.