|
|
|
|
| 게시판 페이징 |
 |
|
|
| 글쓴이 : 바람공주
날짜 : 05-06-03 13:17
조회 : 5726
|
|
 http://cafe.naver.com/q69/6374 (725) | |
|
|
|
SQL 문장은 모든 건수를 select하는데, rs.next()를 통해 해당 페이지까지 skip한 후
해당 페이지 부터 rs.getString()을 통해 실제 데이타를 꺼내 오는 경우지요..
이렇게도 하시면 안됩니다. 말씀드렸다 시피 rs.next()는 단순한 조건 체크가
아니라 물리적으로 DB와 N/W을 경유한 체크가 일어납니다. 즉, 10만건이라면
단 10건의 한 페이지만을 읽기를 원하시더라도 몇십분이 걸리는 작업이 되고
맙니다.
결국, SQL문장 자체에서 이미 page개념이 들어가서 SQL문장이 만들어 져야 합니다.
rownum을 사용하든, sub select를 사용하든, 무슨 방법을 동원해서라도 page개념이
들어간 sql 문장이 만들어져서, 필요한 분량만큼만 select가 일어나야 합니다.
(만들기가 곤란할 때도 있습니다. 그래도 만들어 내야 합니다.)
----------------------------------------------------------------------
10개씩 뿌리는 기법
방법 하나 : ROWNUM이용
SQLJ코드중 SQL부분입니다.
#sql xxx = {
select rn.rnum, empno, ename, job, mgr, hiredate, sal, comm. deptno
from ( select rownum as rnum, empno, ename, job, mgr, hiredate, sal, comm, deptno
from emp where rownum <= :last_row) rn where(rn.rnum >=:first_row);
};
위의 퀴리가 내장된 메소드를 이용하면 되겠군요.
방법둘
10개씩 끊어서 보여줄때 11개를 SELECT 하되 10개만(rownum이나 fetch first 10 row only)
보여주고 11번째는 다음번 페이지할때
키값으로 사용한다. 따라서 다음페이지를 보여줄때 11번째의 키값을 가지고 또다시 11개만
SELECT 한다.(물론 소트가 된다고 가정한다.)
위의 2가지 방법을 적절히 이용하여 트랜젝션단위로 빈을 만든다면
DB의 부하가 줄어들것으로 생각됩니다.
----------------------------------------------------------------------
DB 질의 결과를 Vector나 Array 등에 담아두는 경우,
또 한가지 큰 문제에 부딪힐 수 있죠.
JAVA에서 생각하는 메모리는 Runtime.totalMemory()에 의해 반환되는
JAVA Heap 메모리만이 아닌 경우가 대부분입니다.
실제 JAVA Process가 사용하는 메모리는 이것보다 큰데,
이유중의 하나는 JAVA의 gc가 연속되지 않은 부분의 free memory를 OS에
반납하지 못하거나 OS에 반납했지만 OS가 바쁘다는 핑계로
(또는 바쁘진 않아도 속도를 높이자는 취지) 거둬들이지 않기 때문에
발생하는 것으로 보입니다.
이 현상은 실제로 프로그램에서 메모리 관리를 정확히 했음에도 발생하는
문제이기 때문에 개발자가 적당히 피해가야만 합니다.
그 중 대표적인 것이 Vector 등의 자료구조에 DB 질의 결과를 꽉 채워
놓은 다음 나중에 쓰고 버리는 경우입니다.
이렇게 하면 덩치가 큰 메모리 영역이 발생하고 그것이 gc 될 때도
OS에 반납하기 힘들어지는 결과를 초래합니다.
결국 JVM의 메모리 점유량은 점점 늘어나서 죽게되는 경우도 있습니다.
----------------------------------------------------------------------
답변을 제외하고 10개(지정 가능)씩 게시물을 짤라서
다음 처럼 페이지뷰를 보여주는 게시판이죠..
이전페이지 [-5P] [6] [7] [8] [9] [10] [+5P] 다음페이지
실제로,
현재 1800건의 게시물이 넘어서고 있는 상황에서
전체 게시물을 선택하고 페이지를 보여주었을때
user입장에서.. 10초이상 걸리던 것이.
위와 같이 프로그램한 페이지에서는 1~2초 안에 결과가 나타납니다.
DATA가 많아질수록 그 차이는 커지겠죠? ㅋㅋ
간략히 쿼리 부분만 소개를 해 보면요..
select * from head_board
where ref < 1576
and ref >= (select min(ref)
from (select ref from head_board
where ref < 1576 group by ref order by ref desc )
where rownum <= 50
)
ORDER BY ref desc, ref_seq asc
1576번 이하의 50개 게시물을 선택하는 경우입니다.
(50개는 질문 기준입니다. 답변이 포함되믄 몇개가 될지 모르죠)
select min(ref)
from (select ref from head_board
where ref < 1576 group by ref order by ref desc )
where rownum <= 50
이 서브쿼리에서 질문기준번호(ref)로 50번째 게시물의 번호를 선택하게 되고,
select * from head_board
where ref < 1576
and ref >= (위~)
ORDER BY ref desc, ref_seq asc
여기서 순서대로 소팅을 하게 되죠.. ref_seq는 답변순서.
저기 1576이라는 기준번호하나를 잡는 규칙이나,
이전페이지와 다음페이지를 넘길때의 방법은 지금 저도 복잡합니다.
----------------------------------------------------------------------
제가 아는 방법중의 하나는 oracle 의 hint를 사용하는 방법입니다.
가령
SELECT article_id,title,date FROM articles
와 같은 table이 있다면,
SELECT * FROM (
SELECT /*+ INDEX(articles 정렬하고 싶은 인덱스명) */
article_id, title,date ceil( rownum / 한화면당 갯수 ) as page
FROM articles
WHERE 조건들....
) WHERE page = 출력하고 싶은 페이지번호
이런 식입니다.
즉 정렬은 hint를 써서 인덱싱으로 정렬하여 rownum이 흐트러지는 것을 막습니다.
INDEX_DESC 로 명령을 바꾸면 역으로 정렬되겠죠.
참고로 ceil이라는 함수는 ceil(1/3) = 1, ceil(2/3) = 1, ceil(3/3) = 1,ceil(4/3) = 2
와 같은 결과를 나타냅니다.
즉 rownum이 같 게시물의 순서이고 ceil(rownum/한페이지당 갯수)은 해당 페이지번호가 되지요.
이 방법은 쿼리가 간단하고 속도가 무엇보다도 빠르다는 것입니다.
단 오라클에서만 가능하고 정렬을 하기위해서는 인덱스를 만들어야 하는 문제가 있지만,
대부분은 키값으로 생성하면서 생성된 인덱싱만 이용하는 경우가 대부분이므로
큰 문제는 없다고 생각됩니다.
----------------------------------------------------------------------
이번에는 걍 호기심으로 postgreSQL을 써 보기로 했는데,
메뉴얼을 좀 들추다 보니... 다른 db에서 그토록 초보개발자의 머리를
뽀개던 그 쿼리가 간단하게 되도록 되어 있더군요.
다른 DB에는 있는지 몰겠는데... select할 때 limit란 걸 쓸 수가 있더라구요.
그래서...select 문에 limit 를 써주면 몇 번째 줄부터 몇 개나 돌려줄지
정할 수가 있네요.
예를 들어
1, 2, 3, 4, 5
란 데이타가 있을 때
select * from table where field > 1 limit 2,1 이라고 하면
3, 4
가 출력되지요...1보다 큰 2,3,4,5중에 첫 번째 줄 2를 빼고 나머지 중
2줄을 출력하는 겁니다.. 간단하지요.. 다른 db에도 이런 기능이 있을까요?
커넥션만 제외하고 나머지는 표준만 쓸려면 이걸 못 쓰겠지만...
----------------------------------------------------------------------
와우 ceil이라는 함수가 있었군요..
힌트부분에 있어서는요..
저희는.. ref(게시물번호)는 desc로, 그리고 ref_seq(답변순서)는 asc로 정렬하는 index를
힌트로 줘 봤는데 에러 나더라구요..
desc 로 인덱싱하는 것(function based index로 취급??)에서 문제가 있는 듯..
(Oracle 8.1.5)
정말 둘다 asc로 인텍싱하고 힌트처리했을때는 잘 되구요..
ceil은 안써봤는데.. 함 만들어 봐야겠네요..
/////////////////////////////// 질문갯수 중심 10개씩..///////////////////////
SELECT ref, ref_seq, (select ceil( count(distinct ref)/20) from head_board where bulletin_num='web') mp
FROM head_board
WHERE ref in (SELECT ref FROM ( SELECT ref, ceil(rownum/20) page
FROM (SELECT DISTINCT ref
FROM head_board
WHERE bulletin_num='web'
ORDER BY ref DESC
))
WHERE page = 50
)
AND bulletin_num='web'
ORDER BY ref DESC, ref_seq ASC
/////////////////////////////// 질문, 답변 구분없이 무조건 10개씩..///////////////////////
SELECT ref, ref_seq, page, (select max(ceil(rownum/10)) from head_board where bulletin_num='web') mp
FROM ( SELECT ref, ref_seq, ceil(rownum/10) page
FROM (SELECT ref, ref_seq
FROM head_board
WHERE bulletin_num='web'
ORDER BY ref DESC, ref_seq ASC
)
)
WHERE page = 1
** mp는 total page 수 입니다. 필요할 것 같아서.. **
----------------------------------------------------------------------
흠.. ceil이란 함수가 있었네여.. ^^;
제가 전에 해본거 역시 rownum을 사용한건데..
거의 비슷한거 같아서 올립니다..
위의 ceil을 사용하면... 페이지 번호를 가지고 다니는 경우겠구여..
rownum을 사용하면.. 글 번호를 가지구 다니는 경우구여..
머 내용은 비슷???
SELECT seq, title, 기타등등, rnum
FROM (SELECT seq, title, rownum rnum
FROM 게시판 테이블명
WHERE 조건 ..
ORDER BY 정렬컬럼
)
where rnum between 시작 글번호 and 시작글번호 + 페이지에뿌릴 갯수
음.. 이렇게 하믄 비스므리~ 하겠져..
속도는 별 차이 못느낌다.. 단지.. 배열이나 벡터에..
필요한 데이터를 넣을수 있다는 것 이외에..
하지만.. 그래두 그게 처리하는 시스템에 입장에서는 좋겠져???
----------------------------------------------------------------------
님의 글대로 hint를 써서 query를 해봤지만 정렬이 되지 않는데요.
SELECT * FROM (
SELECT /**INDEX_DESC(DB07 DB07_PRIMARY_PK)*/
DB07_REQ_NO,rownum,ceil(rownum/10) AS PAGE
FROM DB07
WHERE DB07_REQ_NO LIKE '2000%'
)
WHERE PAGE=2
WHERE 절의 DB07_REQ_NO는 PK로 잡혀 있습니다.
혹시 오라클 버젼문제인가요?
저는 오라클 8.0.5를 사용합니다.
참고로 이버젼에서는 SUBQUERY에서 ORDER BY 를 사용할 수 없더군요.
hint를 쓴는 방법이 있다는걸 알고 눈이 번쩍 띄였는데
좀 도와 주세요........
----------------------------------------------------------------------
1. Oracle에서 10개씩 데이타 가져오기
select * from (
select * from (
select record_number, record_no, record_depth, record_depthno, record_title, record_writer, writer_email, to_char(record_date,'yyyy-mm-dd'), read_count
from kpa_pm2000_qna where delete_flag='1' order by record_no desc, record_depth
) where rownum <= 100 order by rownum desc
) where rownum <= 10 order by rownum desc
----------------------------------------------------------------------
제목 [oracle] 쿼리속도 향상, 효율적...
실무에서 해결한 문제를 정리해 올립니다.
제가 유지보수를 맡게된 사이트에서 답변형 게시판의 문제를 해결하는데 사용한
쿼리입니다. 게시물 수가 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(2,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(2,pageSize);
하시면 됩니다.
참고로 테스트환경은
oracle 8.1.5 입니다.
8.0.5 에선 inline view에서 order by 가 안된다고 하네요.
다른 방법을 찾아봐야겠네요. ^^;
----------------------------------------------------------------------
이 근본적인 문제를 해결하는 방법은 보드캐슁 기법을 이용하는 방법입니다.
보드캐슁은 요즘 최신으로 제공되는 Connection Pool에서 SQL 캐슁과 비슷한
기법을 사용하는 방법입니다.
먼저 이를 이용하기 위해서는 쿼리를 단지 조건없이 모두 읽는 방법보다
어느정도 끊어서 읽을 필요가 있습니다.
즉, select first 10 user_id, user_name from test_board 와 같이 말입니다.
인포믹스, 오라클, MS SQL에서는 first 문을 이용해 원하는 개수만큼의
resultset을 얻을 수 있습니다.
아, mysql에서는 select user_id, user_name from test_board limit 10; 을
사용해야 합니다.
그렇다면 매번 이렇게 나눠서 읽어야 할까? 그건 아닙니다.
게시판이 변경(insert, delete)되지 않는다면, 최초 한번만 읽도록 하고
나머지 사람들은 해당하는 게시판의 정보(메모리상의 인스턴스를 말합니다)를
읽어내면 굳이 DB를 다시 읽어낼 필요가 없겠지요.
저의 경우에는 게시물에 대한 페이지 블럭 5개 정도를 메모리에 공유 인스턴스로
띄워서 게시물에 대한 정보를 나눠갖는 캐슁기법을 사용하여 개발하는 중입니다.
추가적으로, 게시물 첫 페이지에 대해서는 아예 표시내용을 벡터에 넣어서
역시 다른 사용자가 DB를 읽지 않고도 벡터값만 가져다 쓰도록 했습니다.
메모리에 대한 계산을 해봤는데, 페이지 블럭 5개 정도면 32kb 밖에 차지하지
않으며, 첫페이지에 대한 벡터 사이즈도 미미할정도입니다.
이런 캐슁기법을 사용하지 않은 게시판은 게시물이 대략 10000건 이상인 경우
심각한 속도저하를 가져오는게 대다수입니다.
특히, 테이블을 1개만 사용하고 여러개의 게시판이 공유하는 경우에는
더욱 심각한 상황이 벌어질 수 밖에 없겠죠...
그리고 인덱스의 중요성을 꼭 알아두시길 바랍니다.
인덱스를 거는 것이랑 아닌것이랑 속도차이는 게시물 수가 증가할 수록
엄청나게 차이가 납니다. (단지 수치적으로 몇십배 이상이 되기도 합니다.)
인덱스를 거는 요령은 where절에서 어떤 필드값을 주로 사용하는지가 포인트이며
보통 2-3개 정도의 필드를 묶어서 cluster index를 생성시킵니다.
그냥 인덱스를 거는것보다 cluster index를 생성하는게 최상의 방법입니다.
쩝... mysql에서는 보통 인덱스만 지원합니다...
(제 개인적으로 mysql을 굉장히 좋아합니다. 작고 파워풀한게 정말 맘에 듭니다.
그러나 기능/성능으는 오라클이나 MS SQL이 더 낫다고 인정해야겠죠...)
----------------------------------------------------------------------
1. 오라클 rownum 사용한 10개 게시물 가져오기
오라클에서는 select 한 결과에 자동적으로 rownum 이라는 숫자가 붙습니다.
그래서 이 rownum 함수를 이용하면 db에서 끊어서 query를 해 낼 수 있습니다.
아래있는 Vector를 이용한 것보다 db 조회수가 현저하게 낮아지죠.
Vector는 DB와 java 가 계속 data를 주고받다가 10개를 골라오는 것이고,
지금 이 쿼리는 DB에서 10개를 선택한 뒤에 자바에게 주는 것입니다.
// totcnt 는 총 게시물의 수 "select * from board where board_gbn='" + boardGbn + "'" 에서 나온 int 값
int pageSize = 10, curPage, totPage;
if (reqpage == null) {
curPage = 1;
} else {
curPage = Integer.parseInt(reqpage);
}
totPage = ((totcnt - 1)/ pageSize) + 1;
int lastNum = totcnt - (curPage-1) * pageSize;
sqlstr = "SELECT * FROM board WHERE board_gbn = '" + boardGbn + "' AND rownum <= " + lastNum ;
sqlstr = sqlstr + " order by board_no DESC";
sqlstr = "SELECT * FROM ("+sqlstr+") WHERE ROWNUM <= "+pageSize+"";
out.println(sqlstr);
rs1 = conn.executeQuery(sqlstr);
....
Query 를 출력해 보시면 대략 이해가 가실 겁니다.
----------------------------------------------------------------------
|
|
|
|
Copyright ⓒ hompy.info. All rights reserved.
