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