Scrollable ResultSet 에 관한 질문입니다.

[정필 장]

프로젝트를 진행하면서 데이터를 조회 할때 페이징에 관한 부분은 언제나 고민되는 부분입니다.

페이징을 구현할때 스크롤 가능한 ResultSet을 생성해서 커서를 이동시켜 구현하는 방법과

페이징된 데이터를 산출하도록 SQL 문을 작성하여 구현하는 두 방법들을 많이들 사용하실텐데요.

대용량 데이터를 조회하는데 있어서 첫번째 방법이 성능에 얼마만큼의 부하를 주게 되는지 궁금해서 질문 드립니다.

지금 새로 시작하는 프로젝트에서 페이징을 어떻게 구현해야 할지 논의를 하고 있는데요, 

개발 Framework은 Springframework 3.x 에 Mybatis 3 를 사용하고 있는데요. Mybatis의 RowBounds 를 이용해서 페이징된 데이터를 조회하는 방법이

첫번째 방법을 사용하고 있었습니다. (소스를 확인해보니 RowBounds의 offset값을 이용하여 rs.absolute() 메서드를 호출하여 커서를 이동시키더군요)

mybatis 의 RowBounds 기능을 이용해 페이징을 구현할까 생각중인데, 성능에 얼마만큼 영향을 미칠까 가늠하기가 어려워서 여러분들의 의견을 듣고 싶습니다.

[임병인]

성능을 수치화한 자료는 없지만 데이터베이스에 따라서 해당 기능이 엉뚱하게 동작하더군요. 일테면 MySQL이나 Oracle, DB2는 RowNum과 같은 기능이 있어서 스킵하는 기능이 있는데 반해, Sybase, MSSQL은 데이터를 몽창가져온다음 RS에서 이동하여 가져오더군요. 결국 데이터 패치량이 엄청난경우(저희의 경우 1억건 정도) 페이징하면 10분도 더 걸리는 현상이 발생했습니다. 단 10건을 가져오더라도 말이죠. 조인을 많이 하는경우만 아니라면 시퀀스를 이용한 sql을 작성하는게 좋을것 같구요. 어쨓든 저의 경우 페이징에서 스클롤러블은 피하고 싶은 선택이었습니다. 성능이 너무 차이가 나서요. 디비마다 조금 다르긴 하겠지만 mssql인 경우 또는 Sybase인경우는 비추합니다. 

Brian Lim iPhone에서 보냄

2012. 7. 4. 오후 5:19 "정필 장" <jup...@gmail.com> 작성:

--
Google 그룹스 'Korea Spring User Group' 그룹에 가입했으므로 본 메일이 전송되었습니다.
웹에서 이 토론을 보려면 https://groups.google.com/d/msg/ksug/-/hgCIbAH-t_QJ을(를) 방문하세요.
이 그룹에 게시하려면 ks...@googlegroups.com(으)로 이메일을 보내세요.
그룹에서 탈퇴하려면 ksug+uns...@googlegroups.com로 이메일을 보내주세요.
더 많은 옵션을 보려면 http://groups.google.com/group/ksug?hl=ko에서 그룹을 방문하세요.

[Sanghyuk Jung]

mysql의 경우는 좀 복잡한데요, fetchSize를 Integer.MIN_VALUE로 놓아야 한꺼번에 가지고 오지 않고, 인덱스가 걸린 컬럼에 range 조회 조건이 되어야지만 효과적인 조회가 됩니다..

사용하시는 DBMS에 따라서 DBA와 의논하셔야할듯합니다.

아니면 두가지 경우를 다 성능테스트 해보시고 실행시간과 DB서버의 부하를 동시에 보셔야할듯합니다.

2012년 7월 4일 오후 6:45, 임병인 <byl...@nextree.co.kr>님의 말:

[Sanghyuk Jung]

아, 위에 말씀드린 Mysql의 fetchSize는 주로 배치 쪽에서 jdbc cursor 바탕으로 한번 쿼리로 대용량 조회를 할 때를 말씀드린 것이였습니다.

자세히 읽어보니 질문 하신 분은  웹페이지에서  페이지를 이동할때의 구현 방식을 고민하시는 것 같네요..

