Hi,

I'm sorry about posting a question that has already been answered, but I can't find the relevant post .

a call to session.iterate() results in multiple queries being generated: the first one that collects record ids, followed by a select for each title id (and we have bucketload of titles).

What needs to be done to make hibernate generate a single query?




Here's the mapping file:

<hibernate-mapping>
<class name="edu.ucsd.som.PayrollTitle" table="payroll_title">

<id name="code" column="code" type="string">
<generator class="assigned"/>
</id>

<property name="name" column="name" not-null="true" type="string" />
<property name="bargainingUnit" column="ttl_title_unit_code" type="string"/>
<property name="appointmentCode" column="ttl_personnel_program_code" type="string" not-null="false" />
<property name="active" column="active" type="boolean"/>
</class>
</hibernate-mapping>

and the query sent to iterate() looks like: from PayrollTitle as o where o.active = 1


Thanks
Dmitry

Use find instead of iterate. You will loose object caching during the lookup that iterate perfroms but 2.1 now has query caching.

i use iterate() because the result set is fairly large and I don't want to keep it all in memory.

So either you use list and fetch everything into memory, or you use iterate and load each row from the DB when it is needed. How else should it possibly work?

You could page the result set using the query object's setMaxResults() and setFirstResult() but you will need a decent JDBC driver that support scrollable result sets.

Actually we barely use scrollable result sets to implement setXXXResult() now - the dialects know the db-specific syntax in 2.1.

let me make sure I understand the implication of everything said so far: 1) 2.0.x's iterate() implementation will always result in multiple queries, one for each record; 2) 2.1's iterate() results in a single database query. Did I understand this correctly?

Ok, i've tried H2.1 and I'm still getting multiple queries. So is there anything I can do with Hibernate or do I have to go the pure JDBC way?

query.list
and setFirstResult and setMaxResult

will do the work I guess

I have absolutely no idea how you think that pure jdbc would help you.

Do as emmanuel says, and your problem is fixed.

No - 1/2 right.

1) iterate can hit the database multiple times unless the objects are found in the cache. This is similar to the N+1 db problem if the cache hit rate is low.

2) find in 2.1 can be told using the query class to cache the result set (whole result set). This will be none or one db hit depending on the cache. Then you have the full set in memory which you did not want.

You also have paging which may or may-not result in multiple db hits (but they will be significantly coarse.

I think all the options are covered.