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 5626 게시물 읽기
No. 5626
속도가 너무 느리네요
작성자
김정화(firefly81)
작성일
2010-04-12 01:17
조회수
5,757

 

아래 쿼리 속도향상 가능할까요

월별 마감데이터를 만들려고 하는데 1개월  insert 시 20분 정도가 걸립니다.

월별 TBL_MONTHLY 테이블 row 약 700만건 정도입니다. 

 


SELECT '200901'MM, CUSTID
,CASE WHEN GRADE1+GRADE2 IN('상승상승')                          THEN '1. 고속성장형'
      WHEN GRADE1+GRADE2 IN('유지상승','상승유지')               THEN '2. 저속성장형'
      WHEN GRADE1+GRADE2 IN('유지유지','상승하락','하락상승')    THEN '3. 유지형'
      WHEN GRADE1+GRADE2 IN('유지하락','하락유지')               THEN '4. 저속하락형'
      WHEN GRADE1+GRADE2 IN('하락하락')                          THEN '5. 고속하락형'
END 등급성장
FROM
(
SELECT A.CUSTID
, CASE WHEN A.grade ='5' AND B.grade BETWEEN '1' AND '4' THEN '상승'
       WHEN A.grade ='4' AND B.grade BETWEEN '1' AND '3' THEN '상승'
       WHEN A.grade ='3' AND B.grade BETWEEN '1' AND '2' THEN '상승'
       WHEN A.grade ='2' AND B.grade BETWEEN '1' AND '1' THEN '상승'
       WHEN A.grade ='1' AND B.grade = '1'  THEN '유지'
       WHEN A.grade ='2' AND B.grade = '2'  THEN '유지'
       WHEN A.grade ='3' AND B.grade = '3'  THEN '유지'
       WHEN A.grade ='4' AND B.grade = '4'  THEN '유지'
       WHEN A.grade ='5' AND B.grade = '5'  THEN '유지'
       WHEN A.grade ='1' AND B.grade BETWEEN '2' AND '5' THEN '하락'
       WHEN A.grade ='2' AND B.grade BETWEEN '3' AND '5' THEN '하락'
       WHEN A.grade ='3' AND B.grade BETWEEN '4' AND '5' THEN '하락'
       WHEN A.grade ='4' AND B.grade BETWEEN '5' AND '5' THEN '하락'
 END GRADE1
, CASE WHEN B.grade ='5' AND C.grade BETWEEN '1' AND '4' THEN '상승'
       WHEN B.grade ='4' AND C.grade BETWEEN '1' AND '3' THEN '상승'
       WHEN B.grade ='3' AND C.grade BETWEEN '1' AND '2' THEN '상승'
       WHEN B.grade ='2' AND C.grade BETWEEN '1' AND '1' THEN '상승'
       WHEN B.grade ='1' AND C.grade = '1'  THEN '유지'
       WHEN B.grade ='2' AND C.grade = '2'  THEN '유지'
       WHEN B.grade ='3' AND C.grade = '3'  THEN '유지'
       WHEN B.grade ='4' AND C.grade = '4'  THEN '유지'
       WHEN B.grade ='5' AND C.grade = '5'  THEN '유지'
       WHEN B.grade ='1' AND C.grade BETWEEN '2' AND '5' THEN '하락'
       WHEN B.grade ='2' AND C.grade BETWEEN '3' AND '5' THEN '하락'
       WHEN B.grade ='3' AND C.grade BETWEEN '4' AND '5' THEN '하락'
       WHEN B.grade ='4' AND C.grade BETWEEN '5' AND '5' THEN '하락'
 END GRADE2
FROM
(SELECT CUSTID,LEFT(grade,1)grade FROM TBL_MONTHLY WHERE MM = '200808' AND grade IS NOT NULL)A  JOIN
(SELECT CUSTID,LEFT(grade,1)grade FROM TBL_MONTHLY WHERE MM = '200811' AND grade IS NOT NULL)B ON A.CUSTID=B.CUSTID  JOIN
(SELECT CUSTID,LEFT(grade,1)grade FROM TBL_MONTHLY WHERE MM = '200901' AND grade IS NOT NULL)C ON A.CUSTID=C.CUSTID
)A
 

[Top]
No.
제목
작성자
작성일
조회
5629로긴 유저가 접근가능한 디비 정보 얻기
이창수
2010-04-12
6063
5628로긴 유저 이름 변경하는 방법
이창수
2010-04-12
5698
5627객체 권한 부여 여부 알아내기
이창수
2010-04-12
5884
5626속도가 너무 느리네요
김정화
2010-04-12
5757
5625[질문] DTS로 Oracle Data가져올때
이서리
2010-04-05
5655
5623[질문]DB백업을 DB서버가 아닌 다른서버에 백업되도록 가능한가요? [2]
이기자
2010-04-05
5981
5622MSSQL 완전초보질문입니다. [1]
이상범
2010-04-05
5942
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.019초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다