심심풀이 14탄 - 적절한 inline-view의 사용.

우리가 작성하는 쿼리문을 보면 inline-view가 많이 쓰이고 있다.
그런데, 우리는 그것을 얼마나 적절히 잘 사용하고 있는지 다시한번 되짚어 보고자
이번에는 inline-view에 관한 샘플 두가지만을 살짝 살펴보고자 한다.

다들 짧은것을 좋아하는듯하여 간단히 하고자 사례를 2가지만을 선정.
/* ============================================================================== */
테이블 연결관계를 보면

   제조사          상품              판매
     m ------------- p --------------- a
     1               M                
                     1                 M

이런 관계를 가진 테이블이 있다고 가정하구

먼저 샘플부터 만들고

--drop table m   -- 제조사 테이블
create table m
( maker_id int not null primary key
, maker_name varchar(10))
go

set nocount on
declare @i int
set @i = 1
while (@i <= 10)
begin
   insert into m values (@i, '회사'+right('0'+convert(varchar(3),@i),2))
   set @i = @i + 1
end
set nocount off

--drop table p   -- 상품 테이블
create table p
( product_id int not null primary key
, product_name varchar(10)
, maker_id int)
go

set nocount on
declare @i int
set @i = 1
while (@i <= 100)
begin
   insert into p values (@i, '상품'+right('00'+convert(varchar(3),@i),3),
                         convert(int, rand()*10)+1)
   set @i = @i + 1
end
set nocount off

--drop table a   -- 판매 테이블
create table a
( dt char(8) not null
, seq int not null
, qty int not null
, product_id int not null)
go
alter table a
   add constraint pk_a primary key(dt, seq)
create index idx on a(product_id)
go
set nocount on
declare @i int, @j int, @dt datetime
set @dt = '2002-01-01'
while (@dt <= '2003-06-30')
begin
    set @i = convert(int, rand()*1000)+1
    set @j = 1
    while (@j <= @i)
    begin
       insert into a values (convert(char(8),@dt,112),
                             @j,
                             convert(int, rand()*1000)+1,
                             convert(int, rand()*100)+1 )
       set @j = @j + 1
    end
set @dt = dateadd(dd, 1, @dt)
end
set nocount off
go
select count(*) from a   --262235

/* ============================================================================== */

사례1 : 상품판매테이블(a)에서 2002년도 월별 합을 구해보자
/* ------------------------------------------------------------------------------ */
< 출력형태 >

합계 m1   m2   m3   m4   m5   m6   m7   m8   m9   m10   m11   m12        
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----  ----  ----

위와 같은 출력형태로 2002년도 1년치 월별 합계를 구하는 쿼리를 작성해보자.

/* ------------------------------------------------------------------------------ */
쿼리1. 가장 단순한 쿼리
--dbcc freeproccache
--dbcc dropcleanbuffers
select sum(qty) 합계,
       sum(case when substring(dt,5,2) = '01' then qty end) m1,
       sum(case when substring(dt,5,2) = '02' then qty end) m2,
       sum(case when substring(dt,5,2) = '03' then qty end) m3,
       sum(case when substring(dt,5,2) = '04' then qty end) m4,
       sum(case when substring(dt,5,2) = '05' then qty end) m5,
       sum(case when substring(dt,5,2) = '06' then qty end) m6,
       sum(case when substring(dt,5,2) = '07' then qty end) m7,
       sum(case when substring(dt,5,2) = '08' then qty end) m8,
       sum(case when substring(dt,5,2) = '09' then qty end) m9,
       sum(case when substring(dt,5,2) = '10' then qty end) m10,
       sum(case when substring(dt,5,2) = '11' then qty end) m11,
       sum(case when substring(dt,5,2) = '12' then qty end) m12
from a
where dt like '2002%'
go

그동안 많이 봐왔기 때문에 sum(case())에 관하여는 설명 생략.

이럴경우 그냥 간단하게 이런식으로 쿼리문을 작성하는 경우가 설마 있지는 않겠지만
그래두 지면을 떼우기 위해서 이런식으로 쿼리문을 만든다고 가정하구서

먼저 대상이 되는 레코드수를 확인해보면
select count(*) from a where dt like '2002%'
172728 row 이다.
그러면, 위와 같은 쿼리문을 돌리게 되면 172728 * 12 = 2072736
대략, 207만번을 case 문을 사용하고, substring()함수를 207만번 사용한다.
정말 끔찍하다.
실제로 돌려서 성능을 측정해보자.

CPU   Reads   Duration  --5회 반복실행한 내용을 프로필러로 캡춰한 수치
----  ------  ---------
6381  2020    6423
5672  713     5670
5640  713     5656
5735  713     5936
5656  713     5830

대략 어림잡아서 5.6초 정도 걸려서 결과를 보여준다.

/* ------------------------------------------------------------------------------ */
쿼리2. 불필요한 substring()함수의 사용을 줄여보고자 inline-view를 사용한 쿼리

--dbcc freeproccache
--dbcc dropcleanbuffers
select sum(qty) 합계,
       sum(case when mm = '01' then qty end) m1,
       sum(case when mm = '02' then qty end) m2,
       sum(case when mm = '03' then qty end) m3,
       sum(case when mm = '04' then qty end) m4,
       sum(case when mm = '05' then qty end) m5,
       sum(case when mm = '06' then qty end) m6,
       sum(case when mm = '07' then qty end) m7,
       sum(case when mm = '08' then qty end) m8,
       sum(case when mm = '09' then qty end) m9,
       sum(case when mm = '10' then qty end) m10,
       sum(case when mm = '11' then qty end) m11,
       sum(case when mm = '12' then qty end) m12
from (select substring(dt,5,2) mm, qty
      from a
      where dt like '2002%') x
go

이번에는 조금 신경쓰서 inline-view를 사용하여 substring()함수의 사용을 207만번에서
17만번으로 줄였다. 하지만 여전히 case()는 207만번 사용하고 있다.

이번에는 어느정도의 성능이 나올지 측정해보자.

CPU   Reads   Duration  --5회 반복실행한 내용을 프로필러로 캡춰한 수치
----  ------  ---------
5422  740     5466
5782  740     6000
5625  740     5813
5375  740     5420
5578  740     5610

위의 쿼리1과 별루 차이가 없다.
아주 약간 새발의 피만큼 성능향상이 되었다.
정말 새발의 피만큼이다.

좀더 연구해보자.

/* ------------------------------------------------------------------------------ */
쿼리3. 좀더 머리를 굴려서 case()와 substring()의 사용을 줄인 쿼리문

--dbcc freeproccache
--dbcc dropcleanbuffers
select sum(qty) 합계,
       sum(case when mm = '01' then qty end) m1,
       sum(case when mm = '02' then qty end) m2,
       sum(case when mm = '03' then qty end) m3,
       sum(case when mm = '04' then qty end) m4,
       sum(case when mm = '05' then qty end) m5,
       sum(case when mm = '06' then qty end) m6,
       sum(case when mm = '07' then qty end) m7,
       sum(case when mm = '08' then qty end) m8,
       sum(case when mm = '09' then qty end) m9,
       sum(case when mm = '10' then qty end) m10,
       sum(case when mm = '11' then qty end) m11,
       sum(case when mm = '12' then qty end) m12
from (select substring(dt,5,2) mm, sum(qty) qty
      from a
      where dt like '2002%'
      group by substring(dt,5,2)) x
go

이번에는 좀더 머리를 굴려서 substring()의 사용은 17만번을 하지만
case()의 사용은 대폭 줄여서 144번만을 사용한다.

역시 어느정도의 향상이 되는지 직접 확인해보자.

CPU   Reads   Duration  --5회 반복실행한 내용을 프로필러로 캡춰한 수치
----  ------  ---------
1500  744     1766
1734  744     1733
1734  744     1576
1391  744     1576
1469  744     1470

엄청나게 성능이 향상 되었다.
5.5초 정도에서 1.5초정도로 줄었다. 4초나 단축된것이다.

즉, 가능하다면 inline-view에서 줄여줄수 있는데 까지 대상 레코드수를 줄여서 inlin-view
바깥쪽에서의 함수사용을 최대한 줄여주는것이 조금이나마 성능향상을 도모할수 있는것이다.

물론, 여러분들의 쿼리문은 이렇게 되어있겠지요 ?   ^^;


/* ============================================================================== */

사례2 : 2003년 06월 한달치중에서 상품별 판매량 top 10 을 보자.

< 출력형태 >

