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

가장 빠른 방법은 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 옵션을 재생하는 것이 좋습니다.

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