|
아래 쿼리문 중에서 총 생산량 수행시 "양품 생산 정보" 와 "불량 생산 정보" 테이블을 한번더 수행함에 있어 속도가 너무 느립니다.
고수님의 조언을 구하고자 합니다.
작업 환경
SQL SERVER 2012 64Bit, ORACLE 11g 64Bit 두군데 다 작업해야 합니다.
ORACLE에서는 퍼포먼스가 양호 합니다.
그리고 인덱스는 잘 적용되고 잇습니다.
항상 좋은 정보 감사 드립니다.
WITH CTE_GOOD (itemcod, colorcd, mm, tot_qty) AS -- 양품 생산 정보
(SELECT itemcod, colorcd
, SUBSTRING(prodate, 5,2) AS mm
, ISNULL(tot_qty,0) AS tot_qty
FROM praotr01
WHERE prodate BETWEEN '20150101' and '20151231' --:Fdate AND :Tdate
AND tot_time > 0
)
,
CTE_BAD (itemcod, colorcd, mm, bad_qty) AS -- 불량 생산 정보
(SELECT itemcod, colorcd
, SUBSTRING(prodate, 5,2) AS mm
, ISNULL(bad_qty,0) AS bad_qty
FROM praotr04
WHERE prodate BETWEEN '20150101' and '20151231' --:Fdate AND :Tdate
AND bad_qty > 0
)
--
SELECT r.name AS carkind, i.itemcod, i.itemnam, i.itemno, s.colorcd, c.name AS colornm
, s.clause
, s.tot, s.ave, s.m01, s.m02, s.m03, s.m04, s.m05
, s.m06, s.m07, s.m08, s.m09, s.m10, s.m11, s.m12
FROM
(SELECT p.itemcod, p.colorcd
, '1. 총 생산량' AS clause
, SUM(CASE WHEN mm = '01' THEN tot_qty ELSE 0 END) m01
, SUM(CASE WHEN mm = '02' THEN tot_qty ELSE 0 END) m02
, SUM(CASE WHEN mm = '03' THEN tot_qty ELSE 0 END) m03
, SUM(CASE WHEN mm = '04' THEN tot_qty ELSE 0 END) m04
, SUM(CASE WHEN mm = '05' THEN tot_qty ELSE 0 END) m05
, SUM(CASE WHEN mm = '06' THEN tot_qty ELSE 0 END) m06
, SUM(CASE WHEN mm = '07' THEN tot_qty ELSE 0 END) m07
, SUM(CASE WHEN mm = '08' THEN tot_qty ELSE 0 END) m08
, SUM(CASE WHEN mm = '09' THEN tot_qty ELSE 0 END) m09
, SUM(CASE WHEN mm = '10' THEN tot_qty ELSE 0 END) m10
, SUM(CASE WHEN mm = '11' THEN tot_qty ELSE 0 END) m11
, SUM(CASE WHEN mm = '12' THEN tot_qty ELSE 0 END) m12
, SUM(tot_qty) AS tot
, SUM(ROUND( (tot_qty / 12), 0)) AS ave
FROM (SELECT p.itemcod, p.colorcd, p.mm
, ISNULL(p.tot_qty,0) + ISNULL(b.bad_qty,0) as tot_qty
FROM CTE_GOOD p
LEFT JOIN CTE_BAD b ON b.itemcod = p.itemcod AND b.mm = p.mm
) p
GROUP BY p.itemcod, p.colorcd
UNION ALL
SELECT p.itemcod, p.colorcd
, '2. 양품 수량' AS clause
, SUM(CASE WHEN mm = '01' THEN tot_qty ELSE 0 END) m01
, SUM(CASE WHEN mm = '02' THEN tot_qty ELSE 0 END) m02
, SUM(CASE WHEN mm = '03' THEN tot_qty ELSE 0 END) m03
, SUM(CASE WHEN mm = '04' THEN tot_qty ELSE 0 END) m04
, SUM(CASE WHEN mm = '05' THEN tot_qty ELSE 0 END) m05
, SUM(CASE WHEN mm = '06' THEN tot_qty ELSE 0 END) m06
, SUM(CASE WHEN mm = '07' THEN tot_qty ELSE 0 END) m07
, SUM(CASE WHEN mm = '08' THEN tot_qty ELSE 0 END) m08
, SUM(CASE WHEN mm = '09' THEN tot_qty ELSE 0 END) m09
, SUM(CASE WHEN mm = '10' THEN tot_qty ELSE 0 END) m10
, SUM(CASE WHEN mm = '11' THEN tot_qty ELSE 0 END) m11
, SUM(CASE WHEN mm = '12' THEN tot_qty ELSE 0 END) m12
, SUM(tot_qty) AS tot
, SUM(ROUND( (tot_qty / 12), 0)) AS ave
FROM CTE_GOOD p
GROUP BY p.itemcod, p.colorcd
UNION ALL
SELECT p.itemcod, p.colorcd
, '3. 불량 수량' AS clause
, SUM(CASE WHEN mm = '01' THEN bad_qty ELSE 0 END) m01
, SUM(CASE WHEN mm = '02' THEN bad_qty ELSE 0 END) m02
, SUM(CASE WHEN mm = '03' THEN bad_qty ELSE 0 END) m03
, SUM(CASE WHEN mm = '04' THEN bad_qty ELSE 0 END) m04
, SUM(CASE WHEN mm = '05' THEN bad_qty ELSE 0 END) m05
, SUM(CASE WHEN mm = '06' THEN bad_qty ELSE 0 END) m06
, SUM(CASE WHEN mm = '07' THEN bad_qty ELSE 0 END) m07
, SUM(CASE WHEN mm = '08' THEN bad_qty ELSE 0 END) m08
, SUM(CASE WHEN mm = '09' THEN bad_qty ELSE 0 END) m09
, SUM(CASE WHEN mm = '10' THEN bad_qty ELSE 0 END) m10
, SUM(CASE WHEN mm = '11' THEN bad_qty ELSE 0 END) m11
, SUM(CASE WHEN mm = '12' THEN bad_qty ELSE 0 END) m12
, SUM(bad_qty) AS tot
, SUM(ROUND( (bad_qty / 12), 0)) AS ave
FROM (SELECT p.itemcod, p.colorcd, p.mm
, ISNULL(b.bad_qty,0) AS bad_qty
FROM CTE_GOOD p
LEFT JOIN CTE_BAD b ON b.itemcod = p.itemcod
AND b.colorcd = p.colorcd
AND b.mm = p.mm
) p
GROUP BY p.itemcod, p.colorcd
) s
LEFT JOIN prbsms01 i ON i.itemcod = s.itemcod
LEFT JOIN sycmms01 r ON r.major = 'CK' AND r.minor = i.carcode
LEFT JOIN sycmms01 c ON c.major = 'CL' AND c.minor = s.colorcd
ORDER BY ISNULL(i.carcode,'zzzzzzzzzz'), s.itemcod, s.colorcd, s.clause
|
