I was passed off a java project at my company that is structured with hibernate and struts. The entire downfall of this project seems to revolve around hibernate, and the amount of overhead it is causing.

I checked the query logs for the database, and I am seeing upwards of 6500, yes SIX THOUSAND FIVE HUNDRED, queries being executed just on a login procedure. You would think only ONE query would be needed.

Essentially, the software is a web based application for a study going on at a hospital, and is basically a patient enrolment tool, as well as a tracking tool for a patient's daily progress. Each user is assigned to an ICU (Intensive care unit) in which they can enrol patients under. The database access is basically linked as follows:

User account > ICU > patients under ICU > daily data entered for the patient each day

I have tried to use lazy loading to kind of isolate each section to cut down on the amount of database requests, but it only help marginally, and as the database is growing in size as more patients are enroled, it gets even slower.

Does anyone have any general tips for hibernate optimization. Clients are getting upset, and I really need to come up with a solution soon.

It really seems hibernate is more of a burden then it helps, but unfortunately, the application is so heavily structured on it, that it would not be feasible to convert it to the very easy to use JDBC to result set standard DB access.

Lots of views, no replies...

ANY help would be appreciated. If you need more elaboration on anything, please let me know.

Are you using connection pooling and/or object/query cache?

Post here your hibernate.cfg.xml and the table mappings used on the user login.

My hibernateconfig file is over 1,200 lines. Is there any portion you would specifically want to see?

Edit: Sorry, I thought you meant my hibernate mappings. Here is hibernate.cfg.xml.


<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>

<session-factory>
<property name="hibernate.cglib.use_reflection_optimizer">false</property>
<property name="connection.datasource">java:comp/env/jdbc/ABATE</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="show_sql">false</property>
<!-- <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</property>-->
<mapping resource="hibernate_mappings.xml" />
</session-factory>

</hibernate-configuration>

And my user object mappings

I think you could set lazy="true" for the sets you don't need to use at the login process.

Are you getting 6500 different queries or the same queries are appearing in multiples?

Hi ,
You might have already done this..but did you see if this is n+1 select issue? are your 6500 selects going against the same set of tables?

Thanks.

To continue the point above about n+1 selects:

It may be due to excessive object navigation. Are their any FOR loops over domain objects?

For example, I came across some code recently that was iterating over all the Company domain objects and then for each one checking if it had a valid licence. This is so wasteful as it will also include companies no longer valid. Each company also has an Address component that will always be loaded (i dont think that can be turned off).

The classic n+1 selects problem.

The solution was to write some HQL that just got back the valid companies. I suspect there is ALOT of code out there like this.

R