복붙노트

[SQL] 가장 빠른 방법은 120 만 개 기록을 업데이트

SQL

가장 빠른 방법은 120 만 개 기록을 업데이트

나는 값으로 새 필드를 초기화 -1 120 만 기록 테이블에 필요합니다.

Update table
       set int_field = -1;

나는 그것을 취소하기 전에 5 시간 동안 실행하자.

저도 같은 결과에 커밋되지 않은 읽기 트랜잭션 수준 세트를 실행 해 보았다.

Recovery Model = Simple.
MS SQL Server 2005

이 빨리 수행하기에 어떤 조언을?

해결법

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

    1.120M 기록의 테이블을 업데이트 할 수있는 유일한 제정신 방법은 두 번째 테이블을 채우는 SELECT 문입니다. 이 작업을 수행 할 때 조심해야합니다. 지침 아래.

    120M 기록의 테이블을 업데이트 할 수있는 유일한 제정신 방법은 두 번째 테이블을 채우는 SELECT 문입니다. 이 작업을 수행 할 때 조심해야합니다. 지침 아래.

    간단한 케이스

    동시 DML이없는 기간 동안 클러스터 된 인덱스, 출력 / w 테이블의 경우 :

    당신이 복제 스키마를 작성할 수없는 경우, 같은 스키마에서 다른 테이블 이름은 할 것입니다. 스위치 후에 모든 제약 조건과 트리거를 (해당되는 경우)의 이름을 변경해야합니다.

    단순하지 않는 가정

    첫째는, 예를 들어 clone.BaseTable 다른 스키마에서 같은 이름으로 BaseTable을 다시 작성하십시오. 별도의 스키마를 사용하면 나중에 이름 변경 프로세스를 단순화합니다.

    그런 다음, / 1000 행 승 당신의 삽입을 테스트 :

    -- assuming an IDENTITY column in BaseTable
    SET IDENTITY_INSERT clone.BaseTable ON
    GO
    INSERT clone.BaseTable WITH (TABLOCK) (Col1, Col2, Col3)
    SELECT TOP 1000 Col1, Col2, Col3 = -1
    FROM dbo.BaseTable
    GO
    SET IDENTITY_INSERT clone.BaseTable OFF
    

    결과를 검사합니다. 모든 순서대로 나타나는 경우 :

    이것은하지만 거의 한 갱신 사항으로, 시간이 걸릴 것입니다. 이 완료되면, 반드시 그 모든 것이 정확한지 확인하기 위해 복제 테이블의 데이터를 확인합니다.

    그런 다음, 모든 클러스터되지 않은 기본 키 / 고유 제한 조건 / 인덱스와 (순서대로) 외래 키 제약 조건을 다시 작성하십시오. 재생성 기본 체크 제약 조건 (해당되는 경우). 모든 트리거를 다시 만듭니다. 별도의 배치에서 각 제약 조건, 인덱스 또는 트리거를 다시 만듭니다. 예를 들면 :

    ALTER TABLE clone.BaseTable ADD CONSTRAINT UQ_BaseTable UNIQUE (Col2)
    GO
    -- next constraint/index/trigger definition here
    

    마지막으로, DBO 스키마에 백업 스키마와 clone.BaseTable에 dbo.BaseTable 이동 (또는 어디서든 테이블을 라이브로 가정한다).

    -- -- perform first true-up operation here, if necessary
    -- EXEC clone.BaseTable_TrueUp
    -- GO
    -- -- create a backup schema, if necessary
    -- CREATE SCHEMA backup_20100914
    -- GO
    BEGIN TRY
      BEGIN TRANSACTION
      ALTER SCHEMA backup_20100914 TRANSFER dbo.BaseTable
      -- -- perform second true-up operation here, if necessary
      -- EXEC clone.BaseTable_TrueUp
      ALTER SCHEMA dbo TRANSFER clone.BaseTable
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
      SELECT ERROR_MESSAGE() -- add more info here if necessary
      ROLLBACK TRANSACTION
    END CATCH
    GO
    

    당신은 무료로 최대 디스크 공간이 필요한 경우는 잠시 더 이상 주위를 유지하는 것이 현명있을 수 있지만, 당신은이 시간에 원래의 테이블을 삭제 할 수 있습니다.

    말할 필요도없이,이 이상적으로 오프라인 작업입니다. 당신은이 작업을 수행하는 동안 사람들이 데이터를 수정하는 경우, 당신은 스키마 스위치 진정한 업 작업을 수행해야합니다. 나는 별도의 테이블에 대한 모든 DML을 기록 dbo.BaseTable에 트리거를 만드는 것이 좋습니다. 당신은 삽입을 시작하기 전에이 트리거를 사용합니다. 그런 다음 스키마 전송을 수행하는 것과 같은 트랜잭션에서, 진정한 업을 수행하기 위해 로그 테이블을 사용합니다. 데이터의 하위 집합에 먼저 테스트! 델타는 망치 쉽습니다.

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

    2.당신이 디스크 공간이있는 경우 SELECT INTO를 사용하여 새 테이블을 만들 수 있습니다. 훨씬 빨리 갈 것이다, 그래서 그것은 최소한, 기록이야

    당신이 디스크 공간이있는 경우 SELECT INTO를 사용하여 새 테이블을 만들 수 있습니다. 훨씬 빨리 갈 것이다, 그래서 그것은 최소한, 기록이야

    select t.*, int_field = CAST(-1 as int)
    into mytable_new 
    from mytable t
    
    -- create your indexes and constraints
    
    GO
    
    exec sp_rename mytable, mytable_old
    exec sp_rename mytable_new, mytable
    
    drop table mytable_old
    
  3. ==============================

    3.나는 작은 단위로 작업을 중단. 그 수행 최적의 간격을 찾기까지 테이블에 대한 다른 배치 크기 간격으로 테스트합니다. 여기에 내가 과거에 사용했다고 샘플입니다.

    나는 작은 단위로 작업을 중단. 그 수행 최적의 간격을 찾기까지 테이블에 대한 다른 배치 크기 간격으로 테스트합니다. 여기에 내가 과거에 사용했다고 샘플입니다.

    declare @counter int 
    declare @numOfRecords int
    declare @batchsize int
    
    set @numOfRecords = (SELECT COUNT(*) AS NumberOfRecords FROM <TABLE> with(nolock))
    set @counter = 0 
    set @batchsize = 2500
    
    set rowcount @batchsize
    while @counter < (@numOfRecords/@batchsize) +1
    begin 
    set @counter = @counter + 1 
    Update table set int_field = -1 where int_field <> -1;
    end 
    set rowcount 0
    
  4. ==============================

    4.당신의 int_field 색인 경우, 업데이트를 실행하기 전에 인덱스를 제거합니다. 그런 다음 다시 색인을 만들 ...

    당신의 int_field 색인 경우, 업데이트를 실행하기 전에 인덱스를 제거합니다. 그런 다음 다시 색인을 만들 ...

    5시간는 1 억 2 천만 RECS에 대한 많은 것 같다.

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

    5.

    set rowcount 1000000
    Update table set int_field = -1 where int_field<>-1
    

    필요에 따라 조정하고 반복 소요 얼마나 빨리 참조

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

    6.

    declare @cnt bigint
    set @cnt = 1
    
    while @cnt*100<10000000 
     begin
    
    UPDATE top(100) [Imp].[dbo].[tablename]
       SET [col1] = xxxx 
     WHERE[col1] is null  
    
      print '@cnt: '+convert(varchar,@cnt)
      set @cnt=@cnt+1
      end
    
  7. ==============================

    7.내가 먼저 시도를하려는 것은 업데이트하기 전에 먼저 모든 제약 조건, 인덱스, 트리거 및 전체 텍스트 인덱스를 삭제합니다.

    내가 먼저 시도를하려는 것은 업데이트하기 전에 먼저 모든 제약 조건, 인덱스, 트리거 및 전체 텍스트 인덱스를 삭제합니다.

    위 확대됨에 충분하지 않으면, 내 다음 행동은 것 1200 만 개 기록하고 bcp를 사용하여 대량 가져 오기와 CSV 파일을 만들 수 있습니다.

    마지막으로, 나는 다른 파일 그룹에없는 인덱스 (기본 키와 테이블을 의미) 새로운 힙 테이블을 만들 것, 그것을 채울 -1. 이전 테이블을 분할하고, "스위치"를 사용하여 새 파티션을 추가합니다.

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

    8.새 열 ( "새 필드를 초기화")을 추가하고 기존의 각 행에 단일 값을 설정할 때, 나는 전술 다음을 사용 :

    새 열 ( "새 필드를 초기화")을 추가하고 기존의 각 행에 단일 값을 설정할 때, 나는 전술 다음을 사용 :

    ALTER TABLE MyTable
     add NewColumn  int  not null
      constraint MyTable_TemporaryDefault
       default -1
    
    ALTER TABLE MyTable
     drop constraint MyTable_TemporaryDefault
    

    열이 Null을 허용하고 당신이 "선언"제약 조건을 포함하지 않는 경우, 열은 모든 행에 null로 설정됩니다.

  9. ==============================

    9.Pabla 산타 크루즈는 언급과 같은 소리는 인덱싱 문제를 좋아한다. 당신의 업데이트가 조건이 아니므로, 당신은 열을 삭제하고 디폴트 값으로 다시는 추가합니다.

    Pabla 산타 크루즈는 언급과 같은 소리는 인덱싱 문제를 좋아한다. 당신의 업데이트가 조건이 아니므로, 당신은 열을 삭제하고 디폴트 값으로 다시는 추가합니다.

  10. ==============================

    10.일반적으로, 권장 사항은 다음과 같습니다

    일반적으로, 권장 사항은 다음과 같습니다

    그러나 특별한 경우에 당신은 가장 적합한 솔루션 또는 조합을 선택해야합니다.

    또한 언젠가 지수가 유용 예를 들어이 될 수 있음을 명심 때 어떤 조건에 의해 비 인덱스 컬럼의 업데이트를 수행.

  11. ==============================

    11.테이블은 내가 업데이트 탑 루프를 통해 이동하면서 데이터 (10000) 문을 둘 것 이상 반복 할 수있는 인덱스가있는 경우. 즉 슬림 트랜잭션 로그를 유지하는 것입니다 및 디스크 시스템에서 이러한 큰 영향이 없습니다. 또한, 나는 (가까이 1로 설정) MAXDOP 옵션을 재생하는 것이 좋습니다.

    테이블은 내가 업데이트 탑 루프를 통해 이동하면서 데이터 (10000) 문을 둘 것 이상 반복 할 수있는 인덱스가있는 경우. 즉 슬림 트랜잭션 로그를 유지하는 것입니다 및 디스크 시스템에서 이러한 큰 영향이 없습니다. 또한, 나는 (가까이 1로 설정) MAXDOP 옵션을 재생하는 것이 좋습니다.

  12. from https://stackoverflow.com/questions/3711217/fastest-way-to-update-120-million-records by cc-by-sa and MIT license