Lotiony's Blog

김정선님의 쿼리 튜닝 글을 보고  몇가지 실제사례에 넣어본 결과.

자료 : 5천ROW정도의 문제테이블 (MQ_Q_INFO)
         300ROW정도의 유형테이블_카테고리  (MQ_Q_TYPE)
         600ROW정도의  기존출제문제 히스토리테이블 (MQ_Q_HST)
         200ROW정도의 회원테이블(MQ_MEM)
        ※ FN_SPLIT은 문자열을 분리해서 POS값과 VALUE값을 테이블로 반환해주는 사용자함수.

액션 : 문제테이블에서 이번에 출제할 대상문항을 여러 조건에 의해 추려내야한다.
조건1. 정해진 유형에 소속된 문항만 추출. (20010^20210^ .... 문자열을 SPLIT 처리)
          유형은 10개를 선택할수도 있고 100개를 선택할수도 있다..
조건2. 과거 출제경력을 조회해서 최근 5회차에 출제된 문제를 배제시킨다.(TIMES로 관리됨)
조건3. 조건2와 더불어서  기존에 3회이상 출제됐었던 문제를 배제시킨다. (SETTIMES로 누적관리됨)

조건1(선택유형 내에서만 뽑는부분)은 걍 SPLIT해서 해당키값을 유형테이블에서 Q_TYPE_CD로 바꿔온다음 문제테이블에서 쪼인해버리는걸로 처리했다.
이렇게 해서 뽑았을때 5천ROW중 708문제가 대상문제로 추출됨.

조건2와 3은 UNION으로 묶고 DISTINCT해준다음 (90개문항이 배제대상문항으로 나옴)
그걸  김정선님의 차집합 스킬로 처리했다.. 최종 산출 row는 618개.

한번에 만들어진게 아니고 계속 조립식으로 만들다보니 너저분해지긴 했지만..
요점은  김정선님의 차집합 추천스킬의 결과를 보는게 목적이므로......


참고블로그 : http://blog.naver.com/visualdb/50028645471
여기서 3,4,5번만 가지고 테스트.

3. NOT IN + 상관서브쿼리+ TOP 1  (평가 : (Very) Good)

 SELECT TPB1.* FROM MQ_Q_INFO TPB1
 , ( SELECT A.Q_TYPE_CD
  FROM MQ_Q_TYPE A,
  (SELECT * FROM  dbo.[FN_SPLIT]('20010^20210^20700^20710^20720^20220^20230^20240^20250^20260^20270^20280^20290^20300','^')) B
  WHERE
  A.CTG2_CD = B.VAL1
 ) TPB2
 WHERE
 TPB1.Q_TYPE_CD = TPB2.Q_TYPE_CD
 
 AND TPB1.Q_NUM NOT IN (SELECT TOP 1 TPC1.Q_NUM FROM
       (
        SELECT DISTINCT(YY.Q_NUM) FROM 
         (
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND SETTIMES > 3
          UNION
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND TIMES > (SELECT MAX(TIMES)-5 FROM MQ_Q_HST WHERE STU_ID = '8701290008')
         ) YY
         ) TPC1
         WHERE TPC1.Q_NUM = TPB1.Q_NUM
         )
총실행시간
시도횟수 1 : 2220 ms
시도횟수 2~10 :  1087 ~ 1165  (평균 1097)

4. NOT EXISTS + 상관서브쿼리+ TOP 1 (평가 : Not bad )

 SELECT TPB1.* FROM MQ_Q_INFO TPB1
 , ( SELECT A.Q_TYPE_CD
  FROM MQ_Q_TYPE A,
  (SELECT * FROM  dbo.[FN_SPLIT]('20010^20210^20700^20710^20720^20220^20230^20240^20250^20260^20270^20280^20290^20300','^')) B
  WHERE
  A.CTG2_CD = B.VAL1
 ) TPB2
 WHERE
 TPB1.Q_TYPE_CD = TPB2.Q_TYPE_CD
 
 AND NOT EXISTS (SELECT TOP 1 TPC1.Q_NUM FROM
       (
        SELECT DISTINCT(YY.Q_NUM) FROM 
         (
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND SETTIMES > 3
          UNION
          SELECT Q_NUM FROM MQ_Q_HST
          WHERE STU_ID = '8701290008'
          AND TIMES > (SELECT MAX(TIMES)-5 FROM MQ_Q_HST WHERE STU_ID = '8701290008')
         ) YY
         ) TPC1
         WHERE TPC1.Q_NUM = TPB1.Q_NUM
         )


총실행시간
시도횟수 1 : 145 ms
시도횟수 2~10 : 17ms ~ 60ms (평균 57.7ms)

 엄청빠르다 +_+

5. 상관 서브쿼리+ TOP 1 + IS NULL (평가 : 김정선의 서브쿼리 튜닝 필살기 (Very) Good)

 SELECT TPB1.* FROM MQ_Q_INFO TPB1
 , ( SELECT A.Q_TYPE_CD
  FROM MQ_Q_TYPE A,
  (SELECT * FROM  dbo.[FN_SPLIT]('20010^20210^20700^20710^20720^20220^20230^20240^20250^20260^20270^20280^20290^20300','^')) B
  WHERE
  A.CTG2_CD = B.VAL1
 ) TPB2
 WHERE
 TPB1.Q_TYPE_CD = TPB2.Q_TYPE_CD
 
 AND (SELECT TOP 1 TPC1.Q_NUM FROM
  (
   SELECT DISTINCT(YY.Q_NUM) FROM 
    (
     SELECT Q_NUM FROM MQ_Q_HST
     WHERE STU_ID = '8701290008'
     AND SETTIMES > 3
     UNION
     SELECT Q_NUM FROM MQ_Q_HST
     WHERE STU_ID = '8701290008'
     AND TIMES > (SELECT MAX(TIMES)-5 FROM MQ_Q_HST WHERE STU_ID = '8701290008')
    ) YY
    ) TPC1
    WHERE TPC1.Q_NUM = TPB1.Q_NUM
    ) IS NULL

총실행시간
시도횟수 1 : 202 ms
시도횟수 2~10 : 175ms ~ 217ms (평균 186.5ms)









결론 : 다른부분은 전혀 튜닝도 안되고 구조도 제멋대로인 부분이지만   차집합구하기 부분만 놓고 봤을때는 어떤 형태의 쿼리에 얹히느냐에 따라  성능의 우위가 바뀔수도 있음을 확인할수 있다.

똑같은작업을 할때 NOT IN이냐 NOT EXISTS냐에 따라 20배에 가까운 속도를 단축할수 있다는데에 새삼 놀랍다....



쓸데없는 DISTINCT를 뺏더니 57.7ms에서 36ms로  또 급 단축되네..ㅎㅎㅎ