[SQL] 500,000+ 행 테이블에 자르기 표를 사용하지 않는 동안 효율적으로 행을 삭제하는 방법

500,000+ 행 테이블에 자르기 표를 사용하지 않는 동안 효율적으로 행을 삭제하는 방법

하자 우리가 30 열과 50 열 테이블 판매를 말한다. 나는 테이블에 40 만 삭제하려면 (그 어디 "toDelete = '1'").

하지만 몇 가지 제약이있다 :

어떤 조언은 변환에 오신 것을 환영합니다 것

DELETE FROM Sales WHERE toDelete='1'

뭔가 더 분할 및 가능성 거래는 무료 로그인합니다.

해결법

1. ==============================

   1.TABLENAME에서 삭제를 호출하면 하나의 큰 트랜잭션에서 전체 삭제를 할 것입니다. 이 비싸다.

   TABLENAME에서 삭제를 호출하면 하나의 큰 트랜잭션에서 전체 삭제를 할 것입니다. 이 비싸다.

   다음은 일괄 행을 삭제합니다 또 다른 옵션은 다음과 같습니다

   deleteMore:
   DELETE TOP(10000) Sales WHERE toDelete='1'
   IF @@ROWCOUNT != 0
       goto deleteMore
   

2. ==============================

   2.당신이 원하는 것은 일괄 처리입니다.

   당신이 원하는 것은 일괄 처리입니다.

   While (select Count(*) from sales where toDelete =1) >0
   BEGIN
   Delete from sales where SalesID in
   (select top 1000 salesId from sales where toDelete = 1)
   END
   

   테이블에 따라 50000 - 물론 당신이 배치에 사용하는 최고의 가치 인 실험 할 수있다, 나는 500에서 사용했습니다. 당신이 계단식 삭제 사용하는 경우 삭제 그 자식 레코드를 가지고, 당신은 아마 작은 번호가 필요합니다.

3. ==============================

   3.나는 대량 삭제 및 업데이트에 대한 다른 접근 방법을 테스트 할 수 있었다 때문에 나는 여기에 내 대답을 떠날거야 (I 업데이트 한 후 125 개 + 미오 행을 삭제, 서버는 16GB의 RAM, 제온 E5-2680 @ 2.7GHz의, SQL Server는있다 2012).

   나는 대량 삭제 및 업데이트에 대한 다른 접근 방법을 테스트 할 수 있었다 때문에 나는 여기에 내 대답을 떠날거야 (I 업데이트 한 후 125 개 + 미오 행을 삭제, 서버는 16GB의 RAM, 제온 E5-2680 @ 2.7GHz의, SQL Server는있다 2012).

   TL; DR : 항상 결코 다른 조건에 의해, 기본 키에 의해 삭제 / 업데이트합니다. 직접 PK를 사용할 수없는 경우, 임시 테이블을 만들고 PK 값과 업데이트를 채우기 / 그 테이블을 사용하여 테이블을 삭제합니다. 이것에 대한 인덱스를 사용합니다.

   나는 (@Kevin Aenmey에 의해) 위의 솔루션을 시작했지만 내 데이터베이스를 방송했습니다과 초당 수백 거래의 몇 가지를 처리하고이 때부터이 방법이 부적절한 것으로 판명 일부 포함 차단 (모든 인덱스가 있었다 ) 아무것도 변경하지 않았다 WITH (ROWLOCK)를 사용하여, 조건에서이 필드.

   그래서, 나는 데이터베이스가 다른 트랜잭션을 처리 허용하는 WAITFOR 문을 추가했다.

   deleteMore:
   WAITFOR DELAY '00:00:01'
   DELETE TOP(1000) FROM MyTable WHERE Column1 = @Criteria1 AND Column2 = @Criteria2 AND Column3 = @Criteria3
   IF @@ROWCOUNT != 0
       goto deleteMore
   

   이 방법은 처리 할 수 ​​있었다 ~ 업데이트 및 대한 1.6mio 행 / 시간 ~ 삭제에 대한 0,2mio 행 / 시간.

   일을 임시 테이블에 꽤 많이 바뀌 켜기.

   deleteMore:
   SELECT TOP 10000 Id /* Id is the PK */
     INTO #Temp 
     FROM MyTable WHERE Column1 = @Criteria1 AND Column2 = @Criteria2 AND Column3 = @Criteria3 
   
   DELETE MT
     FROM MyTable MT
     JOIN #Temp T ON T.Id = MT.Id 
   
   /* you can use IN operator, it doesn't change anything
    DELETE FROM MyTable WHERE Id IN (SELECT Id FROM #Temp)
   
    */
   IF @@ROWCOUNT > 0 BEGIN
       DROP TABLE #Temp
       WAITFOR DELAY '00:00:01'
       goto deleteMore
   END ELSE BEGIN
       DROP TABLE #Temp
       PRINT 'This is the end, my friend'
   END
   

   이 솔루션은 (빠른 11 배)를 삭제하기위한 ~ 25mio 행 / 업데이트를위한 시간 (15 배 빠른)와 ~ 2.2mio 행 / 시간을 처리.

