Whiteship's Note

[하이버네이트] @BatchSize로 쿼리 갯수 대폭 줄이기

모하니?/Coding : 2009.11.10 01:04


참조:
- http://docs.jboss.org/hibernate/core/3.3/reference/en/html/performance.html
- http://docs.jboss.org/hibernate/stable/annotations/api/org/hibernate/annotations/BatchSize.html

스터디의 회원은 각각 Collection<Study, Integer> 타입의 목록을 가지고 있습니다. 스터디당 참여율이나 신뢰도가 들어있는 콜렉션입니다. 그리고 스터디를 보여줄 때 각 회원들의 참여율과 신뢰도를 보여주도록 되어 있는데, 문제는 쿼리입니다.

Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id=?
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id=?

이런 쿼리가 회원 수*2 만큼 생성됩니다. 한 회원당 attendanceRate와 trustRate를 가지고 있기 떄문이죠. 바로 이런 경우 @BatchSize를 사용하면 쿼리를 대폭 줄일 수 있습니다. 설정은 간단하죠;

    @CollectionOfElements(targetElement = Integer.class)
    @Cache(usage=CacheConcurrencyStrategy.READ_WRITE)
    @BatchSize(size=30)
    private Map<Study, Integer> studyAttendanceRates;

    @CollectionOfElements(targetElement = Integer.class)
    @Cache(usage=CacheConcurrencyStrategy.READ_WRITE)
    @BatchSize(size=30)
    private Map<Study, Integer> studyTrustRates;

설정을 한 뒤 쿼리 갯수는 대폭 줄어들게 됩니다. 물론 그만큼 빨라지죠.

Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select studytrust0_.Member_id as Member1_0_, studytrust0_.element as element0_, studytrust0_.mapkey_id as mapkey3_0_ from Member_studyTrustRates studytrust0_ where studytrust0_.Member_id in (?, ?)
Hibernate: select studyatten0_.Member_id as Member1_0_, studyatten0_.element as element0_, studyatten0_.mapkey_id as mapkey3_0_ from Member_studyAttendanceRates studyatten0_ where studyatten0_.Member_id in (?, ?)

top




: 1 : ··· : 300 : 301 : 302 : 303 : 304 : 305 : 306 : 307 : 308 : ··· : 2638 :