Friday, October 28, 2005

JDBC Performance Pointers

Here are a few of the JDBC Performance pointers. Hopefully, there are a lot more, which you may be aware of, but not mentioned here. Care to share them as well?

  • Get database connection from connection pool rather than getting it directly
    Use batch transactions.
  • Choose right isolation level as per your requirement.
  • TRANSACTION_READ_UNCOMMITED gives best performance for concurrent transaction based applications. TRANSACTION_NONE gives best performance for non-concurrent transaction based applications.
  • Your database server may not support all isolation levels, be aware of your database server features.
  • Use PreparedStatement when you execute the same statement more than once.
  • Use CallableStatement when you want result from multiple and complex statements for a single request.
  • Use batch update facility available in Statements.
  • Use batch retrieval facility available in Statements or ResultSet.
  • Set up proper direction for processing rows.
  • Use proper getXXX() methods.
  • Close ResultSet, Statement and Connection whenever you finish your work with them.
  • Write precise SQL queries.
  • Cache read-only and read-mostly tables data.
  • Fetch small amount of data iteratively rather than whole data at once when retrieving large amount of data like searching database etc.
  • Use Type one driver if you don't have a driver for your database. This is a rare situation because all major databases support drivers or you will get a driver from third party vendors.
  • Use Type two driver for two tiered applications to communicate from java client to database that gives better performance than Type1 driver.
  • Use Type three driver to communicate between client and proxy server ( weblogic, websphere etc) for three tiered applications that gives better performance when compared to Type 1 &2 drivers.
  • Use Type four driver for applet to database communication that is two tiered applications and three tiered applications when compared to other drivers.
  • Pass database specific properties like defaultPrefetch if your database supports any of them.

4 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Carfield Yim said...

Hi, would you talk more about "Set up proper direction for processing rows."... not sure what that mean... Do you mean rs.next() is not go to next record but previous record?

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

i Cudn't imagine ppl cracking such PJ's .Cancerian means his Sun Sign is cancer.And about rs.next() ,it definately moves to the next row ,I guess what the author is talking here is scrollable result sets which also has previous() and also seek() I guess

A

Popular Posts (Last 30 days)

This is a personal blog and I do not speak for my employer.