Hi,

I don't understand why find query whith outer join fetching is very slow.


mapping:

<hibernate-mapping>
<class name="test.Personne" table="RFC_T_PERSONNE" proxy="test.IPersonne">
<id name="UID" type="long" column="PER_N_ID" unsaved-value="0">
<generator class="sequence">
<param name="sequence">X</param>
</generator>
</id>
<property name="nom" type="string" column="PER_C_NOM"/>
<property name="ident" type="string" column="PER_C_NO_IDENT"/>
<property name="tel" type="string" column="PER_C_TEL"/>
<property name="fax" type="string" column="PER_C_FAX"/>
<property name="email" type="string" column="PER_C_EMAIL"/>

<joined-subclass name="test.PersonnePhysique" table="RFC_T_PERSONNE_PHYSIQUE" proxy="test.IPersonnePhysique">
<key column="PEP_N_ID"/>
<property name="prenom" type="string" column="PEP_C_PRENOM"/>
</joined-subclass>
</class>
</hibernate-mapping>

program:
public class HiberTest {
static SessionFactory sf;

public static void main(String[] args) throws Exception {

Configuration cfg = null;
Properties props = new Properties();

try {
cfg = new Configuration()
.addClass(Personne.class)
.addProperties(props);
sf = cfg.buildSessionFactory();
} catch (HibernateException he) {
System.out.println("HibernateException " + he.getMessage());
}

findPersonne();
iteratePersonne();
findPersonne();

}

static void iteratePersonne() throws Exception {
Session session = null;
try {
long start = System.currentTimeMillis();
session = sf.openSession();

String query = "from test.IPersonne personne where personne.nom like :name";
Iterator iter = session.iterate(query, "THIR%", Hibernate.STRING);
while (iter.hasNext()) {
Object o = iter.next();
//Hibernate.initialize(o);
if(o instanceof IPersonne) {
System.out.println(((IPersonne) o).getNom());
//IPersonnePhysique pp = (IPersonnePhysique) o;
//System.out.println(":" + pp.getPrenom());
}
}
System.out.println("iterate executed: " + (System.currentTimeMillis() - start));
} finally {
try {
Connection con = session.close();
} catch (HibernateException he) {
System.out.println("HibernateException " + he.getMessage());
}
}
}

static void findPersonne() throws Exception {
Session session = null;
try {
long start = System.currentTimeMillis();
session = sf.openSession();

String query = "from test.IPersonne personne where personne.nom like :name";
List result = session.find(query, "THIR%", Hibernate.STRING);
for (Iterator iter = result.iterator(); iter.hasNext();) {
Object o = iter.next();
//System.out.println(o instanceof PersonnePhysique);
}
System.out.println("find executed: " + (System.currentTimeMillis() - start));

// make sure that you flush and commit the changes to the database
session.flush();
session.connection().commit();

} finally {
try {
Connection con = session.close();
} catch (HibernateException he) {
System.out.println("HibernateException " + he.getMessage());
}
}
}
}

result with Oracle:
find executed: 984
iterate executed: 422
find executed: 797

the SQL query without Hibernat take 32 ms

Thanks

Try turning on hibernate.show_sql, to see what Hibernate is actually *doing*

find request:
Hibernate: select personne0_.PER_N_ID as PER_N_ID, decode (personne0_.PER_N_ID, personne0__1.PEP_N_ID, 1,0 ) as clazz_, personne0_.PER_C_NOM as PER_C_NOM0_, personne0_.PER_C_NO_IDENT as PER_C_NO3_0_, personne0_.PER_C_TEL as PER_C_TEL0_, personne0_.PER_C_FAX as PER_C_FAX0_, personne0_.PER_C_EMAIL as PER_C_EM6_0_, personne0__1.PEP_C_PRENOM as PEP_C_PR2_1_ from RFC_T_PERSONNE personne0_, RFC_T_PERSONNE_PHYSIQUE personne0__1 where personne0_.PER_N_ID=personne0__1.PEP_N_ID(+) and ((personne0_.PER_C_NOM like ? ))

find executed: 938 ms
27 items

iterate request:
Hibernate: select personne0_.PER_N_ID as x0_0_ from RFC_T_PERSONNE personne0_ where (personne0_.PER_C_NOM like ? )
Hibernate: select personne0_.PER_N_ID as PER_N_ID, decode (personne0_.PER_N_ID, personne0__1.PEP_N_ID, 1,0 ) as clazz_, personne0_.PER_C_NOM as PER_C_NOM0_, personne0_.PER_C_NO_IDENT as PER_C_NO3_0_, personne0_.PER_C_TEL as PER_C_TEL0_, personne0_.PER_C_FAX as PER_C_FAX0_, personne0_.PER_C_EMAIL as PER_C_EM6_0_, personne0__1.PEP_C_PRENOM as PEP_C_PR2_1_ from RFC_T_PERSONNE personne0_, RFC_T_PERSONNE_PHYSIQUE personne0__1 where personne0_.PER_N_ID=? and personne0_.PER_N_ID=personne0__1.PEP_N_ID(+)
THIRE
......

iterate executed: 438 ms

Thanks

1) You should *always* do benchmarks in a loop, esp. in a hotspot JVM
2) Your test is also measuring the time taken to obtain a JDBC connection and start a transaction (note that the connection is first obtained during this find() call)


If your tests were implemented sensibly, there is no measurable difference between the performance of find(), and the direct JDBC.

Thank you,

But i use DCP. If i pass the connection in the session the result is equivalent.
The test executes one find method, one iterate method and find method twice again:

static void findPersonne() throws Exception {
Session session = null;
try {
Connection con = getConnection();
long start = System.currentTimeMillis();
session = sf.openSession(con);

String query = "from test.IPersonne personne where personne.nom like :name";
List result = session.find(query, "THIR%", Hibernate.STRING);
for (Iterator iter = result.iterator(); iter.hasNext();) {
Object o = iter.next();
//System.out.println(o instanceof PersonnePhysique);
}
System.out.println("find executed: " + (System.currentTimeMillis() - start));

} finally {
try {
session.close().close();
} catch (HibernateException he) {
System.out.println("HibernateException " + he.getMessage());
}
}
}

The main method:
public static void main(String[] args) throws Exception {

Configuration cfg = null;
Properties props = new Properties();

try {
cfg = new Configuration()
.addClass(Personne.class)
.addProperties(props);
sf = cfg.buildSessionFactory();
} catch (HibernateException he) {
System.out.println("HibernateException " + he.getMessage());
}

findPersonne();
iteratePersonne();
findPersonne();
findPersonne();

}

Thanks