Criteria vs. HQL

cstefkivoila · **Joined:** Thu May 29, 2008 1:10 pm **Posts:** 2

Both queries below return the same list.
But the first takes less than 1 second and the second takes 9 seconds!
Why is the Criteria example slower than pure HQL?

Code:

String hqlQuery = "from Patient as p where p.forename1 like 'ste%' and p.surname like 'mor%'";
List<Patient> patients = session.createQuery(hqlQuery.toString()).list();

Code:

Criteria crit = session.createCriteria(Patient.class);
crit.add(Restrictions.like("forename1", "ste", MatchMode.START));
crit.add(Restrictions.like("surname", "mor", MatchMode.START));
List<Patient> patients = crit.list();

Tested on:
Hibernate 3.2.6ga
MS SQL Server 2000
Windows XP

stolley · **Posted:** Thu May 29, 2008 7:14 pm

Turn on Hibernate SQL statment logging (it's in the manual somewhere).
Look at the SQL statements being generated, probably a lot more for the criteria API version, right?

HQL and Criteria API have treat default fetch profiles differently. The Criteria version is probably eagerly loading a collection or relationship that the HQL version is not. You will need to read the manual about things like lazy vs. eager fetching and how to specify that kind of stuff with the critieria API and HQL API.

Of course, I'm just guessing that that is the problem.

Could be space hamsters.

cstefkivoila · **Joined:** Thu May 29, 2008 1:10 pm **Posts:** 2

Here are the Hibernate SQL statement logging:

For HQL query:

Code:

select patient0_.ID as ID16_, patient0_.FORENAME_1 as FORENAME2_16_, patient0_.FORENAME_2 as FORENAME3_16_, patient0_.SURNAME as SURNAME16_ from VW_PATIENTS patient0_ where (patient0_.FORENAME_1 like 'ste%') and (patient0_.SURNAME like 'mor%')

For Criteria:

Code:

select this_.ID as ID16_0_, this_.FORENAME_1 as FORENAME2_16_0_, this_.FORENAME_2 as FORENAME3_16_0_, this_.SURNAME as SURNAME16_0_ from VW_PATIENTS this_ where this_.FORENAME_1 like ? and this_.SURNAME like ?

As you can see, it produces exactly the same SQL query. So how Criteria could be slower than HQL?

Here is my hbm file for information:

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="org.hhnt.common.domain.patient.Patient" table="TBL_PATIENTS">
      <id name="id" 
         column="ID" 
         type="long" >
         <generator class="assigned"/>         
      </id>

      <property name="forename1" 
         column="FORENAME_1" 
         type="string" 
         length="35" 
      />

      <property name="forename2" 
         column="FORENAME_2" 
         type="string" 
         length="35" 
      />

      <property name="surname" 
         column="SURNAME" 
         type="string" 
         length="35" 
      />

   </class>
</hibernate-mapping>

schauder · **Joined:** Thu Jul 05, 2007 9:38 am **Posts:** 287

Thy aren't creating the same sql.

HQL is concatanating the values into the sql - string while Criteria uses bind variables.

This can make a huge difference for the optimizer of the database.

Also the HQL aproach as it stands is risking SQL-Injection attacks.

kind regards
Jens Schauder