[DB] MSSQL DELETE TOP문 사용하여 대용량 데이터 삭제하기

데이터베이스를 대용량으로 삭제하고 싶은경우가 있습니다. 대량의 데이터를 삭제할경우에는 아무래도 시스템이 멈추는 증상이 발생합니다. 해당부분을 최소화하기 위해서 DELETE문에 TOP문을 사용해서 최소화 하는 방법에 대해서 알아보겠습니다.

1. LogTest 테스트용 테이블 생성

CREATE TABLE [dbo].[LogTest](
    [id] [int] NOT NULL,
    [logDateTime] [datetime] NULL,
 CONSTRAINT [PK_LogTest] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

2. While문을 이용한 테스트 데이터 생성
테스트를 위해서 데이터 10만건을 넣은후에 테스트 하겠습니다.

declare @loopCnt int 
declare @logDateTime datetime
 
set @loopCnt = 1
set @logDateTime = '2001-01-01'
 
while (@loopCnt < 100000)
begin
 
    INSERT INTO [dbo].[LogTest] ([id],[logDateTime]) VALUES (@loopCnt,@logDateTime)
    set @loopCnt = @loopCnt + 1
    set @logDateTime = DATEADD(second,1,@logDateTime)
 
end 

3. DELTE TOP문을 이용한삭제
일괄로 삭제시 시간이 오류걸리므로 TOP문을 사용해서 데이터를 삭제합니다. 이때 Loop문을 사용하여 DELETE문사이에 적절한 Delay를 주어 시스템 멈추는 부분을 최소화 하면됩니다. 삭제문의 경우는 복구가 어려우므로 충분한 테스트를 하신후에 사용하시기 바랍니다. @@rowcount는 전역변수이므로 delete top문을 loop문 마지막에 위치하게 하였습니다.

delete top(1000)    from LogTest where logdatetime < '2001-01-01 10:00'
 
while (@@rowcount > 0)
BEGIN
 
    --delay 100m/s
    waitfor delay '0:0:0:100'
 
    delete top (1000)  from LogTest where logdatetime < '2001-01-01 10:00'
 
END