Hello,

I have a table (I will call it T here) and a view (=V) that need to be joined for fetching some data. The class that is stored in the table T contains a list that has the references to the elements in V.

The view V is very big and REALLY slow. When Hibernate fetches the data, the following SQL is performed:

select ... from T, V WHERE T.id = V.id(+) and T.id = ?

Problem: this takes forever. If it would select the id in the view and THEN join, the query would be quick:

select ... from T, V WHERE T.id = V.id(+) and V.id = ?

I have tried to give Hibernate hints to do this:

* I tried to set "inverse" in the mapping file, but it didn't change the query
* I tried to set lazy=true on the collection and fetch="select" to tell Hibernate to use multiple statements (which, even if it causes 12 statements instead of one would be lightyears faster than doing the huge join above) and then tell Hibernate to load the collection (using Hibernate.initialize(myObject.myCollection)) - but this didn't initialize the attributes. Using lazy="false" and fetch="select" didn't change anything (why not?)

Any idea of what I'm doing wrong or how to tackle this issue?

Thank you for any pointers in this regard.

Version: Hibernate 3.1

Hi

try using Criteria

sess.createCriteria(T.class,"t")
.createCriteria("V","v")
.add( Restrictions.eq("t.id",value)).list();

Amila

(Don't forget to rate if helps)

hmm, this leads to the same query (and I think it should). I tried to change it to:

sess.createCriteria(T.class,"t")
.createCriteria("V","v")
.add( Restrictions.eq("v.id",value)).list();

but that doesn't quite work. The "value" that you provide here should actually come out of the "mother" table. So there is a table T and a view V and an association (that is modeled as a table). I would need to say: Get entity X from T where T = myValue and all the entities Y from V where V.kdnr = T.kdnr. Then Hibernate joins as posted in the first paragraph, but it should try to join "the other way around" (because that would be faster). Any ideas on how to do that?

My workaround so far: Select the entity X from T and load all entities Y with a criteria -> 2 selects.