데브 타임즈 : : BULK INSERT 
 category Development / MsSql 2008 . 10 . 08 16 : 42 by DevTimes oversky 
 이번 글 은 BULK INSERT 명령 을 이용 할 때 여러 경우 에 대한 성능 비교 결과 를 정리 한 것 입니다 . 
 다음 과 같 은 형태 의 10 , 000 , 000 건 의 텍스트 파일 을 Bulk Insert 명령 을 이용 하 여 테이블 로 로딩 할 때 옵션 에 따른 수행 시간 비교 입니다 . 각 경우 에 대해 3 회 반복 수행 을 한 후 , 평균 소요 시간 을 이용 하 여 비교 하 였 습니다 . 
 [ 테스트 파일 – TestFile . txt ] 
 Int 형 순번 열 , 100 자리 문자 열 : 10 , 000 , 000 건 
 [ 저장 테이블 - TESTTABLE ] 
 ※ DB File Size 증가 로 인한 지연 을 막 기 위해 DB Size 를 충분히 크 게 늘린 후 수행 함 . 
 TEST 1 ) 인덱스 가 없 는 테이블 에 로딩 
 -- 인덱스 가 없 는 테이블 에 로딩 
 BULK INSERT TESTTABLE FROM ' D : \ TestFile . txt ' 
 1 회 – 00 : 01 : 48 
 2 회 – 00 : 01 : 50 
 3 회 – 00 : 01 : 49 
 평균 – 00 : 01 : 49 ( 109 초 ) 
 TEST 2 ) 인덱스 가 없 는 테이블 에 로딩 + TABLOCK 옵션 적용 
 -- 인덱스 가 없 는 테이블 에 로딩 + TABLOCK 옵션 
 1 회 – 00 : 00 : 34 
 2 회 – 00 : 00 : 33 
 3 회 – 00 : 00 : 34 
 평균 – 00 : 00 : 34 ( 34 초 ) 
 TEST 3 ) 인덱스 가 없 는 테이블 에 로딩 + TABLOCK 옵션 적용 + 일괄 처리 크기 100 , 000 으로 설정 
 -- 인덱스 가 없 는 테이블 에 로딩 + TABLOCK 옵션 + RPB 100 , 000 
 1 회 – 00 : 00 : 33 
 평균 – 00 : 00 : 33 ( 33 초 ) 
 [ Clustered Index 생성 ] 
 -- Seq 컬럼 에 Clustered Index 생성 
 CREATE CLUSTERED INDEX CIX _ TESTTABLE ON TESTTABLE ( SEQ ) 
 TEST 4 ) Clustered Index 가 있 는 테이블 에 로딩 
 -- Clustered Index 가 있 는 테이블 에 로딩 
 1 회 – 00 : 03 : 40 
 2 회 – 00 : 03 : 39 
 3 회 – 00 : 03 : 39 
 평균 – 00 : 03 : 39 ( 219 초 ) 
 TEST 5 ) Clustered Index 가 있 는 테이블 에 로딩 + 일괄 처리 크기 100 , 000 
 -- Clustered Index 가 있 는 테이블 에 로딩 
 -- RPB 100 , 000 
 1 회 – 00 : 02 : 49 
 2 회 – 00 : 02 : 47 
 3 회 – 00 : 02 : 48 
 평균 – 00 : 02 : 48 ( 168 초 ) 
 TEST 6 ) Clustered Index 가 있 는 테이블 에 로딩 + ORDER 옵션 
 -- ORDER 옵션 
 ORDER ( SEQ ASC ) 
 1 회 – 00 : 02 : 21 
 2 회 – 00 : 02 : 21 
 3 회 – 00 : 02 : 20 
 평균 – 00 : 02 : 21 ( 141 초 ) 
 TEST 7 ) Clustered Index 가 있 는 테이블 에 로딩 + 일괄 처리 크기 100 , 000 + ORDER 옵션 
 -- RPB 100 , 000 + ORDER 옵션 
 1 회 – 00 : 02 : 20 
 3 회 – 00 : 02 : 21 
 TEST 8 ) Clustered Index 가 있 는 테이블 에 로딩 + ORDER 옵션 + TABLOCK 옵션 
 -- ORDER 옵션 + TABLOCK 옵션 
 ORDER ( SEQ ASC ) , 
 1 회 – 00 : 00 : 37 
 2 회 – 00 : 00 : 37 
 3 회 – 00 : 00 : 37 
 평균 – 00 : 00 : 37 ( 37 초 ) 
 TEST 9 ) Clustered Index 가 있 는 테이블 에 로딩 + ORDER 옵션 + TABLOCK 옵션 + 일괄 처리 크기 100 , 000 
 -- RPB 100 , 000 + ORDER 옵션 + TABLOCK 옵션 
 1 회 – 00 : 00 : 36 
 Clustered Index 가 없 는 힙 ( Heap ) 테이블 에 Bulk Insert 명령 을 이용 하 여 로딩 할 경우 , TABLOCK 의 옵션 설정 에 따라 3 배 정도 의 속도 차이 가 있 습니다 . 하지만 , 일괄 처리 크기 ( Rows Per Batch ) 설정 은 성능 에 별 영향 을 미치 지 않 습니다 . ( 34 초 : 33 초 ) 
 TABLOCK 옵션 은 Bulk Insert 작업 수행 시 해당 테이블 에 테이블 수준 의 잠금 을 설정 함 으로써 , 하위 수준 의 잠금 사용 시 발생 하 는 잠금 경합 ( Escalation ) 을 줄일 수 있 을 뿐 만 아니 라 잠금 을 설정 하 고 해제 하 는 단계 를 줄여 주 기 때문 에 처리 성능 이 크 게 향상 될 수 있 습니다 . ( 옵션 적용 전 – 109 초 , 옵션 적용 – 34 초 ) 하 지만 , 이 옵션 을 적용 하 여 로딩 할 경우 에 는 WITH ( NOLOCK ) 옵션 또는 WITH ( READUNCOMMITTED ) 옵션 을 사용 하 더라도 로딩 중 에 해당 테이블 의 데이터 를 읽 을 수 없 습니다 . 따라서 로딩 중 에 다른 부분 에서 사용 되 는 테이블 인 경우 에 는 이 옵션 을 적용 할 경우 블로킹 을 발생 시킬 수 있 기 때문 에 주의 해야 합니다 . 
 Clustered Index 가 설정 되 어 있 는 테이블 인 경우 에 는 Heap 테이블 과 는 달리 몇 가지 옵션 을 더 설정 할 수 있 습니다 . 
 우선 옵션 없이 단순히 Bulk Insert 를 수행 할 경우 , 입력 된 데이터 에 대해 인덱스 구성 작업 을 수행 해야 하 기 때문 에 힙 테이블 의 로딩 과 비교 했 을 때 훨씬 더 많 은 시간 이 소요 됩니다 . ( Heap – 109 초 , Clustered Index – 219 초 ) 
 일괄 처리 크기 ( Rows Per Batch ) 를 100 , 000 건 으로 설정 하 게 되 면 전체 건 ( 10 , 000 , 000 건 ) 의 인덱스 를 한 번 에 구성 하 는 대신 100 , 000 건 씩 나누 어서 구성 하 기 때문 에 처리 시간 이 단축 될 수 있 습니다 . ( 일괄 처리 – 219 초 , RPB 100 , 000 건 168 초 ) 
 만약 입력 되 는 데이터 가 적재 될 테이블 의 Clustered 열 과 동일 한 순서 로 정렬 된 데이터 인 경우 에 는 ORDER ( 열 이름 [ ASC | DESC ] ) 옵션 을 이용 하 여 처리 시간 을 단축 시킬 수 있 습니다 . 본 테스트 에서 는 텍스트 파일 의 첫 번 째 열 로 정렬 된 데이터 이 며 , 이 데이터 가 테이블 의 SEQ 열 로 입력 되 기 때문 에 ORDER 옵션 을 적용 할 경우 , 처리 시간 이 단축 됩니다 . ( 옵션 미 적용 – 219 초 , ORDER 옵션 적용 – 141 초 ) 
 또한 Heap 에서 와 마찬가지 로 TABLOCK 옵션 을 설정 하 여 처리 할 경우 , 처리 시간 이 크 게 단축 됩니다 . 10 , 000 , 000 건 의 텍스트 파일 에 대해 아무런 옵션 없이 Bulk Insert 작업 을 수행 한 경우 에 비해 ORDER , TABLOCK 옵션 을 이용 하 여 처리 한 수행 시간 이 6 배 정도 빠르 게 나타났 습니다 . ( 219 초 : 37 초 ) 
 참고 로 , 이러 한 사항 은 BULK INSERT 명령 뿐 만 아니 라 SSIS ( Integration Services ) 패키지 의 데이터 로딩 작업 에서 도 설정 할 수 있 는 사항 입니다 . 
 ◦ OLE DB 대상 에서 빠른 로드 설정 후 , 고급 OLE DB 대상 편집기 중 FastLoadOptions 부분 에서 설정 
 ◦ SQL Server 대상 의 고급 Server 대상 편집기 중 , BulkInsertOrder 부분 에서 설정 
 본 게시판 에 실린 글 은 누구 나 복사 하 셔서 이용 하 셔도 되 지만 , 반드시 출처 ( SQLLeader . com ) 및 글 의 링크 를 밝혀 주 셔야 합니다 . 
 MS SQL Text 검색 ( 0 )