UNPIVOT 관련한 테스트 및 고민꺼리

파라미터로 넘어온 '100,122,1992,23848,5637373,8371712,1,2,3,4,5,6,7,8,9,0,121,1222,12412,42354,6574' 다음과 같은 값을

다른 테이블과 조인하기 위해서 unpivot 해야 하는 부분을 처리하기 위한 Tip 입니다.

관련 게시물은 아래 참고하세요~ (아래 쿼리의 데이터 생성 부분은 아래 링크에서 ^^)

http://sqler.pe.kr/web_board/view_list.asp?id=1094&read=21112&pagec=&gotopage=&block=&part=myboard7

 

MSSQL 2005에서의 cross apply를 이용하는 방법과 기존 MSSQL 2000에서도 사용 가능한 방법에 대한

비교를 통해서 어떤 차이가 있는지 알아 봤습니다.

 

[CROSS APPLY]

declare @parameter varchar(8000) set @parameter = '100,122,1992,23848,5637373,8371712,1,2,3,4,5,6,7,8,9,0,121,1222,12412,42354,6574'   select substring(@parameter, num, charindex(',', @parameter + ',', num) - num) as 'split' from nums with(nolock) where charindex(',', ',' + @parameter, num) = num   (21개행적용됨) 테이블'NUMS'. 검색수1, 논리적읽기수15, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.   SQL Server 실행시간:    CPU 시간= 10ms, 경과시간= 8ms.

 

[일반쿼리]

declare @parameter varchar(8000) set @parameter = '100,122,1992,23848,5637373,8371712,1,2,3,4,5,6,7,8,9,0,121,1222,12412,42354,6574'   select substring(max(@parameter + ','), min(num), charindex(',', @parameter + ',', num) - min(num)) as 'split' from nums with(nolock) where len(@parameter) >= num group by charindex(',', @parameter + ',', num)   (21개행적용됨) 테이블'Worktable'. 검색수0, 논리적읽기수0, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0. 테이블'NUMS'. 검색수1, 논리적읽기수3, 물리적읽기수0, 미리읽기수0, LOB 논리적읽기수0, LOB 물리적읽기수0, LOB 미리읽기수0.   SQL Server 실행시간:    CPU 시간= 0ms, 경과시간= 2ms.

 

위의 값만 딱하고 보면은 cross apply를 사용하는 것 보다는 기존의 쿼리 방식이 더 좋아 보입니다.

뭐 경과시간 및 논리 I/O만 확인하면 그렇다는 거죠...

근데 한가지 걸리는 것이 worktable이 일반 쿼리에서는 발생 했다는 거죠...

물론 이 부분에 대한 부분이 물리적인 I/O나 기타 부하를 유발하는지 성능 카운터를 확인 했으나...

튀는 부분은 아래에서와 같이 worktable create 밖에 없었습니다.

임의로 checkpoint를 발생해도 내려가는 페이지가 없고 페이지 읽기 쓰기 모두 없습니다.

Log Flush도 없네요...

 

 

딱 여기까지만 보면 그럼 기존 쿼리가 성능상으로 보면 cross apply를 이용하는 것 보다 더 좋다라는 생각이 드네요~

혹시나 싶어서 다른 내용도 확인해 봤습니다. (DBCC TRACEON(3604), DBCC TRACEON(1200, -1) 이용)

 

[CROSS APPLY]

declare @parameter varchar(8000) set @parameter = '100,122,1992,23848,5637373,8371712,1,2,3,4,5,6,7,8,9,0,121,1222,12412,42354,6574'   select substring(@parameter, num, charindex(',', @parameter + ',', num) - num) as 'split' from nums with(nolock) where charindex(',', ',' + @parameter, num) = num   Process 52 acquiring Sch-S lock on OBJECT: 6:2121058592:0  (class bit0 ref1) result: OK   (21개행적용됨) Process 52 releasing lock on OBJECT: 6:2121058592:0

 

[일반쿼리]

declare @parameter varchar(8000) set @parameter = '100,122,1992,23848,5637373,8371712,1,2,3,4,5,6,7,8,9,0,121,1222,12412,42354,6574'   select substring(max(@parameter + ','), min(num), charindex(',', @parameter + ',', num) - min(num)) from nums with(nolock) where len(@parameter) >= num group by charindex(',', @parameter + ',', num)   Process 52 acquiring S lock on DATABASE: 6 [PLANGUIDE] (class bit0 ref1) result: OK   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 SCHEMA(schema_id = 4) (class bit0 ref1) result: OK   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 USER_TYPE(user_type_id = 167) (class bit0 ref1) result: OK   Process 52 releasing lock on METADATA: database_id = 6 SCHEMA(schema_id = 4)   Process 52 releasing lock on METADATA: database_id = 6 USER_TYPE(user_type_id = 167)   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 SCHEMA(schema_id = 1) (class bit0 ref1) result: OK   Process 52 acquiring Sch-S lock on OBJECT: 6:2121058592:0  (class bit0 ref1) result: OK   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 SCHEMA(schema_id = 1) (class bit0 ref1) result: OK   Process 52 releasing lock reference on METADATA: database_id = 6 SCHEMA(schema_id = 1)   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 INDEXSTATS(object_id = 2121058592, index_id or stats_id = 1) (class bit0 ref1) result: OK   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 INDEXSTATS(object_id = 2121058592, index_id or stats_id = 1) (class bit0 ref1) result: OK   Process 52 releasing lock reference on METADATA: database_id = 6 INDEXSTATS(object_id = 2121058592, index_id or stats_id = 1)   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 INDEXSTATS(object_id = 2121058592, index_id or stats_id = 1) (class bit0 ref1) result: OK   Process 52 acquiring Sch-S lock on METADATA: database_id = 6 STATS(object_id = 2121058592, stats_id = 1) (class bit0 ref1) result: OK   Process 52 acquiring S lock on KEY: 6:281474980642816 (5d0083d88d90) (class bit0 ref1) result: OK   Process 52 releasing lock reference on METADATA: database_id = 6 INDEXSTATS(object_id = 2121058592, index_id or stats_id = 1)   Process 52 releasing lock on METADATA: database_id = 6 STATS(object_id = 2121058592, stats_id = 1)   Process 52 releasing lock on METADATA: database_id = 6 SCHEMA(schema_id = 1)   Process 52 releasing lock on METADATA: database_id = 6 INDEXSTATS(object_id = 2121058592, index_id or stats_id = 1)   Process 52 releasing lock on OBJECT: 6:2121058592:0   Process 52 releasing lock on DATABASE: 6 [PLANGUIDE]   Process 52 acquiring Sch-S lock on OBJECT: 6:2121058592:0  (class bit0 ref1) result: OK     (21개행적용됨) Process 52 releasing lock on OBJECT: 6:2121058592:0

 

PLAN에 Hash Match가 나와서 그런 것으로 예상 되는데... cross apply에 비하면은 lock에 대한 부분은 월등히 많네요~

스트레스 테스트는 해보지 않았지만 시간 있을 때 한번 해보면 좋을 듯 하네요~