• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Performance concern with Select Query

Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm working on a tomcat based java application. This application uses only hibernate with c3p0 as connection pool.
Currently i'm performing a stress test of 30 Transaction per second for 90 minutes. hibernate3.0 and c3p0-
Database is oracle 9i.

The transaction uses a select query which contains join of 2 tables. Number of rows in each table is 10,000. (which is very less)
I retreive a single row in that query using inner join. This query is used twice in a single transaction. Hence at 60 TPS data is retreived via this query.

Similar example:

Dept Table

deptId deptName
01 Maths
02 English
and so on...

Emp Table

empId empName deptId status
emp1 amit 01 Y
emp2 anupam 02 Y

select emp.empid, emp.empName, emp.deptId, dept.deptName from Emp emp, Dept dept where dept.deptId=emp.deptId and emp.status='Y';
(Query is wrtten in hql. It reteives 20 column values.)

After nearly 45 minutes of load suddenly the query performs very slow taking 10 to 15 secs for 2 to 3 secs only and after that works fine. At this point in time other queries that uses same c3p0 pool works fine.
This happens 4 to 5 times in a duration of 90 minutes.

My hibernate connection pool config:

<property name="hibernate.c3p0.initial_size">20</property>
<property name="hibernate.c3p0.min_size">20</property>
<property name="hibernate.c3p0.max_size">300</property>
<property name="hibernate.c3p0.timeout">10</property>
<property name="hibernate.c3p0.idle_test_period">5</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.connection.release_mode">on_close</property>

Application is well developed and closes connection corretly. Hence, on_close.

Please tell me why query takes so much time at regular intervals ?
Ranch Hand
Posts: 83
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In c3p0, there are many configuration are available. In which you can set the time requied to complete the Transacation.
Please set transaction time out apporiate. Try it.
Also check that lazy loading of data is appropriate class or not.

If you printing the data, it may take to write. Check print statement is commented or not.

Posts: 3837
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd suggest to try to obtain some performance report from the database itself. Is there some other activity taking place in the database during the stress test?
Do not meddle in the affairs of dragons - for you are crunchy and good with ketchup. Crunchy tiny ad:
Thread Boost feature
    Bookmark Topic Watch Topic
  • New Topic