Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

Strange issue with JDBC execute performance

 
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
====================================
 
permaculture is largely about replacing oil with people. And one tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic