1 기본 개념

2 테스트 준비

3 Star Join 테스트

4 SQL Server의 Star Join

5 2005 vs 2008

6 SQL Server 2008 실제 실행 예제

7 SSIS 조회(Lookup)변환

8 참고문서

Star Join은 데이터 웨어하우스의 Start Schema에 쿼리할 때 자주 등장하는 용어이다. Star Join은 Join의 횟수를 줄이는 것이 목적이다. 차원과 팩트 테이블은 데이터의 양이 매우 큰 차이를 보인다. 차원은 몇 백건인데 비해 팩트는 몇 천만 건, 몇 억건은 기본이다. 그래서 여러 차원과 팩트가 계속해서 Join하게 되면 I/O의 비용보다 Join 비용이 더 커지게 된다. 즉, 차원1, 차원2, 차원3, 팩트 테이블의 상황이라면 일반적인 데이터 연결 기법으로는 다음과 같이 처리될 것이다.

1. 차원1과 팩트 조인
   
2. 1의 결과를 차원2와 조인
   
3. 2의 결과를 차원3와 조인
   
   

만약 팩트 테이블이 1억건이라면 조인의 횟수는 최대 3억 회가 된다. 또한 중간 결과 집합을 생성하여 다시 다음 단계의 입력으로 넘기는 작업도 만만치 않다. 그래서 차원을 Cross Join하여 나온 결과를 팩트 테이블과 Join을 시키면 결과적으로 조인의 횟수가 줄어 들게 되어 전체적인 쿼리 비용이 낮아지게 된다. Cross Join을 하면 데이터의 양이 상대적으로 많이 늘어나지만 팩트 테이블보다는 훨씬 작기 때문에 Cross Join의 부하는 무시[1]해도 된다. SQL Server에서의 Star Join은 Hash Join의 부분집합으로 보면 된다.

Start Join은 스노우플레이크 스키마와도 관련이 있다. 만약 DBMS가 Star Join을 지원하지 않는다면 스노우플레이크 스키마는 질의시 성능이 떨어지게 된다. 그러므로 스노우플레이크 스키마를 사용할 요량이면 반드시 구축에 선정된 DBMS의 Star Join 지원 여부를 확인해 보아야 한다. 개념적인 것을 직접 테스트 해보자.

소스를 다운로드하여 SSMS에서 실행한다.

일단 차원 테이블을 모두 Cross Join 한다.

select *
into #temp
from dbo.Dim_TestCode1
	cross join dbo.Dim_TestCode2
	cross join dbo.Dim_TestCode3
	cross join dbo.Dim_TestCode4
	cross join dbo.Dim_TestCode5

다음의 2개의 쿼리의 비용을 측정해 본다.

--1
select
	a.CodeNM1
,	a.CodeNM2
,	a.CodeNM3
,	a.CodeNM4
,	a.CodeNM5
,	sum(b.Cnt*1.0)
from #temp a inner join dbo.Fact_Test b
on a.CodeKey1 = b.CodeKey1
and a.CodeKey2 = b.CodeKey2
and a.CodeKey3 = b.CodeKey3
and a.CodeKey4 = b.CodeKey4
and a.CodeKey5 = b.CodeKey5
group by 
	a.CodeNM1
,	a.CodeNM2
,	a.CodeNM3
,	a.CodeNM4
,	a.CodeNM5
order by 1,2,3,4,5


--2
select
	b.CodeNM1
,	c.CodeNM2
,	d.CodeNM3
,	e.CodeNM4
,	f.CodeNM5
,	sum(a.Cnt*1.0)
from dbo.Fact_Test a 
	inner join dbo.Dim_TestCode1 b
		on a.CodeKey1 = b.CodeKey1
	inner join dbo.Dim_TestCode2 c
		on a.CodeKey2 = c.CodeKey2
	inner join dbo.Dim_TestCode3 d
		on a.CodeKey3 = d.CodeKey3
	inner join dbo.Dim_TestCode4 e
		on a.CodeKey4 = e.CodeKey4
	inner join dbo.Dim_TestCode5 f
		on a.CodeKey5 = f.CodeKey5
group by 
	b.CodeNM1
,	c.CodeNM2
,	d.CodeNM3
,	e.CodeNM4
,	f.CodeNM5
order by 1,2,3,4,5

실제 실행계획에서 보이듯이 같은 두 쿼리의 비용차이가 발생한다. 전체 처리시간은 더욱 많이 차이난다. 첫 번째 쿼리의 경우 약 5초의 처리시간이 소모되었으며 두 번째 쿼리는 약 20초가 소요되었다. 이 테스트는 2005버전에서 한 것이지만 기본 개념에 대한 테스트이므로 일반적인 Star Join의 개념이라고 생각하면 된다. SQL Server의 Star Join은 다음 단원에서 언급한다.

2005의 Star Join보다 2008의 Star Join은 더 개선되었다. SQL Server의 Star Join은 "Bitmap Filtering을 이용한 쿼리 계획의 최적화"이다. Bitmap Filtering의 개념은 다음과 같다.

Bitmap Filtering

Bitmap Index와 유사하지만 Bitmap Filter는 메모리 구조체이므로 DML 작업으로 인한 인덱스 유지관리의 오버헤드가 없다. 또한 Bitmap Filter는 아주 작으며 쿼리 처리 시간에 미치는 영향을 최소화하면서 동적으로 생성된다. 해시 또는 병합조인이 사용되는 병렬 쿼리 계획에만 적용된다. 그러므로 해시 또는 병합조인이 만들어 질 수 있는 조건에만 사용된다. (예를 들어 equal join 에만 사용될 수 있다.)

