• Post Reply Bookmark Topic Watch Topic
  • New Topic

Performance concern with Select Query  RSS feed

amit attri
Posts: 8
  • Mark post as helpful
  • send pies
  • 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 ?
jagrutkumar dalwadi
Ranch Hand
Posts: 83
Hibernate Java Spring
  • Mark post as helpful
  • send pies
  • 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.

Martin Vajsar
Posts: 3752
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • 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?
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!