product_id  product_name maker_name totalqty   
----------- ------------ ---------- -----------
40          상품040        회사01       103880
89          상품089        회사04       102740
66          상품066        회사05       98224
31          상품031        회사03       97991
29          상품029        회사10       95354
76          상품076        회사03       94957
41          상품041        회사08       94582
15          상품015        회사08       94539
53          상품053        회사08       93045
42          상품042        회사10       91828

(10개 행 적용됨)

/* ------------------------------------------------------------------------------ */
쿼리1. : 가장 일반적인 쿼리형태

--dbcc freeproccache
--dbcc dropcleanbuffers

--이번에는 실행계획까지 봐야하므로 설정해두고
set statistics profile on


select top 10 p.product_id, p.product_name, m.maker_name, sum(a.qty) totalqty
from a inner join p
on (p.product_id = a.product_id)
inner join m
on (m.maker_id = p.maker_id)
where dt like '200306%'
group by p.product_id, p.product_name, m.maker_name
order by sum(qty) desc

가장 일반적인 조인쿼리구문이다.
그냥 from절에다가 쭉~~~ 나열하면서 조인한 형태이다.
예전에 7.0 나와서 처음 sql을 사용하면서 이렇게 쿼리했었다.
그냥, 아무생각없이 쿼리해놓구서 시간이야 얼마가 걸리던지 IO가 어떻게 되는지 모르고
다만 결과가 제대로 나오니까 OK 됐어... 하구서 좋아하던 시절에 이렇게 쿼리했었다.

실행계획을 한번 확인해보자.

Rows  Exec  StmtText
----  ----  ---------------------------------------------
10    1     |--Sort(TOP 10, ORDER BY:([Expr1006] DESC))
100   1        |--Hash Match(Aggregate, HASH:([p].[product_id], [m].[maker_name])
16390 1           |--Hash Match(Inner Join, HASH:([m].[maker_id])=([p].[maker_id])
10    1              |--Clustered Index Scan(OBJECT:(m.pk))
16390 1              |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[product_id]))
16390 1                 |--Clustered Index Seek(OBJECT:(a.pk),
                           SEEK:([a].[dt] >= '200306' AND [a].[dt] < '200307'), 
                           WHERE:(like([a].[dt], '200306%', NULL)) ORDERED FORWARD)
16390 16390             |--Clustered Index Seek(OBJECT:(p.pk),
                           SEEK:([p].[product_id]=[a].[product_id]) ORDERED FORWARD)

그나마, Sort Merger방식으로 풀리지 않고 Hash 조인으로 처리되어서 p테이블과 m테이블은
1번씩만 읽었지만 p테이블은 16390번이나 읽었다. 성능이 좋을리 없다.
프로필러로 확인해보자.

CPU   Reads   Duration  --5회 반복실행한 내용을 프로필러로 캡춰한 수치
----  ------  ---------
984   32988   1000
906   32847   903
891   32847   903
891   32847   1063
891   32847   890

역시나 엄청난 read수를 기록하고 있다.

/* ------------------------------------------------------------------------------ */
쿼리2. 불필요한 group by를 제거한 쿼리

--dbcc freeproccache
--dbcc dropcleanbuffers

select top 10 p.product_id, min(p.product_name) pnm, min(m.maker_name) mnm, sum(a.qty) totalqty
from a inner join p
on (p.product_id = a.product_id)
inner join m
on (m.maker_id = p.maker_id)
where dt like '200306%'
group by p.product_id
order by sum(qty) desc

어짜피 p테이블의 product_id 컬럼이 기준이 되므로 p.product_name 이나 m.maker_name은
group by절에 오지 않아도 된다.
그래서, group by절에서 빼버리고 대신 select절에다가 min()함수를 사용하였다.
이것에 관해서는 심심풀이 4탄에서 언급하였으니 생략하구...

이번에두 실행계획을 확인해보자.

Rows  Exec  StmtText
----  ----  ---------------------------------------------
10    1     |--Sort(TOP 10, ORDER BY:([Expr1008] DESC))
100   1        |--Hash Match(Aggregate, HASH:([p].[product_id])
16390 1           |--Hash Match(Inner Join, HASH:([p].[product_id])=([a].[product_id]))
100   1              |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[maker_id]))
100   1              |  |--Clustered Index Scan(OBJECT:(p.pk), ORDERED FORWARD)
100   100            |  |--Clustered Index Seek(OBJECT:(m.pk),
                           SEEK:([m].[maker_id]=[p].[maker_id]) ORDERED FORWARD)
16390 1              |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
                        SEEK:([a].[dt] >= '200306' AND [a].[dt] < '200307'), 
                        WHERE:(like([a].[dt], '200306%', NULL)) ORDERED FORWARD)

group by절에서 컬럼 2개를 빼구 select절에 min()함수를 사용했는데 실행계획이
조금 다르게 나왔다.
이번에는 p테이블과 m테이블을 먼저 조인하구서 a테이블과 hash 조인을 한다.
그래도 여전히 불필요한 일을 하구 있다.
내가 필요한것은 판매량 상위 10개의 product_id에 해당하는 product_name과
그것의 maker_name이 필요한데 이번 실행계획을 보면 필요하지도 않은
모든 product_name과 maker_name을 join을 통해서 만들어 두고서 나중에
a테이블과의 hash join을 통해서 버린다. 여전히 실행계획이 맘에 안든다.

성능은 어떨지 확인해보자.

CPU   Reads   Duration  --5회 반복실행한 내용을 프로필러로 캡춰한 수치
----  ------  ---------
297   416     360
281   267     280
250   267     250
281   267     280
250   267     250

1번쿼리에 비해서 엄청나게 성능 향상이 되었다.
read수가 32847에서 267로 줄었다. 물론, cpu나 duration수치 역시 4배이상 향상이 되었다.

/* ------------------------------------------------------------------------------ */
쿼리3. 인라인뷰를 사용하여 좀더 효율을 높인 쿼리

--dbcc freeproccache
--dbcc dropcleanbuffers

select p.product_id, p.product_name, m.maker_name, x.totalqty
from (
      select top 10 product_id, sum(qty) totalqty
      from a
      where dt like '200306%'
      group by product_id
      order by sum(qty) desc) x
inner join p
   on (p.product_id = x.product_id)
inner join m
   on (m.maker_id = p.maker_id)

이번에는 inline-view를 사용하여 먼저 a테이블을 내가 필요로 하는 10의 row로 줄여놓고서
p테이블 과 1:1조인으로 유도하였다.
결국 p테이블과 a테이블은 10번만 조인을 하면 되는것이다. 물론 m테이블과도 10번만 조인을
하면 내가 원하는 모든 정보를 가져올수있게 된다.

의도한대로 실행계획이 나오는지 확인해보자.

Rows  Exec  StmtText
----  ----  ---------------------------------------------
10    1     |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[maker_id]))
10    1        |--Nested Loops(Inner Join, OUTER REFERENCES:([a].[product_id]))
10    1        |  |--Sort(TOP 10, ORDER BY:([Expr1002] DESC))
100   1        |  |  |--Hash Match(Aggregate, HASH:([a].[product_id])
16390 1        |  |     |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[a].[pk_a]),
                           SEEK:([a].[dt] >= '200306' AND [a].[dt] < '200307'), 
                           WHERE:(like([a].[dt], '200306%', NULL)) ORDERED FORWARD)
10    10       |  |--Clustered Index Seek(OBJECT:(p.pk),
                     SEEK:([p].[product_id]=[a].[product_id]) ORDERED FORWARD)
10    10       |--Clustered Index Seek(OBJECT:(m.pk),
                  SEEK:([m].[maker_id]=[p].[maker_id]) ORDERED FORWARD)

내가 의도한대로 실행계획이 잘 풀렸다.
a테이블에서 정확히 상위 10개의 row만이 걸러져서 p테이블과 조인하구, m테이블과 조인을한다.

당연히 성능향상이 있겠지만, 어느정도 인지 확인해보자.

CPU   Reads   Duration  --5회 반복실행한 내용을 프로필러로 캡춰한 수치
----  ------  ---------
140   208     200
125   105     126
125   105     126
125   105     123
125   105     123

대략 2번 쿼리에 비해서 2배정도의 성능향상이 있는것 같다.

물론, 일반적으로는 1:M의 관계에 있는 테이블끼리의 조인은 m번 발생을 하지만
이번 예제에서와 같이 1:1 조인으로 처리가 가능한 경우는 먼저 m쪽을 inline-view를
적절히 사용하여 1로 만들고 나서 1쪽과 1:1조인을 하도록 쿼리한다면
조금이나마 성능향상을 도모할수 있을듯...

요번거는 별루 주석달것이 없어서 다행인듯... 주석달기는 너무힘들어~~~  ㅜ_ㅜ...