by 와니와플 [SQL Query] [2019.12.22 22:58:38]
안녕하세요, 아래 쿼리를 작성 했는데,
아무래도 조인이 많이 걸려서 조금 속도가 더디게 나옵니다.
데이터가 많이 쌓인 것은 아니라서, 혹시 좋은 튜닝 포인트가 있을까요?
SELECT Z.PROD_CD
, Z.PROD_SIZE_CD
, Z.PROD_LENGTH
, Z.BRAND_CD
, Z.INVENTORY_CD
, SUM(Z.QTY) AS QTY
, SUM(Z.WEIGHT) AS WEIGHT
, MAX(B.PROD_NM) AS PROD_NM
, MAX(C.PROD_SIZE_NM) AS PROD_SIZE_NM
, MAX(D.USER_NAME) AS UPDATE_NM
, MAX(E.BRAND_NM) AS BRAND_NM
, MAX(F.INVENTORY_NM) AS INVENTORY_NM
, SUM(Z.QTY_SUM) AS QTY_SUM
, SUM(Z.RCV_QTY_SUM) AS RCV_QTY_SUM
, SUM(Z.DEL_QTY_SUM) AS DEL_QTY_SUM
FROM (
SELECT A.PROD_CD
, A.PROD_SIZE_CD
, A.PROD_LENGTH
, A.BRAND_CD
, A.INVENTORY_CD
, A.QTY
, A.WEIGHT
, A.QTY AS QTY_SUM
, 0 AS RCV_QTY_SUM
, 0 AS DEL_QTY_SUM
, A.UPDATE_ID
FROM PROD_STOCK_MST A
UNION ALL
SELECT B.PROD_CD
, B.PROD_SIZE_CD
, B.PROD_LENGTH
, B.BRAND_CD
, B.INVENTORY_CD
, 0 AS QTY
, 0 AS WEIGHT
, 0 AS QTY_SUM
, B.QTY AS RCV_QTY_SUM
, 0 AS DEL_QTY_SUM
, B.UPDATE_ID
FROM PROD_STOCK_PREUSABLE B
UNION ALL
SELECT C.PROD_CD
, C.PROD_SIZE_CD
, C.PROD_LENGTH
, C.BRAND_CD
, C.INVENTORY_CD
, 0 AS QTY
, 0 AS WEIGHT
, 0 AS QTY_SUM
, 0 AS RCV_QTY_SUM
, C.QTY AS DEL_QTY_SUM
, C.UPDATE_ID
FROM PROD_STOCK_UNUSABLE C
) Z
LEFT JOIN DIM_PROD B ON Z.PROD_CD = B.PROD_CD
LEFT JOIN DIM_PROD_SIZE C ON (Z.PROD_CD = C.PROD_CD AND Z.PROD_SIZE_CD = C.PROD_SIZE_CD)
LEFT JOIN DIM_USER D ON Z.UPDATE_ID = D.USER_ID
LEFT JOIN DIM_BRAND E ON Z.BRAND_CD = E.BRAND_CD
LEFT JOIN DIM_INVENTORY F ON Z.INVENTORY_CD = F.INVENTORY_CD
/*
<where>
<if test="sear != null and sear != ''">
AND
(
Z.PROD_CD LIKE CONCAT('%',#{sear},'%')
OR B.PROD_NM LIKE CONCAT('%',#{sear},'%')
OR F.INVENTORY_NM LIKE CONCAT('%',#{sear},'%')
OR E.BRAND_NM LIKE CONCAT('%',#{sear},'%')
)
</if>
<if test="sear2 != null and sear2 != ''">
AND Z.PROD_CD=#{sear2}
</if>
<if test="sear3 != null and sear3 != ''">
AND Z.PROD_SIZE_CD=#{sear3}
</if>
<if test='sear4 != null and sear4 == "N"'>
AND (Z.QTY_SUM > 0 OR Z.RCV_QTY_SUM > 0 OR Z.DEL_QTY_SUM > 0)
</if>
</where>
*/
GROUP BY Z.PROD_CD, Z.PROD_SIZE_CD, Z.PROD_LENGTH, Z.BRAND_CD, Z.INVENTORY_CD
ORDER BY MAX(B.SEQ), MAX(C.SEQ), Z.PROD_LENGTH, MAX(E.SEQ), Z.INVENTORY_CD