NH-517 - Unnecessary selects when mapped as class instead of column

[nhibernate-bot]

Ruurd Boeke created an issue — 30th December 2005, 7:04:57:

I've delved deeper into the problem described by: https://nhibernate.jira.com/browse/NH-435
It seems I have the same kind of problem, but it has nothing to do with using an outer select.

Short summary: I'm getting N selects when I have mapped a one-to-many relation, and in the many-side, the composite-ID maps towards the 'one' with a key-many-to-one instead of a key-property.
This means that nHibernate is using one well-constructed query to get all the info it needs, and then sends a SELECT for every row it got back like this: select parentID from parents where parentID = :parentid

In my current situation, this leads to several thousands of selects.

---

Ruurd Boeke added a comment — 30th December 2005, 7:28:52:

I'm attaching a nhspecific test that can be unrarred and ready to be tested.

Two tests are supplied. The one giving the extra SELECTS is: DoTestIncome
I do not know how to assert the amount of sql sent to the db, therefor it will not fail.

But if you look in your output, you will notice the unnecessary call: SELECT nh517paren0*.ParentID as ParentID0_ FROM tstNH517Parent nh517paren0_ WHERE nh517paren0*.ParentID=:p0
That one is not there in the DoTestChildren tst.

I am guessing it is the select to fill the parent object. Therefor we can expect one such call for every distinct entity you got back.

---

justme84 added a comment — 11th January 2006, 6:32:41:

Seems to me the problem is caused by Parent class being non-lazy.

---

Ruurd Boeke added a comment — 12th January 2006, 3:22:06:

No, if I set the parent to lazy in the class definition, I would agree that only 1 select is issued, however, what I get back are uninitialized proxies. the property 'Income' is null.

key-many-to-one cannot be set to lazy.

nhibernate does not recognise that it already has a parent, when it sees the key-many-to-one.

---

justme84 added a comment — 12th January 2006, 6:54:35:

Did you make all properties of lazy classes virtual? There is a problem with key-many-to-one's, because NHibernate sometimes needs to get the object key before doing anything else, and before it has the key it can't know that the object is loaded already. And to get a key-many-to-one NHibernate has to load the object itself, unless it can be proxied.

---

Ruurd Boeke added a comment — 12th January 2006, 7:27:17:

the provided testfixture has a mapping without any properties, only collections.
Maybe I'm misunderstanding you.

However, I don't understand why we are going towards lazy. The whole point of the hql: s1.Find("from NH517Parent p inner join fetch p.Income") is to not get 'income' as lazy.
the bug is that nhibernate doesn't understand that it already has the client (=parent) when it is looking to get the composite-id from income.

---

justme84 added a comment — 12th January 2006, 7:43:10:

We are going towards lazy because making the class lazy will solve your problem. My comment about the properties is related to your saying: "what I get back are uninitialized proxies, the property 'Income' is null" - if you make the property virtual, the proxy should get initialized when you access the property.

It would be too complicated to make this work without laziness because, as I explained, NH doesn't actually know that the object exists until it read its key. And in our case it reads the child key before the parent key, which means NHibernate has to issue another query to load the for the child key, before it actually knows that the parent is already loaded.

Rather than complicate NH source with all the corner cases the Hibernate team recommends against using composite identifiers.

---

Ruurd Boeke added a comment — 12th January 2006, 23:40:44:

Okay, that is clear.

Making the parent lazy does not work as I expect, but that is a different problem then.
I'm afraid I'm stuck with composite keys, so I'll have to work around this another way ;-)

---

Ruurd Boeke added a comment — 12th January 2006, 23:58:07:

I see now, I can access the income which will initialize the proxy. It doesn't seem to send another query to the database.
And ofcourse I can only do this while I have the session open (it's probably finding the class in the cache).

---

Onur Gumus added a comment — 16th June 2011, 0:55:11:

Please give this issue more priority. It is important for me. It causes very low performance if you load a bunch of child classes first.

---

Alexander Zaytsev added a comment — 11th October 2014, 23:07:59:

Test case applied to a branch at https://github.com/hazzik/nhibernate-core/tree/NH-517