[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2170) Avoid joins on <composite-key><key-many-to-one> queries

[Kirk Wylie (JIRA)]

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.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira