SQL 쿼리와 SSIS 누가 더 빠른가

SQL 쿼리와 SSIS 누가 더 빠른가?

 

 

한대성

MS SQL Server MVP

에이디컨설팅 책임 컨설턴트 | SQLLeader.com 운영자

 

Question

A01 A02 A03 .... A053 으로 되어있는 테이블을 A 01 VAl 값 A 02 VAL 값 .. A53 VAL 값 이런식으로 언피벗을 하려는데요. 데이터가 10개 이면 * 53으로 ROW가 생기는 상황입니다. 실제 데이터 건수는 500만건 정도 되니.. ROW가 굉장히 많이 생깁니다. 언피벗을 다시 가공해서 테이블에 적재해야하는데 언피벗을 할때 SSIS의 피벗해제와 SP에서 T-SQL의 UNPIVOT을 사용하는 것의 성능 차이가 얼마나 있는지 궁금하네요.

우선 SSIS 가 빠른지 DB 처리가 빠른지를 논(?)하기 전에 다음과 같은 사항이 먼저 고려됩니다.

   "동일한 인스턴스에서 처리하는 SQL 데이터이냐? 아니냐?"

표현이 좀 어색하긴 하지만, 다음과 같이 설명할 수 있습니다.

(이건 순전히 저의 주관적 & 경험적인 의견이긴 합니다만..)
만약, 수행하려는 작업이 DB 작업, 좀 더 정확히는 SQL DB의 데이터를 이용하는 작업이고, 대상 테이블 또한 동일한 서버에 있는 데이터베이스의 테이블인 경우에는, 가급적 SSIS 말고 쿼리로 처리하라는 것입니다.

간단한 예로, ADCServer라는 인스턴스에 DatabaseA, DatabaseB 라는 데이터베이스가 있고,
DatabaseA에 있는 테이블을 읽어서 정렬 및 집계, 변환을 한 다음, DatabaseB 테이블에 넣고자 한다면,

SSIS 패키지로 작업할 경우, 데이터 흐름 작업 내에 정렬, 집계, 열 추가와 같은 변환 작업이 있기 때문에 이를 이용하면 되겠네~라고 생각하고 데이터 흐름 작업으로 구성할 수도 있습니다.
구현은 잘 되고, 뽀다구도 나겠지만, 성능은 안좋습니다. DB에서 바로 처리될 수 있는 것을 다른 프로그램(=패키지)에서 읽어서 변환한 다음 다시 넣어주는 형태이기 때문입니다.


그럼 데이터 흐름을 쓰지 말아야 하는 것이냐~~.
앞에서도 말했듯이, 데이터 흐름 보다 저장 프로시저 또는 쿼리를 이용한 처리가 좋은 경우는 동일한 인스턴스에 있는 테이블 처리인 경우에 한합니다.
만 약 텍스트 파일이나 다른 이기종 데이터베이스에서 읽어와서 이를 처리하는 경우는 또 상황이 다르며, 같은 SQL Server이더라도 원격지에 있는 경우에도 다를 수 있습니다. 또한 멀티캐스트, 조건부 분할, 행 갯수, 오류 열 리디렉션 등과 같이 쿼리로는 구현 못하는 기능들을 이용하기 위해 데이터 흐름을 이용할 수도 있습니다.


잠깐 주제를 벗어나서..
어떤 경우에는 패키지라고 만든 후에 보면 전부 SQL 실행 작업들로만 구성되어 있는 경우가 종종 있습니다. 이걸 보고 패키지 참 못만들었다~(^^)라고 할 수는 없습니다. 모두 SQL 실행 작업들로만 구성되었어도 여러 잇점들이 많이 있습니다. 한 번 생각나는데로 주절대 보겠습니다.

1. 쿼리를 병렬 처리 할 수 있습니다.
       
 SSIS 패키지로 구성한다면 위와 같이 동시에 쿼리 2와 쿼리 3이 돌아가도록 구성할 수 있습니다. 저장 프로시저로 만든다면 순차적인 실행 밖에 안되겠지요.

