Hi,

I have hibernate 3.3.1, I have this hql query;

String sqlQuery = "select distinct pratica from PraticaBean as pratica " +
"left join fetch pratica.zona " +
"left join fetch pratica.dettagliPratica " +
"left join fetch pratica.abusi ab " +
"left join fetch ab.caratteristiche " +
"left join fetch ab.naturaDelVincolo " +
"left join fetch ab.comunicante " +
"left join fetch ab.interessato " +
"left join fetch ab.utente " +
"left join fetch ab.pratica " +
"left join fetch ab.stato " +
"left join fetch ab.zonaOmogenea " +
"left join fetch ab.paragrafo " +
"left join fetch ab.paragrafo.articolo " +
"left join fetch ab.particelleCatastali " +
"left join fetch ab.soggetti ";

this hql query is resolved in a single (but very long) sql query.

If I run this query from a java application that use hibernates it thakes about 300 msec, if i run the same query from a web abbplication (tomact + spring + hibernate) it takes several seconds

How can I explain that?

How can I optimize it?

Thanks in advanced

Nicola

Hello ,

Just go through this article and optimize your complex query

http://www.javalobby.org/articles/hiber ... background

Thank you very mutch for your reply, I will try it.

But:
How can you explain the difference between hundred milliseconds to tens of second if I use stand alone java project with hibernate and if i use Tomact + Spring + Hibernate project?

Nicola

Hi Nicola,

The above link can optimize your Hibernate part . i.e. the database fetch part alone.

Generally in Web-Application the database access consumes lot of time, but as you mentioned , are doing any heavy processing in your web-application

again thanks for your support.

The article you suggested asked me to try

"select new CityItem(city.id, city.name, city.electrityCompany.name) from City city"

This "to avoid the overheads of handling associated objects and of loading large numbers of persistent objects into the Hibernate session cache"

But how can I do if my left join is used to many to one associations?

Nicola

According the article suggested, i simplified the hql query

before:

"select distinct pratica from PraticaBean as pratica " +
"left join fetch pratica.zona " +
"left join fetch pratica.dettagliPratica " +
"left join fetch pratica.abusi ab " +
"left join fetch ab.caratteristiche " +
"left join fetch ab.naturaDelVincolo " +
"left join fetch ab.comunicante " +
"left join fetch ab.interessato " +
"left join fetch ab.utente " +
"left join fetch ab.pratica " +
"left join fetch ab.stato " +
"left join fetch ab.zonaOmogenea " +
"left join fetch ab.paragrafo " +
"left join fetch ab.paragrafo.articolo " +
"left join fetch ab.particelleCatastali " +
"left join fetch ab.soggetti "

after I split the query in :

String sqlQuery = "select " +
"ab.id, " +
"ab.pratica.numero, " +
"ab.pratica.zona.comune, " +
"ab.paragrafo.articolo.nome, " +
"ab.descrizione, " +
"ab.localita " +
" from AbusoLazyBean as ab";

and for each entry of the previuos select I perform

String sqlQuerySoggetti = "select " +
"soggetto.tipo, " +
"soggetto.nome, " +
"soggetto.cognome " +
"from SoggettoBean as soggetto " +
"where abuso.id=" + idAbuso;

this resolve one join

but I didn't reach the target I have with java application

first sql in java application it takes 300 msec
first sql in web applicationit takes 75 sec
the combination of second and third query take 11 sec

Could you help me to improve the performance?

Nicola

Hi Nicola

"the combination of second and third query take 11 sec " whether it is in the web application
or it is in plain hibernate part ?

As you mentioned in hibernate it takes 300 milli secs but in web application it takes 75 secs ...

Are you doing heavy processing in web- application ?

How you are connecting to database ( esp connection pool settings ) ?

Another angle to increase the performance of hibernate is the use of cache .

Heavy processing? I only measure the time of

WEB APPLICATION

Query query = session.createQuery(sqlQuery);
long t1 = Calendar.getInstance().getTimeInMillis();
Object obj = query.list();
long t2 = Calendar.getInstance().getTimeInMillis();
System.out.println("query.list:" + (t2-t1) + "(ms)");

JAVA APPLICATION (no spring)

t1 = Calendar.getInstance().getTimeInMillis();
lst = (List<PraticaBean>) hibernateTemplate.execute(new TemplateCustomHibernateCallback<PraticaBean>(sqlQuery,paramMap, true));
t2 = Calendar.getInstance().getTimeInMillis();
System.out.println("hibernateTemplate.execute:" + (t2-t1) + "(ms)");

The mapping is shared; the application context i use in the web app is

...
<bean id="mySessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="myDataSource" />
<property name="mappingResources">
<list>
<value>
osservatorio/persistance/mapping/Osservatorio.hbm.xml
</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
<prop key="hibernate.show_sql">true</prop> <!-- I tried also false -->
<prop key="hibernate.connection.pool_size">10</prop> <!-- I tried also 1 -->
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean>


<!-- hibernateTemplate -->
<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
<property name="sessionFactory">
<ref bean="mySessionFactory" />
</property>
</bean>

<!-- Classi DAO -->
<bean id="configDao" class="osservatorio.persistance.dao.impl.ConfigDao">
<property name="hibernateTemplate">
<ref bean="hibernateTemplate" />
</property>
</bean>
...

I note that elapsed time is strongly related to the ammount of data
Could it be the cache? how can I increase it?

Hi

"the combination of second and third query take 11 sec " whether it is in the web application
or it is in plain hibernate part ?


Whatever be the case you cannot get such difference in time (i.e. 300 msec and 75 sec ) .

I faced this problem and i fixed it by optimizing the hibernate query and cache.

I did n't use the hibernate default connection bcos the default connection pool is not for production system .

I used c3p0 connection pool .

Hi,

And thanks for the hit

I did't test the combination of second and third in plain hibernate part but only in web application

Could you post you application context (where you set the c3p0) I'm very curious to try it

Nicola

Ya ,

c3p0 properties
<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.max_size">5</property>
<property name="hibernate.c3p0.max_statements">5</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.timeout">100</property>
<property name="hibernate.c3p0.autoCommitOnClose">true</property>
<property name="hibernate.c3p0.acquireRetryDelay">1000</property>
<property name="hibernate.c3p0.acquireRetryAttempts">60</property>
<property name="hibernate.c3p0.breakAfterAcquireFailure">false</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="hibernate.c3p0.max_size">100</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.timeout">100</property>

more or less the same time 64 sec

I start to become crazy :-(

The hql is converted in the same sql so why the execution time is so different?

Could it be the problem is not hibernate but spring?

I dont have much idea about spring. As i said earlier the excution time of hibernate while calling from Java application and from a web application should not have much difference . Debug your spring code .

After some day of fighting I fix the problem.

It was very stupid: jta_1.1.jar was missing from my web abb: now everithin work fine!

Many thanks to every body for the support

Nicola