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

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

     [ http://jira.nhibernate.org/browse/NH-766?page=comments#action_14152 ]
     
Kirk Wylie commented on NH-766:
-------------------------------

I can try to, but extracting one given a relatively proprietary schema (that I had to extract things out of above) might take some time. Given that it affects Hibernate as well, and I'm going to that effort, do you recommend doing that in Hibernate (JUnit) or NHibernate (NUnit)?

> 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