Hi, all

I've just started to use hibernate and I appreciate it's power, but now I've a big trouble.

I've already read all possible messages inside this forum and available documentation,
and now a big wall is just in front of me.

I defined an HQL query that join several tables (it's a simple multiple join query as always I used in SQL)

Well,
when I call the named query, hibernate resolve it in a lot of SQL query (I'd say, too many!!),
so my HQL query takes 30 seconds (more or less) when an "OLD" SQL query takes only just a couple of second.
I can't belive !

I already know how FETCH JOIN in conjunction with lazy initialization work, and I also know the limit of one JOIN FETCH.

Anyway, I MUST join all the tables
I belive it's a quite usual need when you have a well designed database.

How can I do?
I don't want go back to use native SQL and loose the powerful of Object-relational features.

Do you have any suggestions ????

Thank in advance

Notes:
Hibernate version: 2.1.4
Database: MS SQL Server 2000

Options:

1) Don't use lazy on some of your relationships and allow outer joins to be used so its collected in one query.
2) Use outer fetch join for all many-to-one's as well as one of the collections.
3) Enable caching so many-to-one can be picked up from the cache rather than the query.
4) Use straight JDBC just for that query.

Or a combination of the above the first three options.

you mean lazy=false + outer-join = true + "from Toto t"
will load all toto to-many assocations in one query?

Thank for your quick answer,
unfortunately I wasn't so quick to verify your suggestions.

Anyway,
about the first and second suggestions

I tried to specify an outer join for a <key-many-to-one> relationship
but the XML parser was a bit angry

Since I have a composite key in a DB table, hibernate synchronizer plugin created the .hbm file in that way

Specifing outer-join="true" only in the collection the query freeze.

How about ???

Tks.

hi
i have the same problem. I need to join 3 tables. They don't have a foreign-key relationship. How can i do the inner join using hibernate
Can you please let me know as soon as possible. i am close to missing the deadline. i appreciate if you can tell me what you found out even if you didn't find a solution.
thx in advance

You have sent this request to multiple threads of discussion. Its been answered atleast twice. I strongly suggest you review your deadlines with management and invest (worth the money) in 'Hibernate In Action'. It has the answer for this question and I'm sure many further questions.