[hibernate-dev] Optimization of db queries for many-to-one associtation by unique key

[Anton Vodonosov]

Hello,

I want to suggest one optimization that significantly reduces 
number of DB queries in certain cases.

First about the problem:

We have two entities: EMany, EOne (mapped to tables TMANY, TONE).
These entities are associated using @ManyToOne on two columns (not the primary key).

When we select EMany, hibernate generates left outer join, similar to:

   select TMANY.ID, TMANY.a, TMANY.b, TONE.ID, TONE.x, TONE.y
   from TMANY left otuer TONE on TMANY.a = TONE.x, TMANY.b = TONE.y

In case if TONE record is present in DB, the outer join returns its columns and 
hibernate creates both EMany and EOne objects from the same result set.

But in case if TONE record is absent in DB, outer joint returns NULLs for
TONE columns, and hibernate tries to resolve EOne entities by
additional queries:

   select ... from TONE where TONE.x = <EMany.a> and TONE.y = <EMany.b>

This query is performed as many times as EMany records were selected, and
always return nothing.

The proposal is to not perform this additional query in case if 
result set have primary key column TONE.ID = NULL; we can be sure 
there is no DB record if primary key column is NULL.

I've tried to fix this in the EntytyType.java, seems working. 

My code is in the attach (the diff is against the latest release 
hibernate-3.3.1.ga)

Best regards,
- Anton
-------------- next part --------------
A non-text attachment was scrubbed...
Name: EntytyType.java.diff
Type: application/octet-stream
Size: 1613 bytes
Desc: not available
Url : http://lists.jboss.org/pipermail/hibernate-dev/attachments/20090304/dff49567/attachment.obj