Hibernate version: hibernate 3

Name and version of the database we are using: Oracle 10g

This is first time we are developing a J2EE application using Hibernate.
We detected performance problems with queries including several tables and joins.
We normally use annotations or HQL statements.

Those queries done manually in SQL take time from milliseconds to 2 seconds. But if we are using Hibernate same queries take too much, near 50 seconds.

We tried to improve our performance tunning hibernate.cfg.xml

I'm completely sure we are not using Hibernate properly but we followed "Java Persistence with Hibernate" and we don't have any clue to improve our performance.

We also put in place ehcache for static tables (DTOs).

All suggestions are really appreciate

My first step would be to enable Hibernate logging to see what is going on under the hood. There is some information about how to do this in http://www.hibernate.org/hib_docs/v3/re ... on-logging

Enabling the org.hibernate.SQL category will for example print out all SQL statements that Hibernate is executing. This is very often useful information.

You might also consider

http://log4jdbc.sourceforge.net/

in order to time the sql statements issued. This way you'll see if single sql statements, the number of statements or the processing of the results is the problem.

Also be aware of bind variables when comparing 'identical' statements.

select * from t where a = 1
is not the same as
select * from t where a = ?
with 1 provided as a parameter

Things to try:

- put these properties within your hiberate config to enable sql logging:



- Analyse your database mappings, look out for Eager relationships that may cause unnecessary data to be loaded. Especially look out for "n + 1" problems where growing collections are loaded unnecessarily when an object needs to be saved.

- Use DTO's to load only the data you need when reading data from the db.

- Look into hiberate second level caching

We had also implemented cache of second level. ehcache is in place with following properties defined:
<property name="hibernate.cache.provider_class">net.sf.ehcache.hibernate.SingletonEhCacheProvider</property>
<property name="hibernate.cache.use_query_cache">true</property>
<property name="hibernate.cache.use_structured_entries">true</property>

We used annotations like this
@Entity
@Table(name = "LOCAL_ITV")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class AddressDTO implements Serializable
{

// to build the xml
@Transient
private String command = null;

@Id
@Column(name = "LOCAL_ITV_REF", nullable = false)
private Long id;

@Column(name = "DERN_ITV_REF")
private Long refOp;

@Column(name = "NOM_COMM")
private String city;