4. ==============================

   4.내가 과거에이 작업을 수행해야했다 방법 중 하나는 삭제 n 개의 기록이 저장 프로 시저 또는 스크립트를하는 것입니다. 완료 될 때까지 반복합니다.

   내가 과거에이 작업을 수행해야했다 방법 중 하나는 삭제 n 개의 기록이 저장 프로 시저 또는 스크립트를하는 것입니다. 완료 될 때까지 반복합니다.

   DELETE TOP 1000 FROM Sales WHERE toDelete='1'
   

5. ==============================

   5.당신은 전체 테이블을 잠그지 않도록 그것을 ROWLOCK 힌트를 제공하기 위해 시도해야합니다. 당신이 행을 많이 삭제하는 경우에는 에스컬레이션이 발생합니다 잠급니다.

   당신은 전체 테이블을 잠그지 않도록 그것을 ROWLOCK 힌트를 제공하기 위해 시도해야합니다. 당신이 행을 많이 삭제하는 경우에는 에스컬레이션이 발생합니다 잠급니다.

   또한, 반드시이 toDelete 열에 클러스터되지 않은 필터링 된 인덱스 (만 1 값)가합니다. 가능하게 그것을 조금 열이 아닌 VARCHAR (또는 무엇 지금)합니다.

   DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'
   

   궁극적으로, 테이블을 반복 시도하고 덩어리에서 삭제할 수 있습니다.

   업데이트

   루프 및 청크 삭제 여기에 새로운 분홍색을하는 동안, 내가 너무 내 버전에서 던질 것이기 때문에 (내 이전의 대답과 함께) :

   SET ROWCOUNT 100
   DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'
   
   WHILE @@rowcount > 0
   BEGIN
     SET ROWCOUNT 100
     DELETE FROM Sales WITH(ROWLOCK) WHERE toDelete='1'  
   END
   

6. ==============================

   6.다음과 같이이 기능에 대한 내 자신의 걸릴 것입니다. 이 방법은 어떤 코드가 반복되지 않으며, 당신은 당신의 청크 크기를 관리 할 수 ​​있습니다.

   다음과 같이이 기능에 대한 내 자신의 걸릴 것입니다. 이 방법은 어떤 코드가 반복되지 않으며, 당신은 당신의 청크 크기를 관리 할 수 ​​있습니다.

   DECLARE @DeleteChunk INT = 10000
   DECLARE @rowcount INT = 1
   
   WHILE @rowcount > 0
   BEGIN
   
     DELETE TOP (@DeleteChunk) FROM Sales WITH(ROWLOCK)
   
     SELECT @rowcount = @@RowCount
   END
   

7. ==============================

   7.나는 약 5000 만 기록을 삭제하려면 아래 사용했다 -

   나는 약 5000 만 기록을 삭제하려면 아래 사용했다 -

   BEGIN TRANSACTION     
        DeleteOperation:
        DELETE TOP (BatchSize)
        FROM  [database_name].[database_schema].[database_table] 
   
        IF @@ROWCOUNT > 0
        GOTO DeleteOperation
   COMMIT TRANSACTION
   

   BatchSize에서 <5000을 유지하는 자원에 대한 비용이 저렴 있습니다.

8. ==============================

   8.내가 기록의 엄청난 금액을 삭제하는 가장 좋은 방법을 가정으로 기본 키에 의해 삭제하는 것입니다. (기본 키는 무슨 볼 수있다)

   내가 기록의 엄청난 금액을 삭제하는 가장 좋은 방법을 가정으로 기본 키에 의해 삭제하는 것입니다. (기본 키는 무슨 볼 수있다)

   그래서 당신은 삭제할 라인의 전체 목록이 포함 TSQL 스크립트를 생성해야하고,이 후이 스크립트를 실행합니다.

   예제 코드는 아래 해당 파일을 생성 할 거입니다

   GO
   SET NOCOUNT ON
   
   SELECT   'DELETE FROM  DATA_ACTION WHERE ID = ' + CAST(ID AS VARCHAR(50)) + ';' + CHAR(13) + CHAR(10) + 'GO'
   FROM    DATA_ACTION
   WHERE  YEAR(AtTime) = 2014
   

   출력 파일과 같은 기록이 곧입니다

   DELETE FROM  DATA_ACTION WHERE ID = 123;
   GO
   DELETE FROM  DATA_ACTION WHERE ID = 124;
   GO
   DELETE FROM  DATA_ACTION WHERE ID = 125;
   GO
   

   그리고 지금 당신은이 스크립트를 실행하기 위해 SQLCMD 유틸리티를 사용합니다.

   sqlcmd -S [Instance Name] -E -d [Database] -i [Script]
   

   여기 explaned이 방법 https://www.mssqltips.com/sqlservertip/3566/deleting-historical-data-from-a-large-highly-concurrent-sql-server-database-table/을 찾을 수 있습니다

from https://stackoverflow.com/questions/11230225/how-to-efficiently-delete-rows-while-not-using-truncate-table-in-a-500-000-rows by cc-by-sa and MIT license