Hello,
I have a question regarding Hibernate performance of the "where in" clause. for example:
Quote:
select * from document where doc_id in (10, 11, 12)
Hibernate seems to insist on running this as follows, resulting in several unneccesary database roundtrips and dismal performance:
Quote:
select * from document where doc_id = 10
select * from document where doc_id = 11
select * from document where doc_id = 12
My question is simply; how can i make Hibernate do this lookup with only one roundtrip?
The actual query is using a view in an Oracla database, defined as follows in our hibernate mappings file:
Quote:
<class name="no.dep.odin.domain.Document" table="view_document" mutable="false">
<id name="id" column="doc_id" type="no.dep.odin.integration.type.DocumentIdType" unsaved-value="any">
<generator class="assigned"/>
</id>
<property name="docKey" column="dockey" type="string"/>
<property name="identifier" column="identifier" type="string"/>
<property name="title" column="title" type="string"/>
<property name="subTitle" column="subtitle" type="no.dep.odin.integration.type.StringClobType"/>
<property name="altTitle1" type="string"/>
<property name="altTitle2" type="string"/>
<property name="altTitle3" type="string"/>
<property name="editorialText" column="editorialtext" type="no.dep.odin.integration.type.StringClobType"/>
<property name="createDate" column="create_date" type="timestamp"/>
<property name="editDate" column="edit_date" type="timestamp"/>
<property name="startDate" column="start_date" type="timestamp"/>
<property name="eventDate" column="event_date" type="timestamp"/>
<property name="publisherId" column="publisher" type="no.dep.odin.integration.type.PublisherIdType"/>
<property name="copublisherId1" column="copublisher1" type="no.dep.odin.integration.type.PublisherIdType"/>
<property name="copublisherId2" column="copublisher2" type="no.dep.odin.integration.type.PublisherIdType"/>
<property name="creator" type="string"/>
<property name="language" type="string"/>
<property name="subLanguage" type="string"/>
<property name="subject" type="string"/>
<property name="searchable" type="boolean"/>
<property name="news" column="isNews" type="boolean"/>
<property name="caseNumber" column="relcase" type="string"/>
<property name="replacedBy" column="reldocument" type="string"/>
<property name="primaryRelationId" column="primary_rel" type="no.dep.odin.integration.type.WebPageIdType"/>
<property name="documentTypeId" column="dtp_id" type="no.dep.odin.integration.type.DocumentTypeIdType"/>
<property name="documentStateId" column="dst_id" type="no.dep.odin.integration.type.DocumentStateIdType"/>
<property name="templateId" column="dtpl_id" type="no.dep.odin.integration.type.DocumentTemplateIdType"/>
<set name="sectionsInternal" table="view_doc_section" sort="natural" inverse="true">
<key column="doc_id"/>
<one-to-many class="no.dep.odin.domain.DocumentSection"/>
</set>
</class>
The code that does the query is as follows:
Quote:
List documents = session.find(
"from no.dep.odin.domain.Document document " +
"where document.id in (" + docIdsAsString + ")");
// The string is a comma separated list of ids.
The output from Hibernate is:
Quote:
Hibernate: select document0_.doc_id as doc_id, document0_.dockey as dockey, document0_.identifier as identifier, document0_.title as title, document0_.subtitle as subtitle, document0_.altTitle1 as alt
Title1, document0_.altTitle2 as altTitle2, document0_.altTitle3 as altTitle3, document0_.editorialtext as editoria9_, document0_.create_date as create_10_, document0_.edit_date as edit_date, document0
_.start_date as start_date, document0_.event_date as event_date, document0_.publisher as publisher, document0_.copublisher1 as copubli15_, document0_.copublisher2 as copubli16_, document0_.creator as
creator, document0_.language as language, document0_.subLanguage as subLang19_, document0_.subject as subject, document0_.searchable as searchable, document0_.isNews as isNews, document0_.relcase as r
elcase, document0_.reldocument as reldocu24_, document0_.primary_rel as primary25_, document0_.dtp_id as dtp_id, document0_.dst_id as dst_id, document0_.dtpl_id as dtpl_id from view_document document0
_ where (document0_.doc_id in(141366 , 138984 , 141492 , 138720 , 138634 , 137054 , 137136 , 137040))
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
Hibernate: select view_doc0_.doc_id as doc_id__, view_doc0_.sec_name as sec_name__, view_doc0_.sec_num as sec_num__, view_doc0_.doc_id as doc_id, view_doc0_.sec_name as sec_name, view_doc0_.sec_num as
sec_num, view_doc0_.stp_id as stp_id, view_doc0_.order_num as order_num from view_doc_section view_doc0_ where view_doc0_.doc_id=?
What is the reason for this? Is it because of the one-to-many mapping we're using?
Our project is suffering from really bad performance because of this, and I'd bee extremely grateful for any hints or tips. :-)
Best regards,
Bjorn Wang
Login
FAQ
Search
