SSIS VS Linked Server의 성능비교

목적 : A서버의 DATA를 B서버로 데이터 이관시 최적의 방법을 알아보자.

  

SSIS는 복잡한 로직을 완성하는 프로그램이기보다는 데이터의 추출 및 변환을 담당하는 ETL 툴로써 Join, Sort, BulkInsert에

최적화 되어있다. 

단순히  하나의 서버에서 처리되는 SP 라면  대상서버에서 실행하는 것이 SSIS를 이용하는 것 보다 빠르다.

하지만 타서버에서 타서버로의 데이터의 추출 및 변환이라면 하기의 테스트 결과와 같이 상당한 차이가 발생한다.

 

각설하고  A서버의 데이터를 B서버로 Insert하는 방법중 가장 흔히 사용하는 Linked 서버 와 SSIS의 속도를 비교해 보았다.

 

먼저 A서버의 데이터가 약 70만건 일때 B서버로 이관시 SSIS와 Linekd 서버로 Test 한 결과는 하기와 같았다.(해당작업의 실행서버는 C서버)

 

결과를 놓고 보면 확실히 대상 테이블에 인덱스가 없는 Heap이 Insert가 빠름

단 인덱스 삭제 불가시 하기에 설명할 Order by 옵션으로 해결 가능

(BULK INSERT 대상 테이블에 인덱스가 있으면 입력되는 데이터를 인덱스의 키로 정렬하는 작업을 tempdb에서 하기 때문)

 

SSIS의 Internel상 데이터 흐름작업은 원본 서버의 메모리 버퍼에서 해결한다. (조회,변환,병합등등)

또한 파이프 라인기술을 통하여 모든 작업이 처리되지 않았어도 즉 1~10라는 최종결과가 도출되기전 1~3까지 결과가

나올시 바로 대상테이블의 버퍼로 이동시키며 다시 4~6이란 데이터를 퍼올린다.

 

하기는 SSIS에서 최대한 속도를 높히기 위한 대상테이블 옵션에 대한 Tip이다.

   

 

데이터 엑세스 모드 –  ‘빠른 로드’ 옵션을 사용시 내부적으로 BULK INSERT 구문을 사용
ID 유지 – 기본적으로 비활성화.  Identity 속성의 열이 있는 대상 테이블에 자동으로 identity 값이 생성된다. 만약 이 설정을 활성화 한다면, 데이터 흐름 엔진은 원본 데이터의 identity 값을 그대로 보존하여 대상 테이블에 입력하게 된다.

Null 유지 – 기본적으로 비활성화.  만약 이 설정이 활성화 되면, 데이터 원본의 NULL 값은 대상 테이블의 열에 기본값이 지정되어 있어도 그대로 NULL 값이 입력된다.

테이블 잠금 –기본적 활성화.  lock escalation을 방지하므로 성능에 도움이 됨.

CHECK 제약 조건 – 기본적으로 활성화. 입력되는 데이터 모두가 대상 테이블의 제약 조건을 모두 만족한다는 확신이 있다면 이 옵션을 해제하는 것이 성능에 큰 도움이 됨.

일괄 처리당 행 수– 기본값은 -1로, 입력되는 모든 행이 하나의 배치로  여러 배치로 나누어 데이터를 입력하고자 한다면 이 설정 값을 변경

최대 삽입 커밋 크기 – 기본 값은 '2147483647' (4 바이트 integer 형식의 최대값) 입력되는 모든 데이터가 성공적으로 입력되면 한번에  커밋 의미

                           커밋 작업을 나눔으로써 대량 데이터의 이동중에 발생할 수 있는 엄청난 트랜잭션 로그와 tempdb의 증가를 방지

                            (최적의 값은 테스트로 구해야 함)

 

참고로 A테이블의 ABC 컬럼에 Clustered 인덱스가 걸려있을시 B테이블로 Insert 시 대상테이블 고급옵션탭에 Order by (ABC ASC) 입력시 인덱스 순서대로 들어가므로 상당한 성능향상을 가져올 수 있음