Hibernate - Avoiding unnecessary join when using foreign key in where clause

Question

Hi I try to optimize the database queries in Hibernate, but I found a blocker:

<class name="SupportedLanguageVO" table="AR_SUPPORTED_LANG" >
    <cache usage="read-only"/>
 <id name="Id" type="java.math.BigInteger">
  <column name="ID" sql-type="NUMBER(20)" not-null="true"/>
  <generator class="assigned"/>
 </id>
    <property name="OrderSeq" type="java.math.BigInteger">
  <column name="ORDER_SEQ" sql-type="NUMBER(20)" not-null="true"/>
 </property>
    <many-to-one name="Country" class="CountryVO" column="CTRY_CD_ID" cascade="none" >
    <many-to-one name="Language" class="LanguageVO" column="LANG_CD" cascade="none" >

    </class>

The primary key of the Country is the CTRY_CD_ID. If I run the following criteria

  Criteria crit = m_Session.createCriteria(SupportedLanguageVO.class);
            crit.createCriteria("Country").add(Restrictions.eq("_CountryCode", p_countrycode));
            crit.addOrder(Order.asc("OrderSeq"));

I can see, that hibernate joins the ctry and the AR_SUPPORTED_LANG tables. Why? It would be better to run

select * from AR_SUPPORTED_LANG where ctry_cd_id=?

sql rather than

select * from AR_SUPPORTED_LANG inner join ctry .... where ctry_cd_id=?

Can I force hibernate to run the first query?

Answer 1

Try explicitly setting the fetch mode for your criteria:

crit.setFetchMode("Country", FetchMode.SELECT);

Answer 2

Why? It would be better to run...

That's not necessarily true, and in fact depends heavily on how your database optimizes its queries. Generally speaking, inner joining will be more efficient because it has the opportunity to vastly reduce the search scope. Of course, with a simple type table with only a couple dozen rows it looks like overkill. Add a couple million rows and you'll see the difference.

For a similar reason, it's generally optimal to add any query hints that you can to joins. For instance (rewriting your query in HQL):

from AR_SUPPORTED_LANG inner join ctry c where c.cd_id=?

...should be...

from AR_SUPPORTED_LANG inner join ctry c WITH c.cd_id=?

The WITH clause is an HQL-specific method of adding AND clauses to JOIN statements.

Answer 3

I think you can dot it. You should apply the eq directly on the country object :

Criteria crit = m_Session.createCriteria(SupportedLanguageVO.class);
crit.add(Restrictions.eq("Country", p_country));
crit.addOrder(Order.asc("OrderSeq"));

That way, if i remember well, hibernate should optimize the query the way you want. But that means you need the country object, not only the country code.