Hi,

I want to iterate all my datas (6 500 000 objects) with Hibernate, and so, I use the session.iterate("hql") function. It works but this solution is much slower than a simple SQL select (12 times). The reason is that for every object, Hibernate load the datas during the first access of one of its properties. So, there are many SQL requests!



I believed that I found the solution with the session.createSQLQuery("sql") function. But the iterate method of the SQLQueryImpl isn't created at this time, and so, I have to use list().iterator().
This time, I have only one SQL request, but all objects are loaded during the call of list(), and now I have an OutOfMemoryException!

I searched in this forum the solution, and I read that this feature isn't in Hibernate. Is it true?

How to manage a huge number of objects with Hibernate? With a max speed, and a min memory consumption?


Frederic

You could be use proxy for your many-to-one and one-to-one associations (for lazy initialization of nested objects).

It's not an association. I just want to visit all objects with a simple HQL request like:



But, I want that Hibernate uses only one SQL request and not one by proxy object which is the case with lazy loading. I have 6 millions objects!


Frederic

how many level do your object graph have?

for example if you have A 1 ---- * B

you can load B collections in only one query with "FECTH" word in your query.
Give some kind of class diagram (of mapping file), we may help you

Why not just use session.find() then? That will retrieve all of the objects in a single query, as long as you have enough memory.

Of course, if your entire dataset won't fit into memory at once, looping through all the data will of course implies multiple queries, no matter what you do with Hibernate, plain JDBC or whatever tool you fancy.

joib,
session.find will return all objects of the 1st level of his object graph.
I think his problem is to retrieve all the graph with one, with outer, fetch, he can manage to retrieve a part of the graph or all the graph , it depends on the mapping... and class diagram

Thanks for your responses.





No, session.find() doesn't retrieve all objects in one SQL request (perhaps because datas in the database use a composite primary key, but the database exists before my program). This method creates a list of proxies that is load by its own SQL request when a property is read or wroten.

And I don't want to use find(), that creates all objects, but [n]iterate()[/b] that creates object only when I call next(). It's very important for me because I have 6 millions of objects and the size of the database is 13 Go... After I get an object and I visit it, I evict() it from the session to release the memory.
Morever, the JDBC driver doesn't load all datas after the SQL call, but only when datas are needed by fetch batch. So, if iterate() generates only one SQL request, I can use Hibernate.
I believed that createSQLQuery() was my solution, but the iterate method is currently unsupported.

Frederic

Ok, so this problem can be solved by mapping the properties with outer-join="true" or with some fetch join in the hql.



iterate() will do a select for every next() call, if that's what you want. Or to be more precise, the call to session.iterate() will only retrieve the indexes (6 million in your case), and then when you call next() it will use the index value to retrieve the entire row from the database. So iterate() whould at least work memory-wise but you'll have an SQL query for every row.



Uhh, what are you trying to say here? That you want to load your 13 Gb in one roundtrip to the DB, but you don't want to allocate 13 Gb of memory? Sorry, there is no way to do that regardless if you're using Hibernate, plain JDBC or anything else.

What I personally would do in your case, would be something like



That will create SQL queries with the LIMIT and OFFSET stuff, so you'll avoid retrieving all 13 Gb in one query.

joib,
even with iterate, the object graph (per row) is not always "loadable" in one generated sql query.
It really depends on your mapping
example: A 1--* B 1--* C cannot be loaded in one query "select a as a left FETCH join a.bs left FETCH join bs.c" is not correct
I may be wrong but i've tested many ways to get this 3 level graph in one query (sql generated) ... i didn't manage to do it.
What is possible is to load all b collections and then x queries will be automatically generated to load C collections.

And of course, as you said, if there was a solution, the problem of memory would not be solved...

Oh, I just assumed that rfred999 had a relatively flat structure and the main problem was the large amount of data. Sorry for any confusion.



You're right, there is no way to do this with "normal" sql (and thus not with hql) with the standard way to model hierarchies, since to fetch the C:s you need the pk:s of the B:s.

Now, there are a number of ways to do this in a single SQL query. One, if you use Oracle, is to use the (proprietary) CONNECT BY statement. The SQL standard has some kind of WITH RECURSIVE statement which is similar to CONNECT BY, but I don't think any DB supports that :(.

Another way, in the case of trees, is to use the "nested sets model" (Google and you'll find out how to do it). Of course, Hibernate:s collection support doesn't support something like that, but you could of course still map the objects themselves with Hibernate and then build the tree yourself from the flat data returned by a query.



Umm, yes. I think that huge trees (i.e. entire tree won't fit in memory) are very difficult to do efficiently with a RDBMS, and if one encounters them, one ought to spend lots of effort thinking which solution will suck the least (in this case, I think all possible solutions will suck).

How do that? I didn't understand your example with fetch join. Where do you see the property "outer-join" for "property"? Is it not only for relation?




It's not true. My database is Oracle 9i and when I call this SQL request:



datas aren't loaded in one shot, but the database sends datas to ResultSet by packets. For example, I can read 155 Mo in 17 Mo! So, the java program hasn't all datas in memory.

I think is currently not possible to do what I want. Thanks for your help.


Frederic