Howdy folks:

I have one doozy of a query to perform. Everything pretty much works as expected except it takes a really long time for results to deserialize into POJO's. Its also worth noting that hibernate is being used in conjunction with the Spring framework in my application.

The code from the DAO (not the best thing I've ever written)

/*
* query by Criteria needs to specialize on a class
*/


Criteria crit = session.createCriteria(MarcRecord.class).setFetchMode("fields", FetchMode.EAGER);

final Hashtable criteriaHash = MakeCriteriaHash(searchParameters);

/*
* First we add criteria pertaining to marc record class data, if
* any
*/

List list = (List) criteriaHash.get(MarcRecord.class);
if (list != null) {
Iterator recordCriteria = list.iterator();
while (recordCriteria.hasNext())
crit.add((Criterion) recordCriteria.next());
}

/*
* Because the world is not flat in the Marc tables, we need to pull a few
* "in" queries;
*/

if(previousResults!=null)
{
List oids = new Vector();
Iterator iter = previousResults.iterator();
while(iter.hasNext()){
oids.add(((MarcRecord)iter.next()).getObjOid());
}
crit.add(Expression.in("objOid", oids));

}
/*
* Next, we create a subcriteria for querying marc field data (tag
* num, etc.)
*/

Criteria fieldsSubCriteria = crit.createCriteria("fields").setFetchMode("subfields", FetchMode.EAGER);
list = (List) criteriaHash.get(MarcField.class);
if (list != null) {

Iterator fieldsCriteria = list.iterator();
while (fieldsCriteria.hasNext())
fieldsSubCriteria.add((Criterion) fieldsCriteria.next());
}

/*
* Finally, we create a sub-subcriteria for querying marc subfield
* data. This is where much of the querying will be done.
*/

Criteria subfieldsSubCriteria = fieldsSubCriteria
.createCriteria("subfields");
list = (List) criteriaHash.get(MarcSubfield.class);
if (list != null) {
Iterator subfieldsCriteria = list.iterator();
while (subfieldsCriteria.hasNext())
subfieldsSubCriteria.add((Criterion) subfieldsCriteria
.next());
}
log.info("Querying...");
crit.setResultTransformer(Criteria.ROOT_ENTITY);
List results = crit.list();
log.info("Got " + results.size() + " results");
return results;

The output I really desire from the query is a list of entity ids for the MarcRecord.class, and I could have sworn that, while searching for the solution for a different problem, I came across a portion of one of the APIs that did just that.

It seems that the query is also selecting extra fields from the joined tables that it doesn't really need to do, and I'm wondering if I am doing something that would cause excessive sql generation.

Well, cheerio...

Andy

Hibernate version:[/b
2.1.7

[b]Mapping documents:

<class name="net.webcheckout.domain.MarcRecord" table="marc_record">
<id name="objOid" type="java.lang.Long" column="obj_oid">
<generator class="native"/>
</id>
<property name="created" type="java.util.Date" />
<property name="modified" type="java.util.Date" />
<property name="origin" type="java.lang.String" />
<property name="recordType" column="record_type" type="java.lang.String" />
<bag name="fields" lazy="true" cascade="all-delete-orphan">
<key column="marc_record_oid"/>
<one-to-many class="net.webcheckout.domain.MarcField"/>
</bag>
</class>

<class name="net.webcheckout.domain.MarcField"
proxy="net.webcheckout.domain.MarcField"
table="marc_field">
<id name="objOid" type="java.lang.Long" column="obj_oid">
<generator class="native"/>
</id>
<property name="tag" type="java.lang.String" />
<property name="data" type="java.lang.String" />
<property name="indicatorOne" column="ind_one" type="java.lang.Character" />
<property name="indicatorTwo" column="ind_two" type="java.lang.Character" />
<many-to-one name="record" class="net.webcheckout.domain.MarcRecord"
column="marc_record_oid"/>
<bag name="subfields" lazy="true" outer-join="true" cascade="all-delete-orphan">
<key column="marc_field_oid"/>
<one-to-many class="net.webcheckout.domain.MarcSubfield"/>
</bag>
</class>

<class name="net.webcheckout.domain.MarcSubfield"
proxy="net.webcheckout.domain.MarcSubfield"
table="marc_subfield">
<id name="objOid" type="java.lang.Long" column="obj_oid">
<generator class="native"/>
</id>
<property name="reducedValue" type="java.lang.String" column="reduced_value"/>
<property name="value" type="java.lang.String" column="subfield_value"/>
<property name="code" type="java.lang.Character" column="subfield_code"/>
<many-to-one name="field" class="net.webcheckout.domain.MarcField" outer-join="true"
column="marc_field_oid" />
</class>





Name and version of the database you are using:
Postgresql 8.0.1

The generated SQL (show_sql=true):

From Postgres Duration logging
LOG: statement: select this.obj_oid as obj_oid2_, this.created as created2_, this.modified as modified2_, this.origin as origin2_, this.record_type as record_t5_2_, x0_.obj_oid as obj_oid0_, x0_.tag as tag0_, x0_.data as data0_, x0_.ind_one as ind_one0_, x0_.ind_two as ind_two0_, x0_.marc_record_oid as marc_rec6_0_, x1_.obj_oid as obj_oid1_, x1_.reduced_value as reduced_2_1_, x1_.subfield_value as subfield3_1_, x1_.subfield_code as subfield4_1_, x1_.marc_field_oid as marc_fie5_1_ from marc_record this inner join marc_field x0_ on this.obj_oid=x0_.marc_record_oid inner join marc_subfield x1_ on x0_.obj_oid=x1_.marc_field_oid where x0_.tag ilike '710' and x1_.subfield_value ilike '%vh1%'
LOG: duration: 247.055 ms