Optimized Bitmap Filtering

쿼리 계획에서 최적화 후 적용되거나 쿼리 계획 생성 중에 옵티마이저에 의해 동적으로 사용된 경우 ‘최적화된 비트맵 필터’라고 한다. 최적화된 비트맵 필터는 해시 조인이 사용되는 병렬쿼리 계획에서 적용된다.

2005버전에서는 Table Scan 연산자의 속성을 보면 팩트 테이블에서 반환되는 행을 제한하는데 사용되는 조건자가 없음을 알 수 있다.

2008의 최적화된 비트맵 연산자가 양쪽 차원 테이블의 하위트리에서 사용되었으며 Table Scan 연산자의 속성을 보면 이러한 하위 트리의 필터(비트맵 검색)가 팩트 테이블 트리에 직접 적용되어 첫 번째 조인 이전에 팩트 테이블에서 반환되는 행을 제한함을 알 수 있다.

최적화된 비트맵 필터 요구사항(도움말 내용)

• 팩트 테이블에는 최소한 100페이지가 있어야 합니다. 최적화 프로그램은 이보다 더 작은 테이블을 차원 테이블로 간주합니다.
  
• 팩트 테이블과 차원 테이블 간의 내부 조인만 고려됩니다.
  
• 팩트 테이블과 차원 테이블 간의 조인 조건자는 단일 열 조인이어야 하지만 기본 키-외래 키 관계일 필요는 없습니다. 정수 기반의 열이 선호됩니다.
  
• 차원을 사용한 조인은 차원 입력 카디널리티가 팩트 테이블의 입력 카디널리티보다 작을 때만 고려됩니다.
  
  

실제로 필자의 PC에서 테스트를 해보았다. 결과는 실망스럽다. 최적화된 Bitmap Filtering을 사용했음에도 불구하고 실제로 큰 차이를 보이지는 않았다. 오히려 Star Join의 개념대로 했을 경우에 전체 처리 속도가 더 향상되었음을 알 수 있다. 처리속도는 첫 번째 쿼리가 약 5초이고, 두 번째 쿼리는 약 20초였다. 2005에 비해 2008에서는 Star Join의 성능이 향상되었다고 한다. 실제 실행계획에서 표시되는 비용은 줄었지만 실제로는 별로 성능이 향상된거 같지는 않다. 씨발 뭐냐 ㅡㅡ; 좀 더 테스트를 해봐야 할 듯하다.

다음은 최적화된 비트맵 필터링을 사용한 것을 확인한 것이다.

비슷한 방법으로 SSIS의 조회변환이 있다. 처음 보았던 아래 그림에서 가운데의 팩트 테이블(Fact_Test)이 비였다고 가정해보자.

또한 아래와 같은 Flat파일(1천 만 건)을 가지고 있으며, 이 Flat 파일의 데이터를 팩트 테이블에 삽입해야 한다고 가정해보자.

팩트 테이블에 데이터를 넣기 위한 방법은 여러 가지지만 SQL을 이용하여 넣는다면 다음과 같은 SQL을 작성해야 할 것이다.

INSERT dbo.Fact_Test
SELECT 
	b.CodeKey1
,	c.CodeKey2
,	d.CodeKey3
,	e.CodeKey4
,	f.CodeKey5
,	[0]
FROM OPENROWSET(
		'MSDASQL'
	,	'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Documents and Settings\dwa2007\바탕화면;'
	,	'select  * from 천만건.txt') a inner join Dim_TestCode1 b
on a.코드= b.CodeNM1 inner join Dim_TestCode2 c
on a.코드= c.CodeNM2 inner join Dim_TestCode3 d
on a.코드= d.CodeNM3 inner join Dim_TestCode4 e
on a.코드= e.CodeNM4 inner join Dim_TestCode5 f
on a.코드= f.CodeNM5

다른 방법으로 SSIS 조회 변환을 이용하여 데이터를 삽입 할 수 있을 것이다.

두 방법을 비교한 성능 비교표가 아래에 있다.

로딩방식	시간	CPU사용률
일반SQL	로딩: 5분 40초, 커밋: 4분43초	약 30%
SSIS Lookup	로딩: 1분, 커밋: 4분 58초	약 40 ~ 50%

* 커밋시간은 아래의 그림처럼 관계를 유지하여 참조무결성 체크를 하기 때문입니다.
* 관계를 끊으면 커밋시간은 없을 것입니다.

빠르게 로딩했다고 꼭 SSIS를 이용하는 것이 좋다는 것은 아니다. 유지보수 비용과 새로운 툴에 대한 적응 위해서는 시간이 필요하기 때문에 상황에 따라서 개발 비용은 달라질 수 있다.

• Using Star Join and Few-Outer-Row Optimizations to Improve Data Warehousing Queries(http://msdn.microsoft.com/en-us/library/ee410012.aspx)
  
• SQL Server 2008 데이터웨어하우스 쿼리 성능(http://technet.microsoft.com/ko-kr/magazine/cc434693(TechNet.10).aspx)
  
• http://www.cs.duke.edu/courses/fall01/cps216/lectures/22-dw.pdf Data Warehousing