I have the following table structure where each line represents a lazy loaded many-to-one collection.



If I am navigating my object graph at A, and call A.getBs(), then for each B I call B.getCs() and B.getDs(), I will have executed A * B(A) * C(B) * D(B) queries. This is the worst case scenario and obviously should usually be avoided.

Some optimizations are available to me, namely the eager fetch capability in Hibernate. This gives me:

"from A join fetch A.Bs where A.id = ?"

which will load A and all of its Bs in a single query. So now, I am at, A * C(B) * D(B). And I thought this is where my optimization would stop, but I begain messing around with some other ideas and came across the following optimization.

If I execute the previous fetch, then execute this fetch: ""from A join A.Bs join fetch B.Cs where A.id = ?", I see one additional SQL statement to load all of the Cs. I could do the same to load all of the Ds.

So now the number of SQL statements are A + B + C + D, which I'm very excited about. But there is a catch. When C and D are loaded, they initialize their respective collections on B automatically. This is good. Hibernate basically iterates the result set and for every C or D it finds, it adds them to the collections on B.

The problem is that if B did not have a C or D related to it, the collection on B remains unititalized. Whenever I then call B.getCs() an SQL call is made to discover that there are no Cs for B.

I understand the problem, but one solution would be to tell the Hibernate collection "you are initialized, and I did the SQL for you so you don't have to do it again." Is there a way to make the Hibernate collection proxy think it is initialized without making it issue a SQL call?

Trolling for attention....

No love?

let's resume your situation
you call 3 differents query in order to fill your object graph with minimum sql.
To be sure to understand, can you describe what you see in the debugger at each query execution? which part is loaded, which is not...

Thanks for giving it a shot Anthony.

The SQL rows returned are just what you would expect. The only problem is that some of the collections are not initialized because they were not in any of the result sets. Meaning that there may not have been any D's that were a child of B. They're not in the database, therefore Hibernate could not load them for me.

The only problem is that Hibernate creates an unitialized collection on every B object as a placeholder for its D objects. I want to tell Hibernate that the collection of D's that B is holding on to is an empty set, not an unitialized set. Unfortunately though, Hibernate doesn't know this and when I access the collection, it insists on doing SQL to prove to itself that the collection is empty.

I want to use these objects in a detached manner and load them as fast as possible. In my particular case, Hibernate does 4 queries in 3 seconds to load all of the actual data in the database. I then have to exercise the unitialized collections and it performs another 400 queries in 9 seconds with each of the queries returning 0 rows. I know they have sero rows, and I want to force initialize these collections without Hibernate issuing SQL queries, so I can increase my performance by 400%.

for me these collections should be empty not uninitialized... as you said.

let's wait for other advanced user response or maybe some hibernate team member has an idea

What i usually do in such case is :
- lazy load and proxy everything
- use the fetch keywork
- set correctly the batch-size value for colelction and classes
- if I can, put it on a second level cache

Chago, if you manage to solve your case with these infos, i propose to write a wiki "best way to fetch multiple collections"

Your case is very well explained and the graph
A
*
C*B*D
is excellent to learn

what do you think cher Emmanuel?

Year it would be fantastic! We miss good materials on that subject, right now

Emmanuel & Anthony, thanks for your interest in this topic.



This is certainly what I am doing, but the example I give is a bit more involved. When you do a fetch join on dependent collections more than one level deep (like A->B->D), Hibernate ends up with unitialized sets in object B if there were no D rows related to a particular B.

Hold the phone. The explanation I was writing got me thinking that I should be using a different join on my collections. It now works blazingly fast with a minimal set of queries.

My HQL looked something like this:



It turns out Hibernate does perform extremely performant if I use a left outer join in my query to make sure that every row with a B has a D value, even if it is null. In these cases Hibernate is now initializing the collection to an empty set and no longer issues redundant SQL. Life is great!

The new HQL looks like this:

so in conclusion for
A
|
|
*
B --* C
|
|
*
D

you can load all the graph with

1-


2-


3-