I have the following graph: A (1) --- (*) B (1) --- (*) C.
My question is how to read the whole graph at once as close as possible to JDBC speed.

Thank you,

mota

select a, b, c from A as a left outer join a.Bs b left outer join b.Cs c

When I use session.load(from A where A.id=?);
Hibernate generates many select statements to get A, Bs, and Cs separately, which is not efficient.

When I add "left join fetch" for B and C, Hibernate uses the same statement to get A, Bs, and Cs, but the query is slower that the previous one. My point is are there any guidelines for this basic case.

Thank you,

mota

I need to get only the top node A (and get Bs and Cs from A), and not explicitly getting all of them in my query.

mota

Doing this depends on the average row size of your 3 tables - cause you will return rowcount(A) * rowcount(B) * rowcount(C) records.
One single query, but may return a huge amount of data - B being duplicated as many times you have corresponding C's, and A duplicated as many times you got duplicate Bs...

We had the same problem, and the only solution we found was to retrieve the collections ourselves using 3 different HQL queries:



Unfortunately, doing so will not initialize the collections in A and B. So if you later call A.getBs() - you will hit the database again :-(

On the other hand, it gives you the opportunity to retrieve only properties of B & C you actually need...

Use "lazy="true" on A's set's. That way you only load A.

My question is not how to get the whole graph at once (There are many ways), but what is the fastest type/example of Hibernate query to use?
Please provide concrete examples.

Thank you,

mota

LOL




Are you manic-depressive ;-) ?

epbernard, please read the whole sentence:

"My question is how to read the whole graph at once as close as possible to JDBC speed."

Thank you,

mota

Think about how you would do using straight SQL through JDBC - and I think you will come close to what I said above...

I came across this kind of problem as well.
The problem is at some point in your program, you know you need the node A and all its children - you need a complete branch of the tree.
Where Hibernate/HQL gives you only the opportunity to go one level deep at once - there is no way to tell it something like
give me A with all its B's, and for each B their C's (C's are 2 level deep - they are no directly reachable from A...

That's why I came to load the objects by myself in 2 steps:
- tell Hibernate to load A, possibly with all B's (join fetch);
- then I build a query to load all C's belonging to the B's retrieve in step 1

Voila..

if you set lazy="false", a query like this "from A a where a.Id = ?" will return A, with Bs, and Cs. But behind the scene Hibernate executes many sql statements to get separately A, Bs, and Cs. I think this is not efficient. I tried to improve the query with "left join fetch" for B and C, but Hibernate still generates more than one sql statement. I am looking for a way to get the whole graph with 1 sql statement.

mota

The point is: show me the single SQL statement you would do if you were not using Hibernate

If you can, then I'll tell you how you can do it using Hibernate...

Did you try the resquest I gave you ?

Do you have hibernate.use_outer_join=true ?

From roadmap:

outer-join attribute for collection mappings, allowing collections of values and one-to-many associations to be fetched by outerjoining for a load() or Criteria query.

<set name="children" outer-join="true">
<key column="PARENT"/>
<one-to-many class="Child"/>
</set>

Jeff

What I want to do with hibernate is read all A, Bs, and Cs in one sql statement similar to the following:
"select a.*, b.*, c.* from A a left outer join B b on b.AForeignKey = a.PrimaryKey left outer join C c on c.BForeignKey = b.PrimaryKey where a.PrimaryKey = ? order by a.PrimaryKey, b.PrimaryKey, c.PrimaryKey"

mota