[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_14153 ]
     
Sergey Koshcheyev commented on NH-766:
--------------------------------------

Hibernate is probably better, once/if it will be fixed in Hibernate I can then port the fix.

> 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