如何優化儲存過程，使其不會以“記憶體不足異常”結束

我創建了一個儲存過程，它檢查 2，然後從 3 中刪除。當我在 Management Studio 上執行儲存過程時，我得到了這個問題中提到的異常：StackExchange Question 但是我的問題是如何優化 SP 所以它不會花那麼長時間執行嗎？我的程式碼如下：

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

去

您可以在單個語句中執行此操作。

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

引用自：https://dba.stackexchange.com/questions/251543