wood burning stoves 2.0*
The moose likes Performance and the fly likes Performance concern with Select Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Performance concern with Select Query" Watch "Performance concern with Select Query" New topic
Author

Performance concern with Select Query

amit attri
Greenhorn

Joined: Aug 24, 2011
Posts: 8
Hi,
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-0.9.1.1.jar
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

Joined: Mar 26, 2009
Posts: 83

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
Sheriff

Joined: Aug 22, 2010
Posts: 3606
    
  60

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?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance concern with Select Query
 
Similar Threads
An attempt by a client to checkout a Connection has timed out.
Problem with all-delete-orphan
com.mysql.jdbc.CommunicationsException MESSAGE: Communications link failure due to underlying except
totally stumped by synchronization.afterCommit error
Hibernate, MySQL and the EOFException