DataBaser.Net: 함수를이용한연결액세스튜닝사례

1 문제의 SQL #

다음과 같은 SQL문이 있다. 수행시간이 약 28초 정도였다. 이 쿼리가 수행되는 사이트에서는 이 쿼리만 수행되면 시스템이 벅벅된다고 하소연을 하였다.

select distinct Case A.InoutGubun When 'SO' then A.InoutNo else A.OriginNo End as InoutNo
 , A.InoutDate 
 ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = B.WhCode)
 ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = A.InoutPlace)
 ,A.InoutPlace , A.SiteCode , B.WhCode 
from mmInoutHeader A join mmInOutItem B 
on A.SiteCode=B.SiteCode 
and A.InoutNo = B.InoutNo 
and A.InoutGubun=B.InoutGubun 
where A.SiteCode = N'N100' 
and  A.inoutGubun = N'SO' 
and A.SysCase = N'400' 
And A.CaseCode = N'400' 
and  A.InOutPlace in (  select WhCode 
                        From WhMaster 
                        where ComCode = N'NXN1' 
                        and C_DeptCode = N'11403' 
                        and WhUse=N'Y' 
                        and  WhType = 'SM' and SiteCode = N'N100' )
and B.InoutQty - (select isnull(SUM(D.InoutQty),0) as InWhQty 
                  from mminoutHeader C join mmInOutItem D 
                  on C.SiteCode = D.SiteCode 
                  and C.InOutNo = D.InOutNo 
                  and C.InoutGubun=D.InoutGubun 
                  where C.SiteCode = A.SiteCode 
                  and C.OriginNo = A.InoutNo 
                  and D.TrackingAltKey = B.InoutSerNo ) > 0 
order by A.InoutNo
go

위 쿼리를 쉽게 다시 작성하면 다음과 같다.

select distinct Case A.InoutGubun When 'SO' then A.InoutNo else A.OriginNo End as InoutNo
 , A.InoutDate 
 ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = B.WhCode)
 ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = A.InoutPlace)
 ,A.InoutPlace , A.SiteCode , B.WhCode 
from mmInoutHeader A join mmInOutItem B 
on A.SiteCode=B.SiteCode 
and A.InoutNo = B.InoutNo 
and A.InoutGubun=B.InoutGubun 
where A.SiteCode = N'N100' 
and  A.inoutGubun = N'SO' 
and A.SysCase = N'400' 
And A.CaseCode = N'400' 
and  A.InOutPlace in (  select WhCode 
                        From WhMaster 
                        where ComCode = N'NXN1' 
                        and C_DeptCode = N'11403' 
                        and WhUse=N'Y' 
                        and  WhType = 'SM' and SiteCode = N'N100' )
and B.InoutQty > (select isnull(SUM(D.InoutQty),0) as InWhQty 
                  from mminoutHeader C join mmInOutItem D 
                  on C.SiteCode = D.SiteCode 
                  and C.InOutNo = D.InOutNo 
                  and C.InoutGubun=D.InoutGubun 
                  where C.SiteCode = A.SiteCode 
                  and C.OriginNo = A.InoutNo 
                  and D.TrackingAltKey = B.InoutSerNo )
order by A.InoutNo
go

위 쿼리는 상관서브쿼리로 메인쿼리의 결과 1건을 받아 서브쿼리(적색글씨)에서 조건에 맞게 처리를 하는 것이 일반적이다. 그러나 위 쿼리는 서브쿼리와 메인쿼리를 각각 독립적으로 처리하여 Hash Join을 하게 된다. 그러므로 서브쿼리의 mminoutHeader 테이블에서 1404720건과 mmInOutItem 테이블에서 1874368건을 각각 읽어 Merge Join을 하는 형태로 풀리어 집계를 한다. 이 쿼리는 쿼리 예상비용이 매우 높기 때문에(100이상) 병렬쿼리를 수행하여 CPU 자원을 많이 사용하게 된다. 튜닝 전 쿼리의 자원 사용량과 수행시간은 다음과 같다.

CPU : 96622
Reads : 542468
Duration: 28173

'WhMaster' 테이블. 스캔 수 8, 논리적 읽기 수 24, 물리적 읽기 수 0, 미리 읽기 수 0.
'mmInoutItem' 테이블. 스캔 수 428, 논리적 읽기 수 181544, 물리적 읽기 수 0, 미리 읽기 수 0.
'mmInoutHeader' 테이블. 스캔 수 1474, 논리적 읽기 수 312090, 물리적 읽기 수 0, 미리 읽기 수 0.

이 쿼리의 문제는 데이터 연결상의 문제다. 데이터를 다른 방식으로 연결하여 메인쿼리의 결과를 받아 적절한 인덱스를 사용하여 집계한다면 빠른 결과를 볼 수 있다. 이러한 경우는 함수를 사용하여 데이터를 연결시키는 것이 효과적이다. 그러므로 다음과 같은 저장함수를 생성하였다.

create function fn_InWhQty (@SiteCode varchar(255), @InoutNo varchar(255), @InoutSerNo varchar(10) )
returns numeric
AS
begin
    declare @rs numeric
    begin
        select @rs = isnull(SUM(D.InoutQty),0) 
        from mminoutHeader C inner loop join mmInOutItem D 
        on C.SiteCode = D.SiteCode 
        and C.InOutNo = D.InOutNo 
        and C.InoutGubun=D.InoutGubun 
        where C.SiteCode = @SiteCode 
        and C.OriginNo = @InoutNo
        and D.TrackingAltKey = @InoutSerNo
        return @rs
    end
end
go

함수 생성 뒤 함수를 이용하여 서브쿼리를 처리한 결과는 다음과 같다.

select distinct Case A.InoutGubun When 'SO' then A.InoutNo else A.OriginNo End as InoutNo
 , A.InoutDate 
 ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = B.WhCode)
 ,(SELECT WhName FROM WHMASTER WHERE ComCode = N'NXN1' and WhCode = A.InoutPlace)
 ,A.InoutPlace , A.SiteCode , B.WhCode 
from mmInoutHeader A join mmInOutItem B 
on A.SiteCode= B.SiteCode 
and A.InoutNo = B.InoutNo 
and A.InoutGubun=B.InoutGubun 
and A.SiteCode = N'N100' 
and  A.inoutGubun = N'SO' 
and A.SysCase = N'400' 
And A.CaseCode = N'400' join WhMaster c
on  A.InOutPlace = c.WhCode
and c.ComCode = N'NXN1' 
and c.C_DeptCode = N'11403' 
and c.WhUse=N'Y' 
and c.WhType = 'SM' 
and c.SiteCode = N'N100'
and B.InoutQty > xerp.dbo.fn_InWhQty(a.SiteCode, A.InoutNo, B.InoutSerNo)
order by A.InoutNo
GO

CPU : 1734
Reads: 60056
ation: 1766

'mmInoutItem' 테이블. 스캔 수 173, 논리적 읽기 수 852, 물리적 읽기 수 0, 미리 읽기 수 0.
'mmInoutHeader' 테이블. 스캔 수 1, 논리적 읽기 수 30691, 물리적 읽기 수 0, 미리 읽기 수 0.
'WhMaster' 테이블. 스캔 수 3, 논리적 읽기 수 9, 물리적 읽기 수 0, 미리 읽기 수 0.

실제로 몇 천건이 처리대상임에 불구하고 튜닝 전 쿼리는 약 1백 50만건 정도를 처리대상으로 처리를 하고 있는 것이 문제였다.

Contents

1 문제의 SQL #

2 튜닝 전/후 비교자료 #

튜닝 전/후 비교	CPU사용량	Reads사용량	수행시간
전	96622	542468	28173
후	1734	60056	1766
향상정도	약 56배	약 9배	약 15배