제목 : [oracle] 쿼리속도 향상과 효율적인 게시판 쿼리
글쓴이: 허광남(heogn) 2001/05/13 03:39:44 조회수:13935 줄수:60 |
|
실무에서 해결한 문제를 정리해 올립니다.
제가 유지보수를 맡게된 사이트에서 답변형 게시판의 문제를 해결하는데 사용한
쿼리입니다. 게시물 수가 9000건을 넘습니다.
게시판 페이지를 가면 6~8초정도 기다려야 첫페이지가 나옵니다.
다음페이지로 이동할 때도 걸리는 시간은 마찬가지입니다.
DBA 와 같이 상의하고 테스트한 결과 중요한 차이점을 발견했습니다.
쿼리하는 컬럼을 지정하느냐 아니면 * 를 써서 모두 가져오느냐가 큰 성능의 차이를
보였습니다. 단순히 order by 가 들어가면 정렬(sorting) 때문에 느려지는 것으로 알고
있었는데 6초 이상 걸리던 쿼리가 단 0.2 초만에 해결되었습니다.
select *
from pm2000_qna order by record_no desc, record_depth
와
select record_number, record_no, record_depth, record_depthno, record_title,
record_writer, writer_email, to_char(record_date,'yyyy/mm/dd') record_date,
read_count
from pm2000_qna
order by record_no desc, record_depth
는 큰 차이라는 뜻입니다.
그리고,
효율적인 db접속을 위해서 게시물 몽땅 가져와서 rs.next() 로 돌려서 위치를 찾는 것
보다는 sql에서 게시물 수만큼 가져와서 뿌려주는 것이 나은 점에 대해서는 이 사이트
여러 곳에서 토론되고 있는 줄 압니다.
오라클에서는 rownum 이 있죠. 어거지로 쓰는 것 같지만 어쩔 수 없죠. 머리를 굴리는
수밖에요.
^^; 수고를 좀 덜어드리면 좋겠습니다.
select * from (
select * from (
select record_no, record_depth, record_depthno, record_title, record_writer,
writer_email, to_char(record_date,'yyyy/mm/dd') record_date, read_count
from pm2000_qna order by record_no desc, record_depth
) where rownum <= ? order by rownum desc
) where rownum <= ? order by rownum desc
jsp 에선
pstmt.setInt(1,(pageNo+1)*pageSize); // pageNo는 0부터..
pstmt.setInt(1,pageSize);
로 해주면 됩니다.
일단 sqlplus 에서 테스트 해보십시오.
아주 속이 시원하더군요.
부록으로 mysql 버전입니다.
select record_no, record_depth, record_depthno, record_title, ...
from pm2000_qna order by seq desc limit ?, ?
jsp에선
pstmt.setInt(1,pageNo*pageSize); // pageNo는 0부터..
pstmt.setInt(1,pageSize);
하시면 됩니다.
좋은 하루 되십시오.
---------------------------------
http://okjsp.pe.kr kenu
|
제목 : Re: [oracle] 쿼리속도 향상과 효율적인 게시판 쿼리
글쓴이: 이호한(miplus) 2001/05/15 01:33:33 조회수:3906 줄수:16 |
|
record_no와 record_depth에 결합인덱스를 만들면 더 빨라지겠군요.
그리고 가능하다면 record_no는
글이 등록될 때마다 값을 증가시키지 말고 감소시키면 더 빨라질겁니다.
예를 들어 0부터 시작해서 계속 1씩 빼는거죠.
0
-1
-2
-3
.
.
.
이렇게 하면 order by record_no desc 를 해 줄 필요없이 그냥 order by record_no하면 되죠.
이상 mysql에서도 사용하는 방법이었습니다.
|
제목 : Re: [oracle] 쿼리속도 향상과 효율적인 게시판 쿼리
글쓴이: 루키(guest) 2001/05/15 10:52:01 조회수:3574 줄수:21 |
|
제가 쓰는 query문 입니다. 이게 좀 더 빠르지 않을까요?
물론, index을 사용하는 방법이 최선의 방법입니다.
cursor_pos=((pagenum - 1) * NumberOfArticlePerPage) + 1;
pstmt=db.prepareStatement("select no,ndate,name,email,title,text,refer,recnt," +
"rede,rnum from (select /*+ index_desc(board BOARD_NO_PK) */ no,"+
"to_char(ndate,'YYYY-MM-DD AM HH24:MI:SS') as ndate,name,email,title," +
"text,refer,recnt,rede,rownum as rnum from board where sorting=0) "+
"where rnum between ? and ?");
pstmt.setInt(1,cursor_pos);
pstmt.setInt(2,(cursor_pos + NumberOfArticlePerPage -1));
물론, oracle에 국한된 방법입니다.
저는 일단 답변이 없는 게시물만 query를 먼저 하기때문에, where sorting=0 부분이
추가되었습니다.
답변글까지 한방에 처리할려면, sorting=0인 부분을 지우고, order by sorting를
사용해야 될것 같네요.
현재, 10000건이 넘는 게시물을 가지고 테스트하고 있지만, 매우(?) 만족할 수준의
속도입니다. 테스트 주소 : http://210.110.158.78/java/list.jsp
그럼...
|
제목 : Re: [oracle] 쿼리속도 향상과 효율적인 게시판 쿼리
글쓴이: 권용훈(Rainman1) 2001/05/15 10:52:22 조회수:4042 줄수:34 |
|
참고로 order by a desc, b에서 가장 큰 문제가 발생합니다.
앞에서 말씀하셨던 것처럼 desc를 우선 빼기 위해서 insert시에 max value에서 값을
감소시 키는 방식을 채택하셔야 하고, 여기에 index 힌트를 사용하심은 물론입니다.
그리고 위에서 결합인덱스를 말씀하셨는데, 실질적으로 결합인덱스를 걸어보시면
알겠지만, 오라클의 옵티마이저에 order by a desc, b는 원하는데로 쉬이 걸리지
않으실겁니다.
저도 여러번 시도는 해봤지만 제대로 않되는군요.
어쨌거나 이 알고리즘은 full scan을 타고, 부분적으로 query내용에 대해서 정렬을
해야하는 알고리즘입니다.
group id와 depth+sequence code의 알고리즘이 직관적이고 좋긴 하지만, 방법을 좀
달리하셔야 더 좋을 듯 합니다.
그 알고리즘이 대부분 10000건대에서 문제가 발생합니다. 해결하시는 분들은 query를
나누거나, 인덱스 걸고 해서 해결하지만, 역시나 수만건대에 문제에 부딪히게 됩니다.
근본적인 알고리즘 문제로 돌아가서, 사용자한테 보이기 위한 depth와 group code,
pmkey, partial scan을 위한 column등의 장치를 만들어 두는 방법도 나쁘지 않습니다.
hirarchical query에 대한 고찰을 했을 경우에는 tree가 커질수록 퍼포먼스는 뒷부분에서
상당히 떨어집니다. 예를들면 1-10000건 사이는 0.x초대이고, 수만건에서는 수초, 그
이상에서는 수십초 입니다.
인덱스와 query잘 엮어주면 성능은 말로 다 할 수 없을 만큼 개선이 되긴하지만, 문제도
있습니다.
connect by로 타고나가는 쿼리의 DB가 중간에 삭제로 구멍이 나면, 다시 이어줘야 한다거나,
중간부터 뒤나 앞으로 찾아나가는 경우, 그리고 DB가 특정로직에 얽메이게 된다는 점입니다.
여하튼 업데이트 없는 답변형 게시판의 초점은 partial scan을 위한 장치가 있다 없다입니다.
참고로 지금 사용하시는 알고리즘은 '없다'입니다.
알고리즘에 대한 고려나 db변환 작업이 불편할 경우에는 partial scan을 위한 컬럼을
하나 두셔서, 퍼포먼스를 높여보시기 바랍니다. 아니면 근본적으로 답변형이라는 것의
리스팅이 그리 좋은 케이스가 아니므로, otn의 q&a와 같이 해당하는 글에 관한 답변은
질문의 내용을 확인 할때에만 볼 수 있는 방식으로 취하는것도 하나의 방법이라 할 수
있겠네요..
|
제목 : 오라클8.1.5와 오라클8.1.6에서의 차이.....
글쓴이: 박정석(dspjs) 2001/06/15 11:26:59 조회수:2105 줄수:44 |
|
제가 프로그램을 하면서 이 쿼리를 이용해서 사용해 보니가.. 데이터 양이 아직
적어서 실제로 어는 정도 성능 향상을 가져왔는지는 확인을 하지 못했지만...
이 코드를 사용할 경우 오라클 버젼에 따라서.. 결과가 다르게 나오는 것을
알아냈습니다.
제가 처음 사용한 오라클은 8.1.5였는데.. 이 때에는 결과가 정상적으로 나오더군요
그런데.. 오라클 버젼을 8.1.6인 곳에서 같은 소스로 실행을 하니까.. select 된
결과가 .. 제대로 정열이 안되더군요...
즉, 오라클 8.1.6에서는 "order by rownum desc" 이 부분이 제대로 안되는것
같더군요...
그래서.. 오라클 버젼에 따라.. 따로 코드를 만들 수 없어서.. 저는 이 코드를
아래 처럼 수정해서 문제를 해결했습니다.
(이 코드는 제가 실제로 사용한 코드를 재 구성 한 것입니다.)
select DocumentNo, seqNum
from
(
select DocumentNO, rownum seqNum
from
(
select DocumentNo
from Board
order by CreateDate desc
)
where rownum <= ?
)
where seqNum >= ?
// pageNo는 1 부터
// listMax는 페이지에 보여줄 리스트 수
pstmt.setInt(1, pageNo * listMax);
pstmt.setInt(1, (pageNo-1)*listMax+1);
이렇게 수정하니까... 오라클 8.1.5와 8.1.6에서 동일한 결과가 나오더군요.
|
제목 : Re: 제가 TEST 해보니 않되는데요?
글쓴이: 이상원(guest) 2001/06/23 12:55:15 조회수:1438 줄수:5 |
|
저 역시 오라클에서 페이징하는것 때문에 고민을 하고 있는데요.
제가 알기론 SUBQUERY 에서는 ORDER BY를 사용할 수 없는걸로 알고 있거든요.
올려놓으신 SQL문을 실행하면 에러가 나는데
오라클의 경우가 맞는지 궁굼하네요.
혹시 제가 잘못알고 있으면 답변을 부탁드립니다.
|
제목 : Re: 오라클8.1.5와 오라클8.1.6에서의 차이.....
글쓴이: 박정석(dspjs) 2001/06/30 22:43:28 조회수:1844 줄수:64 |
|
저의 글을 읽어보고 테스트 하신뒤 에러가 발생하다고 하신 분이 계셔서
다시 글을 올립니다.
제가 올린 글의 쿼리문을 제가 다시 실행을 해보니까.. 아무런 문제가 없었고요
그리고.. 이 쿼리는 제가 실제로 프로젝트를 수행하면서 사용하고 있는 쿼리이기도
합니다.
물론 처음 글에서 말한데로 오라클은 8.1.6과 8.1.5에서 테스트 했는데..
아무런 에러 없이 결과가 나왔고요.. 아래는 실행 결과 입니다.
SQL> select DocumentNo, seqNum
2 from
3 (
4 select DocumentNO, rownum seqNum
5 from
6 (
7 select DocumentNo
8 from Order
9 order by CreateDate desc
10 )
11 where rownum <= 10
12 )
13 where seqNum >= 1
14 ;
DOCUMENTNO SEQNUM
---------------------------------------- ----------
20010630-2125 1
20010630-2124 2
20010629-2123 3
20010629-2122 4
20010629-2121 5
20010629-2120 6
20010629-2119 7
20010629-2118 8
20010629-2113 9
20010629-2114 10
10 rows selected.
혹시.. 그래도.. 이 쿼리가 안 되는 분이 있다면.. 버젼이 다를 경우
버전의 문제가 아닌가 생각됩니다.
=======================================================
추신 : 지금은 이 쿼리를 MS-SQL2000으로 수정하고 있는데 MS-SQL에는
오라클의 rownum같은 것이 없더군요...
그래서.. 임시 테이블과 identity와 저장 프로시저를 이용해서 해결했지요.
제가 다음주 부터 동원 예비군 훈련이라서 다녀오는데로
MS-SQL에서 해결한 방법도 글로 올리지요...
그럼 좋은 주말 되시고요.. 저는 오늘도 집에 가지 못할것 같군요...
|
제목 : Re: 오라클8.1.5와 오라클8.1.6에서의 차이.....
글쓴이: 서민구(4baf) 2001/11/15 15:07:31 조회수:1127 줄수:44 |
|
> 물론 처음 글에서 말한데로 오라클은 8.1.6과 8.1.5에서 테스트 했는데..
> 아무런 에러 없이 결과가 나왔고요.. 아래는 실행 결과 입니다.
> SQL> select DocumentNo, seqNum
> 2 from
> 3 (
> 4 select DocumentNO, rownum seqNum
> 5 from
> 6 (
> 7 select DocumentNo
> 8 from Order
> 9 order by CreateDate desc
> 10 )
> 11 where rownum <= 10
> 12 )
> 13 where seqNum >= 1
> 14 ;
>
> DOCUMENTNO SEQNUM
> ---------------------------------------- ----------
> 20010630-2125 1
> 20010630-2124 2
> 20010629-2123 3
> 20010629-2122 4
> 20010629-2121 5
> 20010629-2120 6
> 20010629-2119 7
> 20010629-2118 8
> 20010629-2113 9
> 20010629-2114 10
>
> 10 rows selected.
>
> 혹시.. 그래도.. 이 쿼리가 안 되는 분이 있다면.. 버젼이 다를 경우
> 버전의 문제가 아닌가 생각됩니다.
위에 적으신 서브쿼리내의 order by는 오라클 8까지는 지원이 안되며
오라클 8i이상부터 지원됩니다. 앞에 적으신분은 아마 오라클 8일겁니다.
오라클 8이하에서 서브쿼리내에 정렬을 하신다면 group by를 사용하여 order by
효과를 보는 수 밖에 없습니다.
-----------------------------------------------
OCP, SCJP
Software Engineer
Seo, Min-Koo(4baf@dreamwiz.com)
|
제목 : Re: [oracle] 쿼리속도 향상과 효율적인 게시판 쿼리
글쓴이: Neo.L(guest) 2001/12/03 01:31:37 조회수:1538 줄수:10 |
|
Order By record_no Asc 과 Order By record_no Desc 와 차이가 있을까요?
0 1 2 3 4 를 Desc로 정렬할때..
0 -1 -2 -3 -4 를 Asc로 정렬할때..
둘다 정렬횟수(시간) 똑같습니다..
ASC, DESC 라서가 아니라.. 정렬자체가 느린것이지요..
|
제목 : Re: Analysis function을 활용해 보시지요[오라클 제한]
글쓴이: fepest(guest) 2008/10/20 17:51:16 조회수:2643 줄수:19 |
|
SAMPLE SQL -> ibatis 수정본(핵심은 이것이 아니지만 이해 도움상)
[
SELECT *
FROM (
SELECT /*+ INDEX_DESC(A DTXDOCP) */
ROW_NUMBER() OVER(ORDER BY :ORDERBY) AS ROW_NO
, COUNT(*) OVER() as ROW_COUNT -->page계산용
, A.SEQ as SEQ_NO
, A.SITE_ID as SITE_ID
, A.TITLE as TITLE
, A.CONTNETS as CONTENTS
FROM DTXDOC A
WHERE A.SITE_ID = :SITE_ID
)
WHERE ROW_NO BETWEEN :STAR_ROW AND :END_ROW
]
|
|
