데이터베이스 질문과 답변
rownum 이 증가 하면 쿼리 속도가 느려지는데 어떻식으로 접근해야할까요?
1
8
7,349
by 만년설 [SQL Query] 최적화 튜닝 rownum 속도 [2015.06.09 21:43:40]
SELECT * FROM (SELECT RESULT_LIST.*, ROWNUM AS RNUM FROM( SELECT USERS.ORG_CODE , ORG.ORG_NM , COMT.AUTHOR_NM , COMT.AUTHOR_CODE , USERS.MEMBER_ID , USERS.MEMBER_PWD , USERS.IHIDNUM , USERS.REAL_AUTH_YN , USERS.REAL_AUTH_ORG , USERS.USE_YN , USERS.USER_ORG_YN , USERS.INFO_AGREE_YN , USERS.FIRST_NM , USERS.LAST_NM , USERS.DMST_YN , USERS.LAST_NM|| FIRST_NM AS USER_NM , USERS.ENG_FIRST_NM , USERS.ENG_LAST_NM , USERS.ENG_FIRST_NM ||ENG_LAST_NM AS ENG_USER_NM , USERS.PWD_QESTN , USERS.PWD_ANSWER , USERS.PWD_UPDT_DT , USERS.BRTH_DT , USERS.CLD_TYPE , USERS.DTY , USERS.POSTNO , USERS.BASE_ADRES , USERS.DTL_ADRES , USERS.TELNO , USERS.MOBILENO , USERS.LIVE_YN , USERS.EMAIL , USERS.EMAIL_RCV_YN , USERS.RS_MGR_YN , USERS.FRST_REGIST_PNTTM , USERS.APPRVL_YN , USERS.APPRVL_DT , USERS.USER_AMD_NO , USERS.ESNTL_ID , USERS.ATCH_FILE_ID , USERS.FRST_REGISTER_ID , USERS.LAST_UPDUSR_ID , USERS.MEM_STTUS_CODE , USERS.MEM_REGIST_SE , USERS.RWENP_YN , USERS.USR_TYPE , USERS.LAST_UPDT_PNTTM , USERS.REAL_AUTH_SE , USERS.REAL_AUTH_FAIL_REASON FROM USERS USERS INNER JOIN ORG ORG ON ORG.ORG_CODE = USERS.ORG_CODE LEFT OUTER JOIN (SELECT BB.ESNTL_ID AS ESNTL_ID , LISTAGG(AA.AUTHOR_CODE, ', ') WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_CODE, LISTAGG(AA.AUTHOR_NM, ', ' ) WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_NM FROM COMTNAUTHORINFO AA INNER JOIN COMTNEMPLYRSCRTYESTBS BB ON AA.AUTHOR_CODE = BB.AUTHOR_CODE GROUP BY BB.ESNTL_ID ) COMT ON COMT.ESNTL_ID = USERS.ESNTL_ID ORDER BY ESNTL_ID desc ) RESULT_LIST WHERE ROWNUM <= '129180') WHERE RNUM > '129160'
위에 처럼 실행하면 Execute Time : 193645ms 걸림니다 ......
SELECT * FROM (SELECT RESULT_LIST.*, ROWNUM AS RNUM FROM( SELECT USERS.ORG_CODE , ORG.ORG_NM , COMT.AUTHOR_NM , COMT.AUTHOR_CODE , USERS.MEMBER_ID , USERS.MEMBER_PWD , USERS.IHIDNUM , USERS.REAL_AUTH_YN , USERS.REAL_AUTH_ORG , USERS.USE_YN , USERS.USER_ORG_YN , USERS.INFO_AGREE_YN , USERS.FIRST_NM , USERS.LAST_NM , USERS.DMST_YN , USERS.LAST_NM|| FIRST_NM AS USER_NM , USERS.ENG_FIRST_NM , USERS.ENG_LAST_NM , USERS.ENG_FIRST_NM ||ENG_LAST_NM AS ENG_USER_NM , USERS.PWD_QESTN , USERS.PWD_ANSWER , USERS.PWD_UPDT_DT , USERS.BRTH_DT , USERS.CLD_TYPE , USERS.DTY , USERS.POSTNO , USERS.BASE_ADRES , USERS.DTL_ADRES , USERS.TELNO , USERS.MOBILENO , USERS.LIVE_YN , USERS.EMAIL , USERS.EMAIL_RCV_YN , USERS.RS_MGR_YN , USERS.FRST_REGIST_PNTTM , USERS.APPRVL_YN , USERS.APPRVL_DT , USERS.USER_AMD_NO , USERS.ESNTL_ID , USERS.ATCH_FILE_ID , USERS.FRST_REGISTER_ID , USERS.LAST_UPDUSR_ID , USERS.MEM_STTUS_CODE , USERS.MEM_REGIST_SE , USERS.RWENP_YN , USERS.USR_TYPE , USERS.LAST_UPDT_PNTTM , USERS.REAL_AUTH_SE , USERS.REAL_AUTH_FAIL_REASON FROM USERS USERS INNER JOIN ORG ORG ON ORG.ORG_CODE = USERS.ORG_CODE LEFT OUTER JOIN (SELECT BB.ESNTL_ID AS ESNTL_ID , LISTAGG(AA.AUTHOR_CODE, ', ') WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_CODE, LISTAGG(AA.AUTHOR_NM, ', ' ) WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_NM FROM COMTNAUTHORINFO AA INNER JOIN COMTNEMPLYRSCRTYESTBS BB ON AA.AUTHOR_CODE = BB.AUTHOR_CODE GROUP BY BB.ESNTL_ID ) COMT ON COMT.ESNTL_ID = USERS.ESNTL_ID ORDER BY ESNTL_ID desc ) RESULT_LIST WHERE ROWNUM <= '20') WHERE RNUM > '0'
그런데 위에 쿼리 돌리면 Execute Time : 81ms 이걸림니다.
그리고 이상한거 sqlDeveloper 로 첫번째 쿼리 돌리면 100ms 정도 밖에 안걸리던데 왜그런걸까여...
해결하려면 어떤식으로 접근해야할까요? 부탁드리겟습니다.
by 아발란체
[2015.06.09 22:16:30]
by 만년설
[2015.06.10 09:33:01]
감사합니다
by jkson
[2015.06.10 08:00:41]
위의 쿼리는 129180번째까지 데이터를 찾아야 뿌려줄 수 있으니 당연히 20번째까지만 찾아도 되는 아래 쿼리보다 느립니다. sqldeveloper로 돌렸을 때 빨리 나오는 것은 sqldeveloper 자체 옵션에 특정 row까지 먼저 가지고 오게 세팅이 되어있거나 첫번째 돌린 쿼리 데이터 집합이 서버에 남아있는 상태라 빠르게 나올 수 있겠죠. 이 경우에는 flush 해주면 두번 째 돌려도 느릴 겁니다.
by 만년설
[2015.06.10 09:33:11]
감사합니다
by 마농
[2015.06.10 08:43:22]
1. 조인 후 페이징 > 페이징 후 조인
위 쿼리에서 org 와 comt 의 역할은 단순 추가정보 조회 정도밖에 없습니다.
이러한 테이블은 일단 조인부터 하는게 아닙니다.
페이징 처리가 완전히 끝 난 뒤에 한페이지 분량만 조인하면 됩니다.
2. 조인을 스칼라 서브쿼리로
comt 의 그룹바이는 전체집합을 대상으로 합니다. 일량이 엄청나죠
이 조인을 스칼라 서브쿼리로 올려서 페이징된 20건만 처리하도록 변경하세요.
컬럼이 두개라 스칼라 2번 쓰면 총 40번 수행되겠네요.
하지만 전체를 모두 읽어 그룹바이하는것 보다는 나을 수 있습니다.
by 만년설
[2015.06.10 09:34:40]
SELECT USERS.* ,
(SELECT LISTAGG(AA.AUTHOR_CODE, ', ') WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_CODE
FROM COMTNAUTHORINFO AA INNER JOIN COMTNEMPLYRSCRTYESTBS BB
ON AA.AUTHOR_CODE = BB.AUTHOR_CODE
WHERE ESNTL_ID = USERS.ESNTL_ID
GROUP BY BB.ESNTL_ID) AS AUTHOR_CODE,
(SELECT LISTAGG(AA.AUTHOR_NM, ', ') WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_NAME
FROM COMTNAUTHORINFO AA INNER JOIN COMTNEMPLYRSCRTYESTBS BB
ON AA.AUTHOR_CODE = BB.AUTHOR_CODE
WHERE ESNTL_ID = USERS.ESNTL_ID
GROUP BY BB.ESNTL_ID) AS AUTHOR_NM
FROM (SELECT RESULT_LIST.*, ROWNUM AS RNUM FROM(
SELECT
USERS.ORG_CODE
, ORG.ORG_NM
, USERS.MEMBER_ID
, USERS.MEMBER_PWD
, USERS.IHIDNUM
, USERS.REAL_AUTH_YN
, USERS.REAL_AUTH_ORG
, USERS.USE_YN
, USERS.USER_ORG_YN
, USERS.INFO_AGREE_YN
, USERS.FIRST_NM
, USERS.LAST_NM
, USERS.DMST_YN
, USERS.LAST_NM||
FIRST_NM AS USER_NM
, USERS.ENG_FIRST_NM
, USERS.ENG_LAST_NM
, USERS.ENG_FIRST_NM ||ENG_LAST_NM AS ENG_USER_NM
, USERS.PWD_QESTN
, USERS.PWD_ANSWER
, USERS.PWD_UPDT_DT
, USERS.BRTH_DT
, USERS.CLD_TYPE
, USERS.DTY
, USERS.POSTNO
, USERS.BASE_ADRES
, USERS.DTL_ADRES
, USERS.TELNO
, USERS.MOBILENO
, USERS.LIVE_YN
, USERS.EMAIL
, USERS.EMAIL_RCV_YN
, USERS.RS_MGR_YN
, USERS.FRST_REGIST_PNTTM
, USERS.APPRVL_YN
, USERS.APPRVL_DT
, USERS.USER_AMD_NO
, USERS.ESNTL_ID
, USERS.ATCH_FILE_ID
, USERS.FRST_REGISTER_ID
, USERS.LAST_UPDUSR_ID
, USERS.MEM_STTUS_CODE
, USERS.MEM_REGIST_SE
, USERS.RWENP_YN
, USERS.USR_TYPE
, USERS.LAST_UPDT_PNTTM
, USERS.REAL_AUTH_SE
, USERS.REAL_AUTH_FAIL_REASON
FROM USERS USERS
INNER JOIN ORG ORG ON ORG.ORG_CODE = USERS.ORG_CODE
ORDER BY
ESNTL_ID desc
) RESULT_LIST WHERE ROWNUM <= '20') USERS WHERE RNUM > '0'
이렇게 하니까 Execute Time : 38ms
SELECT USERS.* ,
(SELECT LISTAGG(AA.AUTHOR_CODE, ', ') WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_CODE
FROM COMTNAUTHORINFO AA INNER JOIN COMTNEMPLYRSCRTYESTBS BB
ON AA.AUTHOR_CODE = BB.AUTHOR_CODE
WHERE ESNTL_ID = USERS.ESNTL_ID
GROUP BY BB.ESNTL_ID) AS AUTHOR_CODE,
(SELECT LISTAGG(AA.AUTHOR_NM, ', ') WITHIN GROUP (ORDER BY AA.AUTHOR_CODE) AS AUTHOR_NAME
FROM COMTNAUTHORINFO AA INNER JOIN COMTNEMPLYRSCRTYESTBS BB
ON AA.AUTHOR_CODE = BB.AUTHOR_CODE
WHERE ESNTL_ID = USERS.ESNTL_ID
GROUP BY BB.ESNTL_ID) AS AUTHOR_NM
FROM (SELECT RESULT_LIST.*, ROWNUM AS RNUM FROM(
SELECT
USERS.ORG_CODE
, ORG.ORG_NM
, USERS.MEMBER_ID
, USERS.MEMBER_PWD
, USERS.IHIDNUM
, USERS.REAL_AUTH_YN
, USERS.REAL_AUTH_ORG
, USERS.USE_YN
, USERS.USER_ORG_YN
, USERS.INFO_AGREE_YN
, USERS.FIRST_NM
, USERS.LAST_NM
, USERS.DMST_YN
, USERS.LAST_NM||
FIRST_NM AS USER_NM
, USERS.ENG_FIRST_NM
, USERS.ENG_LAST_NM
, USERS.ENG_FIRST_NM ||ENG_LAST_NM AS ENG_USER_NM
, USERS.PWD_QESTN
, USERS.PWD_ANSWER
, USERS.PWD_UPDT_DT
, USERS.BRTH_DT
, USERS.CLD_TYPE
, USERS.DTY
, USERS.POSTNO
, USERS.BASE_ADRES
, USERS.DTL_ADRES
, USERS.TELNO
, USERS.MOBILENO
, USERS.LIVE_YN
, USERS.EMAIL
, USERS.EMAIL_RCV_YN
, USERS.RS_MGR_YN
, USERS.FRST_REGIST_PNTTM
, USERS.APPRVL_YN
, USERS.APPRVL_DT
, USERS.USER_AMD_NO
, USERS.ESNTL_ID
, USERS.ATCH_FILE_ID
, USERS.FRST_REGISTER_ID
, USERS.LAST_UPDUSR_ID
, USERS.MEM_STTUS_CODE
, USERS.MEM_REGIST_SE
, USERS.RWENP_YN
, USERS.USR_TYPE
, USERS.LAST_UPDT_PNTTM
, USERS.REAL_AUTH_SE
, USERS.REAL_AUTH_FAIL_REASON
FROM USERS USERS
INNER JOIN ORG ORG ON ORG.ORG_CODE = USERS.ORG_CODE
ORDER BY
ESNTL_ID desc
) RESULT_LIST WHERE ROWNUM <= '129160') USERS WHERE RNUM > '129140'
이렇게 하니까 Execute Time : 333ms 나오네요 감사합니다 ~^^
by 마농
[2015.06.10 09:41:44]
org 도 위로 올려보세요...
by 만년설
[2015.06.10 09:55:10]
org 뺴니까 110 ms 나오네요
그런데 안쪽 쿼리 에서 정렬 조건이 필요해서 그대로 사용하려고 해요~ ^^
속도는 1/586 로 줄었으니까 만족해요 ㅋㅋㅋㅋ
) 버튼을 클릭하여 작성 하시면 됩니다.