SQL Server에서 100만 행 삭제

SQL Server에서 100만 행 삭제

나는 고객의 데이터베이스에서 작업 중인데 소프트웨어 버그로 인해 삭제해야 할 행이 약 100만 개 있습니다.다음 외에 이러한 항목을 효율적으로 삭제할 수 있는 방법이 있습니까?

DELETE FROM table_1 where condition1 = 'value' ?

위에서 제안한 바와 같이 일괄 삭제를 위한 구조입니다.한 번에 1M을 시도하지 마십시오...

배치의 크기와 지연 대기 시간은 분명히 매우 다양하며, 서버 기능과 경합을 완화해야 하는 필요성에 따라 달라집니다.일부 행을 수동으로 삭제하여 시간을 측정하고 배치 크기를 서버가 처리할 수 있는 수준으로 조정해야 할 수 있습니다.위에서 언급한 바와 같이, 5000이 넘으면 잠금이 발생할 수 있습니다(제가 미처 알지 못했습니다).

이 일은 근무 시간 이후에 하는 것이 최선일 것입니다.그러나 SQL이 처리하기에는 1M 행이 많지 않습니다.SSMS에서 메시지를 보는 경우 인쇄 출력이 표시되는 데 시간이 걸릴 수 있지만 몇 번의 배치 후에는 실시간으로 업데이트되지 않습니다.

편집: 중지 시간이 추가되었습니다.@MAXRUNTIME&@BSTOPATMAXTIME설정하는 경우@BSTOPATMAXTIME1까지, 스크립트는 원하는 시간에 자체적으로 중지됩니다(예: 8:00).이렇게 하면 밤 12시에 시작하도록 예약할 수 있으며 오전 8시에 생산되기 전에 중지됩니다.

편집: 답변이 꽤 인기가 많아서 추가했습니다.RAISERROR대신에PRINT평당

DECLARE @BATCHSIZE INT, @WAITFORVAL VARCHAR(8), @ITERATION INT, @TOTALROWS INT, @MAXRUNTIME VARCHAR(8), @BSTOPATMAXTIME BIT, @MSG VARCHAR(500)
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4000
SET @WAITFORVAL = '00:00:10'
SET @MAXRUNTIME = '08:00:00' -- 8AM
SET @BSTOPATMAXTIME = 1 -- ENFORCE 8AM STOP TIME
SET @ITERATION = 0 -- LEAVE THIS
SET @TOTALROWS = 0 -- LEAVE THIS

WHILE @BATCHSIZE>0
BEGIN
    -- IF @BSTOPATMAXTIME = 1, THEN WE'LL STOP THE WHOLE JOB AT A SET TIME...
    IF CONVERT(VARCHAR(8),GETDATE(),108) >= @MAXRUNTIME AND @BSTOPATMAXTIME=1
    BEGIN
        RETURN
    END

    DELETE TOP(@BATCHSIZE)
    FROM SOMETABLE
    WHERE 1=2

    SET @BATCHSIZE=@@ROWCOUNT
    SET @ITERATION=@ITERATION+1
    SET @TOTALROWS=@TOTALROWS+@BATCHSIZE
    SET @MSG = 'Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR)
    RAISERROR (@MSG, 0, 1) WITH NOWAIT
    WAITFOR DELAY @WAITFORVAL 
END

BEGIN TRANSACTION     
    DoAgain:
    DELETE TOP (1000)
    FROM <YourTable>
    IF @@ROWCOUNT > 0
    GOTO DoAgain
COMMIT TRANSACTION

어쩌면 우리 디만트의 이 해결책이

WHILE 1 = 1
BEGIN
   DELETE TOP(2000)
   FROM Foo
   WHERE <predicate>;
   IF @@ROWCOUNT < 2000 BREAK;
END

(링크: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b5225ca7-f16a-4b80-b64f-3576c6aa4d1f/how-to-quickly-delete-millions-of-rows?forum=transactsql)

제가 사용한 것은 다음과 같습니다.

1. 나쁜 데이터가 좋은 데이터와 섞이면,

   INSERT INTO #table 
      SELECT columns 
      FROM old_table 
      WHERE statement to exclude bad rows
   
   TRUNCATE old_table
   
   INSERT INTO old_table 
      SELECT columns FROM #table
   

이것이 얼마나 좋을지는 모르겠지만 아래와 같이 하면 어떨까요(제공).table_1독립 실행형 테이블입니다. 즉, 다른 테이블에서 참조하지 않습니다.)

1. 의 복제 테이블 생성table_1맘에 들다table_1_dup

2. insert into table_1_dup select * from table_1 where condition1 <> 'value';

3. drop table table_1

4. sp_rename table_1_dup table_1

복구하는 동안 데이터베이스를 운영 환경에서 사용할 수 없는 경우에는 소규모 일괄 작업을 수행합니다.참고 항목:500,000개 이상의 행 테이블에서 잘라내기 테이블을 사용하지 않는 동안 행을 효율적으로 삭제하는 방법

급하고 가능한 한 빠른 방법이 필요한 경우:

• 데이터베이스 운영 중단
• 모든 비관측 인덱스 및 트리거 삭제
• 레코드 삭제(또는 대부분의 레코드가 불량인 경우 복사+드롭+테이블 삭제)
• (해당되는 경우) 트리거를 삭제하여 발생한 불일치 수정
• 인덱스 및 트리거 다시 만들기
• 데이터베이스를 운영 환경으로 되돌립니다.

언급URL : https://stackoverflow.com/questions/24785439/deleting-1-millions-rows-in-sql-server