Large collections: how to load partially?

plm · **Posted:** Wed May 25, 2005 12:37 pm

Hello,

We have Objects that contain very large collections, for example an Asset class that has a price history of 10 years, i.e. it has about 2000 prices as a Map of date -> price.

Is it possible in Hibernate to load only a part of a collection? Usually we only need quite recent prices and loading the complete price history every time is out of the question.

If not, is there some strategy to deal with this situation?

TIA,

Peter

VampBoy · **Posted:** Wed May 25, 2005 1:41 pm

If you only need the current price you can write a filter and apply it to your collection.

anthony · **Posted:** Wed May 25, 2005 2:41 pm

Quote:

Map of date -> price.

do you know tha <map/> tag? it allows lazy loading, for example if the collection is lazy, calling myObject.getPrices().get(myDate); will only load the wanted price.

Advice: write business methods on your pojo: myObject.getPrice(myDate) is great ;)

plm · **Posted:** Sun May 29, 2005 5:23 pm

anthony wrote:

Quote:

Map of date -> price.

do you know tha <map/> tag? it allows lazy loading, for example if the collection is lazy, calling myObject.getPrices().get(myDate); will only load the wanted price.

Advice: write business methods on your pojo: myObject.getPrice(myDate) is great ;)

Hello Anthony,

I tried it, but it loaded the whole <map> collection in one go. Should it work in Hibernate 2.x?

This is my mapping:

Code:

   <class name='Index' table='SE_INDEX' mutable='false'>
      <id name='id' column='index_id' access='field'>
         <generator class='assigned'/>
      </id>
      <property name="name" column="name" access="field"/>
      <property name="tsCurrency" column="ccy" access="field"/>
      <map name="timeSeries" table="se_index_ts" lazy="true" access="property">
         <key column="index_id"/>
         <index column="adate" type="date"/>
         <element type="double" column="value"/>
      </map>
   </class>

When I try to access index.getTimeSeries.get(someDate) the following query is generated:

Code:

select timeseries0_.index_id as index_id__, timeseries0_.value as value__, timeseries0_.adate as adate__ from pms.se_index_ts timeseries0_ where timeseries0_.index_id=?

When I access other parts of the Map afterwards, no more queries are being generated. So it looks like the whole map is loaded at once in spite of laze="true" setting.

Is there any setting I have to make to influence this?

Also I cannot use a filter query in this case. I tried the following:

Code:

List result = session().createFilter(idx.getTimeSeries(), "where adate in (?,?)").setDate(0, from).setDate(1, to).list();

I know adate is not correct. I should use a property of the Collection, but in this case I need to filter by index. It doesn't seem to be possible :(.

Must I map the collection as a full blown entity using <one-to-many>? Can I use <map> together with <one-to-many>; the documentation shows only <set> and <bag> examples with <one-to-many>.

Thanks,

Peter

hackingbear · **Joined:** Mon Mar 21, 2005 12:27 am **Posts:** 17

I may have the same question.

More precisely, is the list returned by query.list() backed by ResultSet? Or is q.list() behaves the same as a ResultSet in term of memory usage?

The User Document says that the list will be completely loaded into a collection in memory. I'm not comfortable with that. I want to be able to retrieve some larger results without loading everything into memory. Like:

Code:

Query q = session.createQuery(...);
List l = q.list(); // may have large result rows 
for (Object o : l) {
    o.getValue(); // just get some value off the object
    // go to next row, the previous o can be garbage collected
}
// At this point, I should still use a constant amount of memory
// since results are GCed

If q.list() doesn't do that? What else can I do to reduce memory consumption retrieving large result sets?

Thanks.

[/code]

gavin · **Posted:** Mon Jun 06, 2005 2:20 am

So use ScrollableResults.

Documentation, people!

hackingbear · **Joined:** Mon Mar 21, 2005 12:27 am **Posts:** 17

The user document is quite vague on the performance differences between list() and scroll(). I actually figured that out by reading the Hibernate source codes.

Quote:

If your JDBC driver supports scrollable ResultSets, the Query interface may be used to obtain a ScrollableResults object, which allows flexible navigation of the query results.

This sounds like if the JDBC supports forward-only result set, it could not be used. Also need to compare list() and scroll(), like: should I use scroll regardless of result set size or should I use scroll() only for large result.

christian · **Posted:** Mon Jun 06, 2005 5:56 am

"This sounds like if the JDBC supports forward-only result set, it could not be used."

Maybe you like to read again what you pasted.