튜닝 예시 #1

• 속도개선 : 1303ms → 23ms

-- 원본쿼리
select count(*)
  from (
        select o.userid
          from 주문 o
             , (
                select userid
                  from 배송 d
                 where d.배송일자코드='20210702_1555'
                   and d.userid not in (N'koukann',N'd_press',N'rakuten1234',N'amazon1234',N'sunbuy1')
                 group by userid
               ) a
         where o.주문상태='4'
           and o.userid = a.userid Collate Japanese_CI_AI
           and convert(varchar(10),o.결제일시,121)<'2021-07-02'
           AND O.결제수단 <>'7'
           and O.userid not in (N'테스트아이디1',N'테스트아이디2',N'테스트아이디3',N'테스트아이디4',N'테스트아이디5')
           and O.주문사이트코드 not in ('F')
         group by o.userid
        having count(o.userid)>0
       ) b;
-- 2035
-- CPU 시간 = 1297ms, 경과 시간 = 1303ms
-- 약 132만건 억세스 후 aggreagtion 한 다음 카운트

-- 튜닝쿼리
select count(*)
  from (
        select userid
          from 배송 with(nolock)
         where 배송일자코드='20210702_1555'
           and userid not in (N'테스트아이디1',N'테스트아이디2',N'테스트아이디3',N'테스트아이디4',N'테스트아이디5')
         group by userid
       ) a
 where exists (
               select 1
                 from 주문 with(nolock)
                where userid = a.userid Collate Japanese_CI_AI
                  and 주문상태 = '4'
                  and 결제일시 < '2021-07-02'
                  AND 결제수단 <> '7'
                  and 주문사이트코드 not in ('F')
              );
-- 2035
-- CPU 시간 = 16ms, 경과 시간 = 23ms
-- 약 2천건 억세스에 대한 결과 카운트

-- 결과 : 세미조인을 이용하기 위한 쿼리 형태 변경과 인덱스 대상 컬럼 변형 되지 않도록 변경하여
         적합한 인덱스와 억세스패스를 이용할 수 있도록 함.
         억세스 수를 132만건에서 2천건으로 1% 미만이 되게 함으로써
         총 수행시간을 1300ms -> 50ms로 98% 줄임

* 튜닝 포인트
1. convert(varchar(10),o.결제일시,121)<'2021-07-02'
  - 인덱스 대상이 될 수도 있는  컬럼을 변형하여 필터링하는 구문
    개발자분들이 아주 많이 실수하는 구문으로 결제일시 컬럼에 인덱스가 있더라도
    해당 인덱스를 활용할 수 없게 만드는 구문
    보통은 특정일자에서 몇일 더하거나 빼는 과정에서 발생하는데
    이 쿼리는 아예 그럴 필요가 없음에도 사용됨.

2. exists를 통한 세미 조인
  - 원본 쿼리를 보면 특정 배송일자 코드에 배송된 고객들을 추출하여
    해당 배송일 이전에 결제를 한적이 있는 고객들의 주문을 전체를 조인하는것을 볼 수 있다.
    이후 그룹핑을 해서 카운트를 하는것을 보면
    결국 해당 배송일 이전에 결제를 몇번 했는지는 무의미하고 결제를 한적이 있느냐 없느냐만
    구분할 수 있으면 된다.
    exists나 not exists를 통한 세미조인을 모를 경우 보통 작성하게 되는 방식으로
    주문 테이블의 데이터가 적을 경우는 별차이가 없겠지만 1년, 2년 증가하면서
    극악으로 치닫게 된다. 해당 테이블의 경우 무려 10년치 데이터.
    보통 첫주문 고객인지 여부를 찾는 쿼리를 작성하다가 많이 발생한다.

3. having count(o.userid)>0
  - 이 부분은 2번을 통해 자연스럽게 제거되긴 하지만
    원본쿼리에서도 전혀 불필요한 부분으로 아마 옵티마이저가 알아서 걸렀을 가능성이 높다.
    o.userid는 조인이 되면서 null이 발생할 수가 없기 때문에(이미 not null 컬럼이기도함.)
    count(o.userid)의 결과는 0이상일 수 밖에 없으므로 제거되어야할 구문이다.

* 제안사항
  - 해당 쿼리의 목적은 해당 날짜 이전에 결제된 적이 있는 배송 고객수를 추출하는 것 인듯 한데
     실시간성으로 꼭 필요한게 아니라면 1시간 또는 1일 주기로 반정규화한 형태 데이터를 생성해놓고
     생성된 통계용 테이블을 조회하는것이 올바름