Friday, May 9, 2008

JDBC Performance Tuning

When a JDBC call is slow, you can do a performance tuning on both the Java code side and the database server side. The following is a list of tips.

Java side performance tuning:
  1. Time your database access to have an idea whether the Java code or the database is the culprit.
  2. Make sure you are using the right JDBC driver. There are four types of JDBC drivers. Some types, like JDBC-ODBC bridge, are doomed to be slow.
  3. Be sure to use database connection pool unless your application is entirely single threaded.
  4. Be sure to close database resources, such as Connection, Statement, ResultSet even in situations when Exception's are thrown.
  5. Use PreparedStatement correctly.
Database side performance tuning:
  1. Determine whether indexes are necessary to create.
  2. Update database statistics.
  3. Run Explain Plan or similar tools on expensive queries to avoid full table scan.

These tips sound like vanilla. However they are proven effective ways to kill 80% of your JDBC performance issues.