2. 로깅 및 모니터링, 에러 핸들링을 이용한 관리도 용이합니다.
저 장 프로시저도 역시 몇 번째 줄에서 에러가 났다라고 메시지를 뿌리긴 하지만, SSIS의 로깅 기능을 이용한다면 훨씬 더 관리하기 용이합니다. 또한 에러 핸들링 기능을 이용해서 에러가 나면 어떤 작업(예를 들어 관리자한테 메일 보내라)들도 쉽게 구현 가능하지요.

3. 원격 서버에서 수행해도 성능에 큰 저하가 없다.
만약 저장 프로시저로 처리한다면, 해당 서버에 저장 프로시저를 만들어 놓고선 해당 서버에서 돌려야 합니다. 물론 Agent를 이용해서 원격지에서 이를 수행하도록 구현할 수는 있으나, 복잡합니다. 또한 원격지에서 RPC(Remote Procedure Call)을 이용해서도 호출할 수 있지만 썩 추천하지는 않지요.
또한 까다로운 서버 관리자가 절때 프로시저 못 만들게 한다면? 갑갑하지요...
SSIS 패키지로 만든다면 원격지에서 패키지를 수행하더라도, 로컬 DB에서 수행하는 방식대로 실행할 수 있습니다. 뭔 말이냐면..간단히 프로그램을 생각해 보면 웹 서버는 DB 서버와 다른 곳에 있더라도 프로그램에서 DB 연결을 맺고선 쿼리 등을 실행하듯이 SSIS 패키지도 패키지 내부에서 명령을 수행할 DB에 대해 연결을 맺고선 쿼리를 날리는 방식이라는 것이지요. 성능? 좋습니다.

4. 저장 프로시저보다 뽀다구(=폼, 모양새) 납니다.
Visual Studio를 경멸하는 분들도 있고, Window의 아이콘보다 Shell 환경의 검은 바탕의 흰 색 글자가 더 뽀다구 난다고 생각하는 고수(^^)분들도 계시겠지만, 쿼리로만 쫙 있는 것 보다 시각적으로 구성된 패키지가 더 보기좋은 경우도 있습니다..


쓰다보니 주제가 다른 곳으로 빠졌네요. 이제 질문에 답변 드리겠습니다.
SSIS 피벗 기능과 쿼리의 피벗 기능 중 누가 더 빠르냐면, 그때 그때 다릅니다.(ㅡ,.ㅡ)
우선 데이터 형태가 많은 영향을 미칠 것이고, 처리되는 빈도도 영향을 주겠지요.

그러면 동일한 데이터베이스가 아니고 다른데 있는 데이터인 경우라면 SSIS가 더 빠른가요? 그것도 상황따라 다릅니다.(욕 나오시죠?)
앞에서 원격 서버인 경우, SSIS가 더 낫다는 듯이 말하더니만 왠 소리냐.
원 격 데이터를 처리한다면 SSIS가 쿼리보다 더 나을 수는 있습니다. 그러나, 만약 원격 데이터 전체를 Bulk Loading으로 로컬 임시 테이블로 가져온 다음, 로컬에서 쿼리로 처리하는 것이 더 빠른 경우도 있다는 것이지요. 요놈은 SSIS와 쿼리를 둘 다 섞어서 쓰는 형태이겠지요?

그래서 제가 말씀드릴 답변은, 절대적으로 빠르고 느린 것은 말씀드릴 수 없고, 상황에 따라 적절한 방법을 찾는 것이 답이다 입니다.


월요일 아침부터 해괴한 글로인해 열 받게 해 드린 것은 아닌가 모르겠습니다만, 제가 패키지를 만들 때 고려하는 중요한 사항이고 격있는 말로는 편하게 쓸 수 없기에 이렇게 자유롭게 써봤습니다.

그럼..^^