Hi

We are using Session.find( ... ) to query a table with 18000 objects in it. When we have an object containing a set (one-to-many) of other objects, Hibernate is clever enough to lazy initialise the set - only retreiving a subset of the total resultset. this works well and is fast.

When we use the find method directly which returns a List - there is no lazy filling of the returned values pulling in the whole 18000 objects at once. Needless to say - this is slow. We have added the lazy="true" attribute to the object class mapping file hoping that this would return values from the db as needed rather than all at once.

How do you get the find method to handle large datasets by lazily filling the returned List.

We have tried using the session.createQuery().list with no change. Sesssion.iterate() works well but no good for us as we need to be able scroll back and forward along the result List.

thanks in advance.

James Kennedy

Hibernate version:Hibernate 2.1.6

Mapping documents:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.amateo.domain.party.Party" table="PARTY" schema="AMATEO360" lazy="true">
<id name="urn" column="URN" type="java.lang.Long">
<!-- <generator class="assigned"/> -->
<generator class="sequence">
<param name="sequence">party_sequence</param>
</generator>
</id>
<property name="partyname" column="PARTYNAME" type="string" length="255"/>
<many-to-one name="partytype" class="com.amateo.domain.party.Partytype" column="PARTYTYPEID"/>
<many-to-one name="person" class="com.amateo.domain.party.Person" column="PURN"/>
<many-to-one name="organisation" class="com.amateo.domain.party.Organisation" column="OURN"/>
<property name="commenttext" column="COMMENTTEXT" type="string" length="255"/>
<property name="stateid" column="STATEID" type="java.lang.Long" length="22"/>
<property name="accdisabled" column="ACCDISABLED" type="java.lang.Long" length="22"/>
<property name="visible" column="VISIBLE" type="java.lang.Long" length="22"/>
<property name="sourceid" column="SOURCEID" type="java.lang.Long" length="22"/>
<property name="tobedeleted" column="TOBEDELETED" type="java.lang.Long" length="22"/>
<property name="isnotcontactable" column="ISNOTCONTACTABLE" type="java.lang.Long" length="22"/>
<property name="foreignurn" column="FOREIGNURN" type="string" length="255"/>
<property name="cruser" column="CRUSER" type="string" length="255"/>
<property name="crdate" column="CRDATE" type="timestamp" length="7"/>
<property name="mduser" column="MDUSER" type="string" length="255"/>
<property name="mddate" column="MDDATE" type="timestamp" length="7"/>
<property name="owner" column="OWNER" type="java.lang.Long" length="22"/>

<set name="contactpoints" lazy="true">
<key column="partyurn"/>
<one-to-many class="com.amateo.domain.party.Contactpointmap"/>
</set>

<set name="partylinks" lazy="true">
<key column="partyurn1"/>
<one-to-many class="com.amateo.domain.party.Partylink"/>
</set>

<set name="custpersons" lazy="true">
<key column="partyid"/>
<one-to-many class="com.amateo.domain.bmdev.Custperson"/>
</set>

<set name="custorganisations" lazy="true">
<key column="partyid"/>
<one-to-many class="com.amateo.domain.bmdev.Custorganisation"/>
</set>
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
protected List getList(String query) throws DAOException {
List results = null;
Session ses = null;



try {
ses = getSession();

//results = (List)ses.createQuery(query).list();
Query hibernateQuery = ses.createQuery(query);

hibernateQuery.setCacheable(true);
results = hibernateQuery.list();
//results = (List)ses.find(query);


if (results != null) {
log.debug("The results size of query is: " + results.size());
switch (results.size()) {
case 0 :
return null;
default :
;
}
}

} catch (Exception e) {
throw new DAOException("getList failed", e);
}

return results;
}


Full stack trace of any exception that occurs:
no exception just very slow on a dataset of 18000 objects
Name and version of the database you are using:
Oracle 8.1
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt: