join fetch making extra query

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

The mapping fragments below work. They pull back the data I want to see. But there appears to be an extra query being made that should not need to be made, at least it seems that way to me.

Granted that this may not be the most wonderful relational model (we've discussed that before), I still want to optimize it, and extra selects bother me.

Pay particular attention to the <many-to-one> from CallLogEntry to ContactName. Although I have specified outer-join and fetch="join" it still seems to be doing a select fetch.

In the generated SQL below, why is the second query being done? It seems completely redundant. Hasn't the first query already fetched this data? I've tried many different attributes on the many-to-one, none of them made a difference.

Also I added this to hibernate.cf.xml, also without effect.

Code:

Another point of note that may or may not be relevant is that this is generated by a criteria query

Please indicate whether I am missing something and a solution if possible.

Hibernate version: 3.0.5

Mapping documents:
CallLogEntry

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="CallLogEntry"
        table="call_log_entry">
        <id name="id" type="java.lang.Long" column="id">
            <generator class="seqhilo">
                <param name="max_lo">100</param>
                <param name="sequence">call_log_entry_seq</param>
            </generator>
        </id>
        <many-to-one name="lSession" not-null="true">
            <column name="lsession_id" index="call_log_ndx"/>
        </many-to-one>
        <property name="callType" not-null="true">
           <column name="call_type" length="8" index="call_log_ndx"/>
        </property>
...
             
        <component name="otherTn"
            class="TelephoneNumber">
            <property name="rawTn" column="other_tn" length="20"
                not-null="true" />
        </component>
        <many-to-one name="contactName" 
                 not-null="false" not-found="ignore"
                 class="ContactName" 
                 insert="false" update="false"
                 fetch="join" lazy="false"
                 outer-join="true"
                 property-ref="contactNameKey">
         <column name="lsession_id" />
         <column name="other_tn"/>                 
        </many-to-one>

    
    </class>
    

   <query name="callLogCounts"><![CDATA[
   select count(entry), entry.callType 
      from CallLogEntry entry
      where entry.lSession=:lsess
      group by entry.callType
   ]]></query>
   
    
</hibernate-mapping>

ContactName

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="ContactName"
      table="contact_name">
      <id name="id" type="java.lang.Long"/>
   
      <property name="firstName" column="first_name" length="50"/>
      <property name="lastName" column="last_name" length="50"/>
      <property name="nickname" length="50"/>
      <property name="lSessionId" column="lsession_id" insert="false" update="false" not-null="true"/>

       
    <properties name="contactNameKey" unique="true">
       <many-to-one name="lSession" column="lsession_id" not-null="true" />
      <property name="tn" column="phone_number" length="100" not-null="true" />
     </properties>
    </class>
    
   <query name="deleteContactsBySession"><![CDATA[
      delete ContactName where lSessionId =:lsessid
   ]]></query>
   
</hibernate-mapping>

The generated SQL (show_sql=true):

Code:

select * from ( 
            select this_.id as id1_, 
                   this_.l_session_id as l_s2_12_1_, 
                   this_.call_type as call3_12_1_, 
   ...
                                            this_.other_tn as other9_12_1_, 
                   contactnam2_.id as id0_, 
                   contactnam2_.first_name as first2_10_0_, 
                   contactnam2_.last_name as last3_10_0_, 
                   contactnam2_.nickname as nickname10_0_, 
                   contactnam2_.l_session_id as l_s5_10_0_, 
                   contactnam2_.phone_number as phone6_10_0_ 
            from call_log_entry this_ 
            left outer join contact_name contactnam2_ 
            on this_.l_session_id=contactnam2_.l_session_id 
            and this_.other_tn=contactnam2_.phone_number 
            where this_.l_session_id=? 
            and this_.call_type=? 
            order by this_.call_start_time_stamp desc ) 
      where rownum <= ?

select contactnam0_.id as id0_, 
       contactnam0_.first_name as first2_10_0_, 
       contactnam0_.last_name as last3_10_0_, 
       contactnam0_.nickname as nickname10_0_, 
       contactnam0_.l_session_id as l_s5_10_0_, 
       contactnam0_.phone_number as phone6_10_0_ 
from contact_name contactnam0_ 
where contactnam0_.l_session_id=? 
and contactnam0_.phone_number=?

jbosseur · **Posted:** Thu Jun 08, 2006 1:39 pm

Since you did not post any Java code showing how you
obtain the result I assume you are using HQL or Criteria:

read the following:
19.1.2. Tuning fetch strategies

Basicly: the fetch stratey you inform in your mapping document ony affects load and get.... and when you navigate...
In HQL and Criteria you have to specify excplicitly what you want.
like :

Code:

     from A
     inner join fetch a.b....

Good luck!

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

Thanks. I did specify I was using a Criteria query, but I'm sure it was easy to overlook in my original (long) post.

The documentation seems to say that mapping document fetch strategies apply to Criteria queries. ( I am using 3.0.5, so this is in Chapter 20 of the manual I'm using.

At any rate, here is the java code you asked for. The setFetchMode call was not there originally, but seems to have had no effect, as the documentation cited above would seem to indicate.

Code:

sess.createCriteria(CallLogEntry.class)
.add(...)
.addOrder(...)
.setMaxResults(pageSize)
.setFirstResult((pageNumber-1) * pageSize)
.setFetchMode("CallLogEntry.contactName", FetchMode.JOIN)
.list();

jbosseur · **Posted:** Thu Jun 08, 2006 2:22 pm

Sorry about that.... hibernate 3.0.5 is no longer online...:(

change in your Criteria

stevecoh2 wrote:

.setFetchMode("CallLogEntry.contactName", FetchMode.JOIN)

To:

Code:

.setFetchMode("contactName", FetchMode.JOIN)

I think this should do the trick....

Good luck

stevecoh2 · **Joined:** Tue Mar 21, 2006 11:01 am **Posts:** 65

Still doing the 2nd query. :-(

could the presence of the .setMaxResults() and .setFirstResult() be influencing it?

jbosseur · **Posted:** Thu Jun 08, 2006 2:47 pm

You are right about what it says using Criteria queries in the docs...
but I always specify excplicitly what I want in the Criteria...

Unfortunately I am not able to test anything at the moment...
maybe toninght at home I will have a go...

try to play arround a bit with the cofiguration and the criteria...

jbchurn · **Posted:** Tue Jun 13, 2006 5:15 am

I understand the meaning of

Code:

outer-join=true

and

Code:

outer-join=false

, but what does it mean when

Code:

outer-join=auto

?

Please advise.
Thank you.

zanfolim · **Joined:** Thu Mar 25, 2004 1:43 pm **Posts:** 3

I had the same issue, and the following seems to have fixed it for my case:

Quote:

outer-join="true

" .