database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 6960 게시물 읽기
No. 6960
쿼리 최적화 및 속도 개선의 도움이 필요합니다.
작성자
redical
작성일
2016-03-21 12:21ⓒ
2016-03-21 15:38ⓜ
조회수
4,495

아래 쿼리문 중에서 총 생산량 수행시 "양품 생산 정보" 와 "불량 생산 정보" 테이블을 한번더 수행함에 있어 속도가 너무 느립니다.

고수님의 조언을 구하고자 합니다.

 

작업 환경

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

 

이 글에 대한 댓글이 총 4건 있습니다.

조인 조건이 이상합니다.
양품과 불량품간의 조인 조건이
1. 총 생산량 에서는 itemcod 만 조인 조건 걸었고
3. 불량 수량 에서는 itemcod 와 colorcd 를 조인 조건 걸었네요.
둘다 itemcod 와 colorcd 로 조인 걸어야 하는 것 아닌지?


또한 itemcod 와 colorcd 로 조인 하더라도?
mm 조건만으로 조인한다면 중복이 발생되지 않나요?
itemcod 와 colorcd 에 대해서 월별 1건씩의 데이터만 존재하지는 않을 듯 한데요?
 

마농(manon94)님이 2016-03-21 15:57에 작성한 댓글입니다.

마농님 갑사합니다.

조인조건에서 colorcd를 빠뜨렸네요..

itemcod, colorcd, mm 동시에 조인해야 합니다.

---------------------------------------------------------------

품목 색상 총계 평균 1월 2월 3월 4월 5월

---------------------------------------------------------------

aaa NAVY 165 33 11 22 33 44 55

이런 양식으로 결과를 나타냅니다.

 

 

그래도 속도 개선이 안되네요...

항상 감사합니다.

좋은하루 되세요...

Redical님이 2016-03-21 16:19에 작성한 댓글입니다.
이 댓글은 2016-03-21 16:32에 마지막으로 수정되었습니다. Edit

SELECT r.name    AS carkind
     , s.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 itemcod, colorcd
             , ISNULL(clause, '1. 총 생산량') clause
             , SUM(qty) tot
             , ROUND(SUM(qty)/12., 0) ave
             , ISNULL(SUM(CASE mm WHEN '01' THEN qty END), 0) m01
             , ISNULL(SUM(CASE mm WHEN '02' THEN qty END), 0) m02
             , ISNULL(SUM(CASE mm WHEN '03' THEN qty END), 0) m03
             , ISNULL(SUM(CASE mm WHEN '04' THEN qty END), 0) m04
             , ISNULL(SUM(CASE mm WHEN '05' THEN qty END), 0) m05
             , ISNULL(SUM(CASE mm WHEN '06' THEN qty END), 0) m06
             , ISNULL(SUM(CASE mm WHEN '07' THEN qty END), 0) m07
             , ISNULL(SUM(CASE mm WHEN '08' THEN qty END), 0) m08
             , ISNULL(SUM(CASE mm WHEN '09' THEN qty END), 0) m09
             , ISNULL(SUM(CASE mm WHEN '10' THEN qty END), 0) m10
             , ISNULL(SUM(CASE mm WHEN '11' THEN qty END), 0) m11
             , ISNULL(SUM(CASE mm WHEN '12' THEN qty END), 0) m12
          FROM (SELECT itemcod, colorcd
                     , '2. 양품 수량'           AS clause
                     , SUBSTRING(prodate, 5, 2) AS mm
                     , SUM(tot_qty)             AS qty
                  FROM praotr01
                 WHERE prodate BETWEEN '20150101' AND '20151231'
                   AND tot_time > 0
                 GROUP BY itemcod, colorcd, SUBSTRING(prodate, 5, 2)
                 UNION ALL
                SELECT itemcod, colorcd
                     , '3. 불량 수량'           AS clause
                     , SUBSTRING(prodate, 5, 2) AS mm
                     , SUM(bad_qty)             AS qty
                  FROM praotr04
                 WHERE prodate BETWEEN '20150101' AND '20151231'
                   AND bad_qty > 0
                 GROUP BY itemcod, colorcd, SUBSTRING(prodate, 5, 2)
                ) p
         GROUP BY GROUPING SETS ( (itemcod, colorcd, clause)
                                , (itemcod, 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
;

마농(manon94)님이 2016-03-21 16:27에 작성한 댓글입니다.
이 댓글은 2016-03-21 16:28에 마지막으로 수정되었습니다.

와우 ~~~ 마농님 감사합니다.

GROUP BY GROUPING SETS 이라는 새로운 함수 하나 배워 갑니다.

오라클에도 적용 가능한지 테스트 해보아야 겠네요..

 

항상 정성스런 댓글과 문제해결에 최선을 다하는 모습이 아름답습니다.

좋은 하루 되세요...

Redical님이 2016-03-22 15:24에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
6964단위에 따라서 금액 소수점을 표시하려고 합니다. [2]
선이
2016-04-04
3941
6963암호화 질문입니다.
우와
2016-03-30
3651
6961ODBC 이용 시 문제점..
초보DBA
2016-03-23
3838
6960쿼리 최적화 및 속도 개선의 도움이 필요합니다. [4]
redical
2016-03-21
4495
6959분포도가 좋은 random 값을 생성하고 싶습니다. [1]
...
2016-03-19
4017
6958MS사의SQL 서버 PDW 함수 질문
김민석
2016-03-04
4043
6956mysql 설치 질문입니다~
qweasd
2016-02-22
4148
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.048초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다