How to optimize stored procedure so it won't end with "Out of memory exception"

Question

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 '19 at 19:03
You could just do this as a single delete statement. Is there a reason why you don’t? — Philᵀᴹ, Oct 20 '19 at 19:05

McNets · Accepted Answer · 2019-10-20T19:37:31.507

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

edited Oct 20 '19 at 19:37

answered Oct 20 '19 at 19:06

McNets

23,749
10
48
88

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 '19 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 '19 at 19:42
@DanGuzman it is just an example – McNets Oct 20 '19 at 19:50

How to optimize stored procedure so it won't end with "Out of memory exception"

1 Answers1