SQL Server – How to Optimize Stored Procedure to Avoid ‘Out of Memory Exception’

optimizationsql serverstored-procedurestransaction

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

Best Answer

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
Related Question