Hi,
Loading objects in hibernate seems to be very slow for large data sets,
We tried Loading the same set of data using simple jdbc call and the results are significantly better than hibernate.


We are using Hibernate 2.0, MS SQL 8.0 on Windows XP Platform.

Could anybody tell us what are we doing wrong here?

Here's is the code to load,

public Object load(String dbName, Class oClass, Serializable oId) {

Object o = null;
Connection connection = null;
Session session = null;
Transaction tx = null;

try {
connection = this.dbService.getDBPool(dbName).borrowConnection(this.getClass());
session = this.sessionFactory.openSession(connection);
tx = session.beginTransaction();
o = session.load(oClass, oId, LockMode.NONE);
connection.commit();
tx.commit();
} catch (Exception e) {
try {
connection.rollback();
} catch (SQLException se) {
throw new DBException(se);
}
throw new DBException(e);
} finally {
try {
session.close();
connection.setAutoCommit(true);
} catch (HibernateException e) {
throw new DBException(e);
} catch (SQLException e) {
throw new DBException(e);
}
this.dbService.getDBPool(dbName).returnConnection(this.getClass(), connection);
}
return o;
}

Here's my mapping file

<hibernate-mapping>

<class
name="insite.business.core.buyer.CostCenter"
table="cost_center"
dynamic-update="true"
dynamic-insert="true">

<id name="id" column="cost_center_id" type="java.lang.String">
<generator class="assigned"/>
</id>

<property name="name" type="string" column="name" not-null="true" />
<property name="code" type="string" column="code" not-null="true" />
<property name="activeFlag" type="boolean" column="active_flag" length="1" not-null="true"/>
<property name="buyerCode" type="string" column="buyer_code" length="4" not-null="true" />
<property name="description" type="string" column="description"/>
<property name="workEnv" type="string" column="work_env"/>
<property name="createTime" type="timestamp" column="create_time"/>
<property name="revisionTime" type="timestamp" column="revision_time"/>
<property name="closedReasonId" type="string" column="closed_reason_id" length="24"/>
<property name="closedTime" type="timestamp" column="closed_time"/>
<property name="currency" type="string" column="currency" not-null="true" length="3"/>
<property name="ownerId" column="owner_id" type="string" length="24"/>

<set name="expenseSet" table="cost_center_expense" lazy="false" cascade="all">
<key column="cost_center_id"/>
<element type="string" column="expense_id"/>
</set>

<set name="taskSet" table="cost_center_task" lazy="false" cascade="all">
<key column="cost_center_id"/>
<element type="string" column="task_id"/>
</set>

<set name="customFieldSet" lazy="false" table="cost_center_custom_field" cascade="all">
<key column="cost_center_id"/>
<composite-element class="insite.business.core.ObjectCustomField">
<property name="customFieldId" type="string" column="custom_field_id"/>
<property name="value" type="string" column="value"/>
</composite-element>
</set>

</class>

</hibernate-mapping>

You are using lazy=false everywhere, this will load your whole object graph, without using outer join, as you have nowhere specified the outer-join attribute. Look at the log and check the generated sql.

I used outer-join="true" but xml parser throws following error,

org.xml.sax.SAXParseException: Attribute "outer-join" is not declared for element "set".
at org.apache.crimson.parser.Parser2.error(Parser2.java:3210)

Here is the generated sql,

Hibernate: select costcent0_.cost_center_id as cost_center_id, costcent0_.name as name, costcent0_.code as code, costcent0_.active_flag as active_f4_, costcent0_.buyer_code as buyer_code, costcent0_.description as descript6_, costcent0_.work_env as work_env, costcent0_.create_time as create_t8_, costcent0_.revision_time as revision9_, costcent0_.closed_reason_id as closed_10_, costcent0_.closed_time as closed_11_, costcent0_.currency as currency, costcent0_.owner_id as owner_id from cost_center costcent0_ where (costcent0_.active_flag=1 ) order by costcent0_.name

Hibernate: select cost_cen0_.expense_id as expense_id__ from cost_center_expense cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.task_id as task_id__ from cost_center_task cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.custom_field_id as custom_f2___, cost_cen0_.value as value__ from cost_center_custom_field cost_cen0_ where cost_cen0_.cost_center_id=?

Hibernate: select cost_cen0_.expense_id as expense_id__ from cost_center_expense cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.task_id as task_id__ from cost_center_task cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.custom_field_id as custom_f2___, cost_cen0_.value as value__ from cost_center_custom_field cost_cen0_ where cost_cen0_.cost_center_id=?

Hibernate: select cost_cen0_.expense_id as expense_id__ from cost_center_expense cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.task_id as task_id__ from cost_center_task cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.custom_field_id as custom_f2___, cost_cen0_.value as value__ from cost_center_custom_field cost_cen0_ where cost_cen0_.cost_center_id=?

Hibernate: select cost_cen0_.expense_id as expense_id__ from cost_center_expense cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.task_id as task_id__ from cost_center_task cost_cen0_ where cost_cen0_.cost_center_id=?
Hibernate: select cost_cen0_.custom_field_id as custom_f2___, cost_cen0_.value as value__ from cost_center_custom_field cost_cen0_ where cost_cen0_.cost_center_id=?

.....

Are you using the correct doctype?



Well you see yourself what is happening in the generated SQL. I suggest you work some with lazy, outer-join and the join fetch clause in HQL and learn how to control hibernate loading behaviour, before you are doing performance measurements.

In most cases it is not a big problem to get Hibernate to execute exactly the same SQL your manual loading approach would do.

I was trying loading a difft object (Invoice), and found this strange behaviour. if u look at the last sql executed it is executing delete sql's


Hibernate: select invoice0_.invoice_id as invoice_id, invoice0_.paid_amount as paid_am29_ from invoice invoice0_ where invoice0_.invoice_id=?
Hibernate: select invoice_0_.invoice_adj_id as invoice_2___, invoice_0_.reason_id as reason_id__, invoice_0_.amount as amount__ from invoice_adj invoice_0_ where invoice_0_.invoice_id=?
Hibernate: select invoice_0_.object_id as object_id__, invoice_0_.module_id as module_id__ from invoice_detail invoice_0_ where invoice_0_.invoice_id=?
Hibernate: select invoice_0_.cost_center_id as cost_cen2___, invoice_0_.tax_amount as tax_amount__, invoice_0_.amount as amount__ from invoice_cost_center invoice_0_ where invoice_0_.invoice_id=?
Hibernate: select invoice_0_.custom_field_id as custom_f2___, invoice_0_.value as value__ from invoice_custom_field invoice_0_ where invoice_0_.invoice_id=?
Hibernate: delete from invoice_detail where invoice_id=? and object_id=? and module_id=?

To my delete problem,
Sorry, I had not implemented the hashCode properly. But wouldn't this be handled differenlty. Generating delete on load will be dangerous.