[MSSQL] 특정 테이블에서 날짜 필드로 삭제하는 StoreProcedure

[TABLE_DATA] 테이블은 PK가 "seq" 이고, "CREATION_STDDATETIME" 이라는 날짜 필드가 존재한다. 

 

다음은 [TABLE_DATA] 테이블에서 14일 이전 데이터만 삭제하는 SP 이다. [TABLE_DATA] 테이블은 전체 100 만건 정도 가지고 있다.

CREATE PROCEDURE "dbo"."usp_deleteBatch"

AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRAN
            DELETE FROM TABLE_DATA
            WHERE CREATION_STDDATETIME < DATEADD(DAY, -14, GETDATE()) ;
        COMMIT TRAN
            SELECT 1
    END TRY

    BEGIN CATCH
        ROLLBACK TRAN;
        SELECT 0
    END CATCH
END

그런데, 해당 테이블의 데이터 건수가 많은 경우 처음 SP 가 실행될때 "CREATION_STDDATETIME" 가 인덱스가 안잡혀있기 때문에 수행될때 blocking 이 발생할 수 있다.

 

다음과 같이 PK 인 "seq" 를 이용한 SP 로 변경한다.

CREATE PROCEDURE "dbo"."usp_deleteBatch"
DECLARE @min_seq int
DECLARE @max_seq int
select @min_seq = MIN(seq) ,@max_seq = MAX(seq) From TABLE_DATA with (nolock) WHERE CREATION_STDDATETIME < DATEADD(DAY, -14, GETDATE()) ;

WHILE(@min_seq < @max_seq)
BEGIN
begin tran
DELETE FROM [TABLE_DATA] WHERE SEQ BETWEEN @min_seq AND @min_seq + 100
SET @min_seq = @min_seq + 100
WAITFOR DELAY '0:0:1'
commit tran
END