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.
and so on...
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.
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.