mysql의 옵션과 관련해서 말이 나온김에 제가 전에 정리해 놓은  글을 공유해드립니다.. Spring batch와 관련된 내용이 약간 섞여 있지만 혹시나 도움이 되실분이 있을까하여서 붙여 넣습니다.

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

그러나 MySQL에는 fetchSize가 예상처럼 동작하지 않습니다. 아래 URL에 자세히 설명이 되어 있는데요,

* Framework Cursor (cursor in MySql) :   http://bleujin.tistory.com/152

* http://www.databasesandlife.com/reading-row-by-row-into-java-from-mysql/

 

 간단하게 정리하면, MySQL에서는 fetchSize를 지정해봤자 거기에 상관없이 한방에 다  전체 건을 보내줍니다.  

 꽁수로 fetchSize를 Integer.MIN_VALUE로 지정하고,   ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY 옵션으로 쿼리를 날리면 '스트리밍'으로 부분적으로 결과를 전송해주긴 합니다. Spring Batch의 JdbcCursorItemReader도 아래와 같이 기본적으로 ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY로 지정해서 PreparedStatement를 생성합니다.

 

preparedStatement = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

  그래서 JdbcCusorItemReader.setFetchSize(Integer.MIN_VALUE )만 지정되면 스트리밍 방식이 동작합니다.

  그러나 이렇게 해도 한번에 몇건씩 fetch해 주는지까지는 제어가 안 됩니다.  JDBC규약과도 어긋납니다. 

  java.sql.Statement의 javadoc에는 setFetchSize로 지정되는 값은 0보다 작으면 SQLException을 던진다고 적혀 있습니다. 그래서 이 방식을 계속 써도 될지도 불안하게 느껴집니다.

 

  위와 같은 한계와 때문에 Mysql에서 대용량 조회를 할 때는 쿼리 조건으로 일정건수가 넘지 않게 제약하는 방식을 많이 써왔습니다. Spring Batch에서는 JdbcCursorItemReader보다는  JdbcPagingItemReader, IbatisPagingItemReader은 페이징처리 클래스를 쓰는 것입니다.  

 

 페이징 로직처리를 해주는 추상화된 클래스를 만들어 놓으면, 반복해서 같은 로직을 구현하지 않아도 되기는해서, 개발에 공수가 크게 더 들어가지는 않습니다.

MySQL에서 useCursorFetch 옵션사용 

 

  얼마전에 MySQL에서도 5.0.2버전 이상에서는 useCursorFetch라는 옵션을 사용하면 DB에서 cursor를 사용해서 fetchSize가 인식된다는 걸 알게 되었습니다.  JDBC연결 url을 설정할 때 뒤에 useCursorFetch를 붙여주면 됩니다.

 

 

* MySQL을 JDBC로 연결할때 쿼리 결과를 여유있게 받기 :http://kang594.blog.me/40515882

* http://wiki.gxtechnical.com/commwiki/servlet/hwiki?Client+and+server+cursors+-+using+MySQL,

 

  이 옵션을 사용한다면 MySQL에서도 한번의 쿼리로 대용량 쿼리 조회가 가능해집니다.  그런데 DBA께 문의해보니 DB서버에서는 메모리에 커서를 유지해야되니 서버 쪽에서는 좀 부담이 될 수도 있다고 합니다. 그리고 성능에도 쿼리를 여러번 던지는 편이 더 유리하다고 하네요. DB장비의 상황과 조회조건의 index활용 여부에 따라서 결정해야겠지만, 아예 옵션이 없었던 때보다는 선택의 여지가 넓어졌습니다. 물론 아무리 fetchSize를 지정을 한다고 도  sqlMapClient.queryForList 같은 메소드로 모든 건을 다 List에 쌓는다면 당연히 메모리 걱정을 해야 합니다 ^^; 

2012년 7월 4일 오후 7:50, Sanghyuk Jung <ben...@gmail.com>님의 말:

[정필 장]

질문에 대한 답변 감사합니다. 

도움이 많이 됐습니다. 사실 최초에 질문했던 내용은 웹 페이지에서 데이터조회시 페이징에 관한 내용이었는데 스프링 배치에서 커서를 이용하는 내용에 대해서 새로운 사실을 알게되었습니다. 

지금 진행하는 프로젝트에서 MySql을 사용하는데 SpringBatch를 이용한 batch 작업도 포함되어 있거든요. ^^ 

링크해주신 자료 감사합니다~