[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2170) Avoid joins on <composite-key><key-many-to-one> queries
Avoid joins on <composite-key><key-many-to-one> queries
-------------------------------------------------------
Key: HHH-2170
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2170
Project: Hibernate3
Type: Improvement
Components: core
Versions: 3.1.3
Environment: Hibernate 3.1.3, Oracle 9.0.2, Oracle9 dialect
Reporter: Kirk Wylie
This is a copy of NHibernate #NH-766, based on a conversation with Sergey, because it
appears in both Hibernate and NHibernate: http://jira.nhibernate.org/browse/NH-766
COPIED TEXT BELOW:
This is being promoted from something I posted on the NHibernate forums.
Using NHibernate 1.0.2.0 against Oracle 9.
I'm working with an existing schema which isn't using surrogate keys, and I have a
composite key of the form:
<class name="CalculatedRating" ...>
<composite-id>
<key-property name="EffectiveDate"
column="EFFECTIVE_DATE"/>
<key-many-to-one name="FdeOrganization"
column="ORGANIZATION_ID" .../>
</composite-id>
...
</class>
Using this query:
from CalculatedRating cr
inner join fetch cr.FdeOrganization fdeOrg
left join fetch fdeOrg.Attributes as attribute
inner join fetch attribute.AttributeType
where cr.EffectiveDate =
(select max(cr2.EffectiveDate)
from CalculatedRating cr2
where cr2.EffectiveDate >= :EffectiveDate)
order by cr.FdeOrganization.OrgId
NHibernate is quite correctly generating the SQL necessary to completely manifest the
CalculatedRating element in a single query. However, after executing that query, it then
still proceeds to do the N+1 select on FdeOrganization.
As in this application I'm actually using quite a few cases where I'm successfully
using the inner join fetch technique against FdeOrganization, I can only assume that this
is a problem related to key-many-to-one rather than many-to-one.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira