I have read the rules and searched the forum. I want to have a 1-M association fetched lazily, but when I do access it, I want it to be retrieved in
ONE query instead of
N+1 queries. I know I can do this by telling Hibernate to do an outer join to that 1-M association before I get the parent object, but I may not know in advance
which 1-M associations might be needed... so I just need it to fetch the 1-M associations efficiently
if and when they are accessed.
Hibernate version:2.1
Mapping documents:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping auto-import="false">
<class name="com.rivermine.model.CustomerRequest" dynamic-update="true" optimistic-lock="none" table="CUSTOMER_REQUEST">
<id name="crId">
<column name="CR_ID"/>
<generator class="uuid.hex"/>
</id>
<set name="elements" lazy="true" batch-size="100">
<key column="CR_ID"/>
<one-to-many class="com.rivermine.model.CustomerRequestElement"/>
</set>
</class>
<class name="com.rivermine.model.CustomerRequestElement" dynamic-update="true" optimistic-lock="dirty" table="CUSTOMER_REQUEST_ELEMENT">
<composite-id>
<key-many-to-one name="customerRequest">
<column name="CR_ID"/>
</key-many-to-one>
<key-property name="sequence">
<column name="SEQUENCE"/>
</key-property>
</composite-id>
<property name="elementType">
<column name="ELEMENT_TYPE" length="20" not-null="false"/>
</property>
<property name="requestElementData" type="java.io.Serializable">
<column name="REQUEST_ELEMENT_DATA" length="0" not-null="false"/>
</property>
</class>
</hibernate-mapping>
(I have abbreviated the mapping by removing some irrelevant properties.)
Code between sessionFactory.openSession() and session.close():All I am doing is that I get back an instance of CustomerRequest (which Hibernate retrieves with one query) and then I call getElementCount() on it:
Code:
public int getElementCount()
{
return _elements.size();
}
where _elements is declared as a java.util.Set.
Full stack trace of any exception that occurs:Name and version of the database you are using:Oracle 9i
The generated SQL (show_sql=true):Here is where the real problem is. You'll see Hibernate issue the first, top-level query, which is fine. I then call getElementCount(). In this example, there are 3 "elements". It issues one query against the child table and finds there are 3 rows, which it then re-queries one at a time instead of just building CustomerRequestElements out of the 3-row query. As a result, what could have been done with two queries has taken 5. Not a big deal for 3 child rows but when I have 100, I'm going to have a problem.
Quote:
<Fri Mar 11 08:03:31 EST 2005> <Debug > <sistenceService> <HQL: SELECT a FROM com.rivermine.model.CustomerRequest AS a WHERE (a.crId = :param0)>
08:03:31,229 DEBUG SQL:230 - select customerre0_.CR_ID as CR_ID, customerre0_.ACTUAL_VALUE as ACTUAL_V2_, customerre0_.ADDITIONAL_INFO as ADDITION3_, customerre0_.ASSIGNED_AID as ASSIGNED4_, customerre0_.ASSIGNED_GID as ASSIGNED5_, customerre0_.ASSIGNED_UID as ASSIGNED6_, customerre0_.CR_AUX_ID as CR_AUX_ID, customerre0_.CREATION_DATE as CREATION8_, customerre0_.CURRENT_MILESTONE as CURRENT_9_, customerre0_.CURRENT_MILESTONE_DATE as CURRENT10_, customerre0_.FILE_ID as FILE_ID, customerre0_.MODIFICATION_DATE as MODIFIC12_, customerre0_.ORIGINATING_UID as ORIGINA13_, customerre0_.OWNER_AID as OWNER_AID, customerre0_.OWNER_GID as OWNER_GID, customerre0_.RECORD_STATE as RECORD_16_, customerre0_.REMOTE_ID as REMOTE_ID, customerre0_.REMOTE_ID_TYPE as REMOTE_18_, customerre0_.REQUEST_CONTACT_CID as REQUEST19_, customerre0_.REQUEST_DATA as REQUEST20_, customerre0_.REQUEST_DESCRIPTION as REQUEST21_, customerre0_.REQUEST_STATUS as REQUEST22_, customerre0_.REQUEST_TYPE as REQUEST23_, customerre0_.REQUEST_VALUE as REQUEST24_, customerre0_.SALES_UID as SALES_UID from CUSTOMER_REQUEST customerre0_ where ((customerre0_.CR_ID=? ))
Hibernate: select customerre0_.CR_ID as CR_ID, customerre0_.ACTUAL_VALUE as ACTUAL_V2_, customerre0_.ADDITIONAL_INFO as ADDITION3_, customerre0_.ASSIGNED_AID as ASSIGNED4_, customerre0_.ASSIGNED_GID as ASSIGNED5_, customerre0_.ASSIGNED_UID as ASSIGNED6_, customerre0_.CR_AUX_ID as CR_AUX_ID, customerre0_.CREATION_DATE as CREATION8_, customerre0_.CURRENT_MILESTONE as CURRENT_9_, customerre0_.CURRENT_MILESTONE_DATE as CURRENT10_, customerre0_.FILE_ID as FILE_ID, customerre0_.MODIFICATION_DATE as MODIFIC12_, customerre0_.ORIGINATING_UID as ORIGINA13_, customerre0_.OWNER_AID as OWNER_AID, customerre0_.OWNER_GID as OWNER_GID, customerre0_.RECORD_STATE as RECORD_16_, customerre0_.REMOTE_ID as REMOTE_ID, customerre0_.REMOTE_ID_TYPE as REMOTE_18_, customerre0_.REQUEST_CONTACT_CID as REQUEST19_, customerre0_.REQUEST_DATA as REQUEST20_, customerre0_.REQUEST_DESCRIPTION as REQUEST21_, customerre0_.REQUEST_STATUS as REQUEST22_, customerre0_.REQUEST_TYPE as REQUEST23_, customerre0_.REQUEST_VALUE as REQUEST24_, customerre0_.SALES_UID as SALES_UID from CUSTOMER_REQUEST customerre0_ where ((customerre0_.CR_ID=? ))
<Fri Mar 11 08:03:31 EST 2005> <Debug > <rmine.test.Test> <calling getElementCount...>
08:03:31,579 DEBUG SQL:230 - select elements0_.CR_ID as CR_ID__, elements0_.SEQUENCE as SEQUENCE__, elements0_.CR_ID as CR_ID0_, elements0_.SEQUENCE as SEQUENCE0_, elements0_.ELEMENT_TYPE as ELEMENT_3_0_, elements0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT elements0_ where elements0_.CR_ID=?
Hibernate: select elements0_.CR_ID as CR_ID__, elements0_.SEQUENCE as SEQUENCE__, elements0_.CR_ID as CR_ID0_, elements0_.SEQUENCE as SEQUENCE0_, elements0_.ELEMENT_TYPE as ELEMENT_3_0_, elements0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT elements0_ where elements0_.CR_ID=?
08:03:31,599 DEBUG SQL:230 - select customerre0_.CR_ID as CR_ID0_, customerre0_.SEQUENCE as SEQUENCE0_, customerre0_.ELEMENT_TYPE as ELEMENT_3_0_, customerre0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT customerre0_ where customerre0_.CR_ID=? and customerre0_.SEQUENCE=?
Hibernate: select customerre0_.CR_ID as CR_ID0_, customerre0_.SEQUENCE as SEQUENCE0_, customerre0_.ELEMENT_TYPE as ELEMENT_3_0_, customerre0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT customerre0_ where customerre0_.CR_ID=? and customerre0_.SEQUENCE=?
08:03:31,669 DEBUG SQL:230 - select customerre0_.CR_ID as CR_ID0_, customerre0_.SEQUENCE as SEQUENCE0_, customerre0_.ELEMENT_TYPE as ELEMENT_3_0_, customerre0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT customerre0_ where customerre0_.CR_ID=? and customerre0_.SEQUENCE=?
Hibernate: select customerre0_.CR_ID as CR_ID0_, customerre0_.SEQUENCE as SEQUENCE0_, customerre0_.ELEMENT_TYPE as ELEMENT_3_0_, customerre0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT customerre0_ where customerre0_.CR_ID=? and customerre0_.SEQUENCE=?
08:03:31,689 DEBUG SQL:230 - select customerre0_.CR_ID as CR_ID0_, customerre0_.SEQUENCE as SEQUENCE0_, customerre0_.ELEMENT_TYPE as ELEMENT_3_0_, customerre0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT customerre0_ where customerre0_.CR_ID=? and customerre0_.SEQUENCE=?
Hibernate: select customerre0_.CR_ID as CR_ID0_, customerre0_.SEQUENCE as SEQUENCE0_, customerre0_.ELEMENT_TYPE as ELEMENT_3_0_, customerre0_.REQUEST_ELEMENT_DATA as REQUEST_4_0_ from CUSTOMER_REQUEST_ELEMENT customerre0_ where customerre0_.CR_ID=? and customerre0_.SEQUENCE=?
<Fri Mar 11 08:03:31 EST 2005> <Debug > <rmine.test.Test> <Number of elements: 3>
Debug level Hibernate log excerpt:Code:
Login
FAQ
Search
