Hello!

I'm quite new to using NHibernate and brand new to this forum, so I beg your pardon if the problem I stumbled
into recently has already been adressed (though I wasn't able to find a solution using the search function).

I'm currently working on the mappings of an older DataBase.

The behaviour, which I'm seeing, is that I do a query, which seems to return the desired objects, but after the appropriate
sQL-query, another SELECT is done for each of the returned objects (while the only line of code I execute is the
one, that initiates the query).



The problem occures at the following query:


select a
from A as a
inner join fetch a.B as b
...
inner join fetch a.B.C as c
inner join fetch a.B.C.D as d
where ...
...
and a.B.C.D = :someObjectOfTypeD



The mappings look like the following:

In A:
<many-to-one name="B" column="b_id" unique="true" not-null="true" foreign-key="FK_A_B"/>

In B:
<one-to-one name="A" class="A" property-ref="B"/>
<one-to-one name="C" class="C" property-ref="B"/>

In C:
<many-to-one name="B" column="b_id" not-null="true" foreign-key="FK_C_B"/>
<many-to-one name="D" column="d_id" not-null="false" foreign-key="FK_C_D"/>

In D:
-



Is there anything awfully wrong with the above code?
Any help would be greatly appreciated, as performance is really bad right now.

Thanks in advance and
greetings from Germany

Benjamin

Hmm, not sure, but afaik, if you define aliases, you have to use them. Otherwise you get additional joins:

select a
from A as a
inner join fetch a.B as b
...
inner join fetch a.B.C as c
inner join fetch a.B.C.D as d
where ...
...
and d = :someObjectOfTypeD

Hi Wolfgang!

Thank you very much for your answer.
While looking like a much cleaner way to do the query (and I will update all those I've already written accordingly), this does nothing to solve my problem :-(

Have you only replaced the construct in the where clause or everywhere ?

select a
from A as a
inner join fetch a.B as b
...
inner join fetch b.C as c
inner join fetch c.D as d
where ...
...
and d = :someObjectOfTypeD

Hibernate does only support join fetching up to a defined depth. You can configure that depth with the "max_fetch_depth" setting in your configuration file.

Thanks again for your help!

I have only done replacements in the where-clause, as when I try do similar replacements in the joins, Visual Studio tells me, there are errors in the query.

Indeed max_fetch_depth was set to 2. But after setting it to 4, I still get the same behaviour.

What do you mean with "Visual Studio tells me, there are errors in the query" ? What is the error (check the inner exception).

I'm sorry, I can't be more specific about this exeption. The company I work for uses another (selfmade) abstraction layer encapsulating NHibernate.

The inner execption is not being passed through, thus is null.
It simply tells me:
"Errors in named queries: {QueryName}"

Can you post the complete query ?

Yes, I just replaced some names, as I am not perfectly sure, if I would be allowed to otherwise.
That being said, I've double and triple checked, that this is not due to some typo (having rewritten the query several times).

<query name="MyQueryName">
select a
from A as a
inner join fetch a.B as b
inner join fetch a.B.M as m
inner join fetch a.B.C as c
inner join fetch a.B.C.D as d
where b.Date between :firstDay and :lastDay
and m.SomeStringProperty = :someString
and d = :someObjectOfTypeD
</query>

This is the way, I've used to do the query + your modifications in the where-clause.
If I do this:

<query name="MyQueryName">
select a
from A as a
inner join fetch a.B as b
inner join fetch b.M as m
inner join fetch b.C as c
inner join fetch c.D as d
where b.Date between :firstDay and :lastDay
and m.SomeStringProperty = :someString
and d = :someObjectOfTypeD
</query>

I get said errors.

Hmmm, can't see any obvious error, but I suppose this is the problem

inner join fetch b.M as m
inner join fetch b.C as c

This would imply a join from b to two different tables and I think that's just not supported for fetch. Can you try and remove the m part from the query and check if it works ?

I will do so first thing tomorrow morning and let you know!

Morning!

Removed the second join on B.
Sadly, this does nothing to solve my problem.

Still seeing those huge amounts of selects.

EDIT: I played around a bit and realized, that I also get these selects, if I narrow my query down to

select a
from A as a
inner join fetch a.B as b
where b.Date between :firstDay and :lastDay

Try setting lazy-load="false" on the classes and the associations.

Can you post the queries you get ?

Setting lazy load to false doesn't change anything.

The queries look like the following:
NHibernate: select a0_.a_id as a1_16_0_, b
1_.b_id as b1_8_1_, a0_.a_id as a2_16_0_, ~~~some properties here~~~
from tbl_A a0_ inner join tbl_B b1_ on a0_.a_id=b1_.b_id where b1_.date between @p0 and @p1;@p0 = 15.08.2009 00:00:00, @p1 = 21.08.2009 00:00:00

NHibernate.SQL: 2009-09-18 09:58:40,359 DEBUG - select a0_.a_id as a1_16_0_, b1_.b_id as b1_8_1_, a0_.b_id as b2_16_0_, ~~~some properties here~~~
from tbl_A a0_ inner join tbl_B b1_ on a0_.b_id=b1_.b_id where b1_.date between @p0 and @p1;@p0 = 15.08.2009 00:00:00, @p1 = 21.08.2009 00:00:00

The following set of queries is repeated for each returned object:

NHibernate.SQL: 2009-09-18 09:58:40,375 DEBUG - SELECT ~~~some properties here~~~
FROM tbl_C c0_ inner join tbl_B b1_ on b0_.b_id=b1_.b_id left outer join tbl_A a2_ on b1_.b_id=a2_.b_id WHERE b0_.b_id=@p0;@p0 = 277952

NHibernate: SELECT b0_.c_id as c1_9_2_, b0_.b_id as b2_9_2_, ~~~some properties here~~~
FROM tbl_C b0_ inner join tbl_B b1_ on b0_.b_id=b1_.b_id left outer join tbl_opt_A

NHibernate.SQL: 2009-09-18 09:58:40,578 DEBUG - SELECT a0_.a_id as a1_16_2_, a0_.a_id as b2_16_2_, ~~~some properties here~~~
FROM tbl_A a0_ inner join tbl_B b1_ on a0_.b_id=b1_.b_id left outer join tbl_C b2_ on b1_.b_id=b2_.b_id
WHERE a0_.b_id=@p0;@p0 = 277952

NHibernate: SELECT a0_.a_id as a1_16_2_, a0_.b_id as b2_16_2_, ~~~some properties here~~~
FROM tbl_A a0_ inner join tbl_B b1_ on a0_.b_id=b1_.b_id left outer join tbl_C b2_ on b1_.b_id=b2_.b_id
WHERE a0_.b_id=@p0;@p0 = 277952