wood burning stoves*
The moose likes JDBC and the fly likes Strange issue with JDBC execute performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Strange issue with JDBC execute performance" Watch "Strange issue with JDBC execute performance" New topic
Author

Strange issue with JDBC execute performance

ken zhu
Greenhorn

Joined: Apr 09, 2009
Posts: 20
hi everyboy:

i met a strange issue in my j2ee project, i got connection from tomcat dbcp(connection pool) when i
call ResultSet resultSet = pst.executeQuery(sql) it's became very slow need 100 seconds sometime,
1, but i execute the SQL in toad is very fast and it's very simple in fact
2, but when i open or refresh other servlet which also need invoke database, the previous page will load successful very fast.

I don't know how to explain these strange issues and what's the root cause.

Thanks for your hint.
Kiran Joshi
Ranch Hand

Joined: Sep 04, 2005
Posts: 54
Try to check execution plan of the sql query in the database.
Check this when your servlet is firing the sql query.
There could be some issue with the indexes of the database tables involved in the query.
ken zhu
Greenhorn

Joined: Apr 09, 2009
Posts: 20
Kiran Joshi wrote:Try to check execution plan of the sql query in the database.
Check this when your servlet is firing the sql query.
There could be some issue with the indexes of the database tables involved in the query.


Thanks your reply.
The sql query is very simple just like 'select sysdate from dual' and i executed it in toad or sqlplus, it's very fast.
Just when invoke in servlet it will become slow sometimes, i don't know whether have matter with jdbc driver version or other unknown reason?
Kiran Joshi
Ranch Hand

Joined: Sep 04, 2005
Posts: 54
Ken,

I have witnessed such an issue where the sql query fired by java program was following a different execution path than the one fired through sqlplus or TOAD. And that's why it was taking time too.There was an index which was not being used by query from java prog.
Our DBA had analyzed that by monitoring the execution path of the two cases. He suggested us to use a particular 'hint' in the sql query & it worked.
So the suggestion is, try to monitor the execution plan.
I assume your sql query is querying some db table and not querying 'dual'.
ken zhu
Greenhorn

Joined: Apr 09, 2009
Posts: 20
You are right after tried, thanks a lot.
Our 'DBA'(actually we have no a real one) change DB configuration and set server as dedicated mode.
Then i found i can't use DBCP connection pool and we use normal jdbc, but it need long time to establish a connection.
So, would i ask how your web application apply which connect DB way? What happen to me?

below code is our connect DB now:
====================================
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Strange issue with JDBC execute performance