File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Performance Evaluation of JDBC-Oracle 9i applications Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance Evaluation of JDBC-Oracle 9i applications" Watch "Performance Evaluation of JDBC-Oracle 9i applications" New topic
Author

Performance Evaluation of JDBC-Oracle 9i applications

Srinivasa Kadiyala
Ranch Hand

Joined: Jun 10, 2004
Posts: 237
We developed JDBC / Oracle 9i application and loading of a querry takes a lot of time.

I have noticed that some ResultSet, Statement and Connection objects are not closed and I closed them.

In the struts-config: I increased the coonection pool from 20 to 30. Added maxIdle=5 and maxWait=30000 to the dbcp parameters.

Found lot of String objects are used for SQL querry statements and planning to convert them to StringBuffer.

Any further ideas? Any document that describes the guide lines for performance improvement?

Thanks


Srini
Srinivasa Kadiyala
Ranch Hand

Joined: Jun 10, 2004
Posts: 237
Message to Bartenders... Is this question to be moved to Performance area?

Thanks....
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30915
    
158

Puthriah,
Since the question is about performance specific to database, we should leave it here (in JDBC.) If it was only about things like String vs StringBuffer, it would have gone in performance.

When a database is involved, it is nearly always the bottleneck. And when it isn't, network time to the database is the next bottleneck.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30915
    
158

Now on to your real question:

Since this is a web application, you are likely to be doing a lot of similar queries. If you haven't already done so, switch your Statements to PreparedStatements. The database only has to compile them once, which saves a lot of time.

Another big thing is to check the execution plan in the database. This will make sure that indexes are being used. Also, check you aren't doing a "select *" and returning unneeded columns.

For all of these, you need to do some analysis and see which queries are taking the longest so you can focus on those. You can also post that query here for suggestions.
Srinivasa Kadiyala
Ranch Hand

Joined: Jun 10, 2004
Posts: 237
Thanks Jeanne. I will investigate in datail and get back to the forum....
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance Evaluation of JDBC-Oracle 9i applications