[nhibernate-issues] [JIRA] Created: (NH-766) Avoid joins on <composite-key><key-many-to-one> queries

[NHibernate J. <mik...@us...>]

Avoid joins on <composite-key><key-many-to-one> queries
-------------------------------------------------------

         Key: NH-766
         URL: http://jira.nhibernate.org/browse/NH-766
     Project: NHibernate
        Type: Improvement
  Components: Core  
    Versions: 1.0.2    
    Reporter: Kirk Wylie
    Priority: Trivial


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.

After further investigation, I can establish that Hibernate 3.1.3 exposes the exact same functionality. I'm filing this first against NHibernate since that's where I first discovered it, however, since it affects Hibernate as well it might be better filed against that, and I can do so if needed.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://jira.nhibernate.org/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira