MSSQL LEFT JOIN 집계로 반복 엑세스 줄이기

한 테이블을 반복 엑세스 하는 문제가 발생함

주문이 완료된 건수의 주문ID만 출력하는 쿼리에서 아래의 2가지 사항을 제외함

• 상품원장의 상태가 취소인 건수
• 발주신청이 안되어 조인이 안되는 건수

 

 

 

[튜닝 전]

SELECT A.ORDERID, A.COUNT 
  FROM
 (SELECT ORDERID, COUNT (ID) AS COUNT 
    FROM ORDERDETAIL
   WHERE PLACE = 'SEOUL'
     AND ORDERID IN (1,2,3)
   GROUP BY ORDERID) A
 INNER JOIN 
 (SELECT A.ORDERID, COUNT(A.ORDERID) AS COUNCT
    FROM PRODUCTASK B
   INNER JOIN ORDERDETAIL A ON B.ORDERDETAILID = A.ORDERDETAILID
   INNER JOIN PRODUCTLEDGER C ON C.PRODUCTID = B.PRODUCTID
   WHERE A.PLACE = 'SEOUL'
     AND C.STATE != 'CANCEL'
     AND A.ORDERID IN (1,2,3)
   GROUP BY A.ORDERID) A2
    ON A.ORDERID = A2.ORDERID
 WHERE A.COUNT > 0
   AND A.COUNT = A2.COUNT
;

 

 

[튜닝 후]

• ROW_NUMBER를 사용하여 STATE가 NULL인 집합을 첫번째로 나오게 한다(RN=1) 그 후에 A.STATE != 'CANCEL'에서 NULL과 비교하면 거짓이므로 'CANCEL'과 NULL은 제외됨

SELECT A.ORDERID, CNT --CNT는 확인용
FROM (
SELECT A.*, ROW_NUMBER() OVER(PARTITION BY A.ORDERID ORDER BY A.STATE ) RN 
FROM (
SELECT A.ORDERID, C.STATE, COUNT(*) CNT 
 FROM ORDERDETAIL A
 LEFT JOIN  PRODUCTASK B 
   ON B.ORDERDETAILID = A.ORDERDETAILID
 LEFT JOIN PRODUCTLEDGER C 
   ON C.PRODUCTID = B.PRODUCTID
WHERE 1=1
  AND A.PLACE = 'SEOUL'
  AND A.ORDERID IN (1,2,3)
GROUP BY A.ORDERID, C.STATE 
) A 
) A 
WHERE RN = 1
  AND A.STATE != 'CANCEL' --NULL제거됨 (조인안된 건수 제거)
;