OLE DB 원본으로 뷰(View)를 사용할 때 주의할 점

OLE DB 원본으로 뷰(View)를 사용할 때 주의할 점

 

한대성

MS SQL Server MVP

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

 

 

 

참고 사이트) http://blogs.msdn.com/sqlperf/archive/2007/04/29/set-up-ole-db-source-to-read-from-view-efficiently.aspx

 

작년에 블로그에서 읽은 내용 중에 괜찮다 싶은 내용이라 즐겨찾기에 추가만 해 놓고선 잠시 잊었는데, 정리하다가 다시 읽어보니 괜찮은 내용이고 실제 겪었던 이슈라서 소개합니다.

 

SSIS의 데이터 흐름 작업에서 OLE DB 원본을 이용하여 뷰(View)의 데이터를 가지고 올 때 고려해야 할 사항입니다.

 

다음과 같은 형태의 뷰를 예로 들겠습니다.

USE AdventureWorks GO   CREATE VIEW TestView AS SELECT A.ProductNumber, A.MakeFlag, B.ReferenceOrderLineID FROM Production.Product A                   JOIN Production.TransactionHistory B ON A.ProductID = B.ProductID GO

 

이와 같은 뷰 에서 데이터를 가져올 때 일반적으로 다음과 같이 간단히 설정합니다.

 

 

 

원본 편집기에서 데이터 액세스 모드를 테이블 또는 뷰로 설정한 후 해당 뷰를 지정하게 됩니다.

 

그러나 다음과 같은 형태로도 사용할 수 있습니다.

 

결론부터 먼저 비교해 봅시다. (제 노트북의 테스트 결과)

 

횟수	테이블 또는 뷰로 설정		SQL 명령으로 설정
	Read	Duration	Read	Duration
1회	227,678	1,237	807	433
2회	227,678	1,342	807	429
3회	227,678	1,310	807	469
4회	227,678	1,285	807	414
5회	227,678	1,212	807	412
평균	227,678	1,287	807	431

 

테이블 또는 뷰로 설정한 것에 비해 SQL 명령으로 설정한 방식이 3배 정도 빠르며 Read의 수도 훨씬 적습니다. (읽어오는 데이터는 모두 113,443건으로 동일함)

 

왜 이런 차이가 발생할까요?

 

SSIS에서 데이터를 처리하는 단계와 SQL에서의 실행 계획 생성 및 재사용 특성이 이러한 성능 차이를 발생시키는 원인입니다.

 

데이터 원본에서 데이터를 읽어오는 과정은 다음과 같은 단계를 거칩니다.

 

패키지에서 지정한 대로 무조건 읽어오는 것이 아니라 설정한 열 정보가 맞는지를 확인한 후, 열 정보가 변경되었거나 문제가 있을 경우 데이터를 읽어오기 전에 에러를 발생시킵니다. 이러한 작업 단계를 유효성 검사(Validation Check)라 합니다.

 

 

 

테이블 또는 뷰로 지정했을 때와 SQL 명령으로 설정했을 때 열 정보를 가져오는 방식이 다릅니다.

 

ž   테이블 또는 뷰로 지정했을 때

 

ž   SQL 명령으로 지정했을 때

 

즉, 첫 번째 경우는 SET ROWCOUNT 1 명령을 수행한 후, 쿼리를 실행해서 열 정보를 읽어오는 방식이고, 두 번째 경우는 sp_prepare로 핸들(Handle)을 만들어서 열 정보를 읽어오는 방식입니다.(단순히 핸들만 만들고, sp_execute를 호출하지 않고 sp_unprepare를 실행 함.)

 

문제는 여기에 있습니다. 만약 테이블 또는 뷰로 설정한 후, 해당 쿼리에 대한 실행 계획이 없는 경우에는 SET ROWCOUNT 1이 설정된 상태에서 실행 계획이 만들어집니다. 이 실행 계획은 다음 단계인 데이터를 읽어오는 부분까지 영향을 미치게 됩니다. 확인해 봅시다.

 

쿼리 분석기에서 다음과 같이 쿼리를 실행 시킨 후, 실행 계획을 확인해 봅시다.

Hash Join으로 실행됩니다.

 

하지만 SSIS에서 실행한 후, 실행 계획을 확인해 보면 다음과 같습니다.

 

즉, SET ROWCOUNT 1로 설정한 상태에서 쿼리가 실행되어 만들어진 실행 계획이 전체 데이터를 가져올 때에도 그대로 이용되는 것입니다. 마치 쿼리 분석기에서 다음과 같이 실행한 것과 동일한 형태입니다.

SET ROWCOUNT 1 GO SELECT * FROM dbo.TestView GO   SET ROWCOUNT 0 GO SELECT * FROM dbo.TestView GO

 

이와 비교해 볼 때, SQL 명령을 이용하여 데이터를 가져오는 경우에는 실제 데이터를 가져오는 단계에서만 쿼리를 실행할 뿐 열 정보를 가져오는 단계에서는 쿼리가 실행되지 않기 때문에 잘못된(=적합하지 않은) 실행 계획을 사용할 위험은 없습니다.

  

 

정리하자면,

 

데이터 흐름 작업을 사용하여 데이터를 옮기거나 읽어오는 경우,

읽어올 데이터가 뷰(View)이며, OLE DB를 사용할 경우에는

테이블 또는 뷰로 설정한 후 직접 뷰를 지정하는 방식과 SQL 명령으로 설정한 후

쿼리로 가져오는 방식을 비교해 보시기 바랍니다.