Rajneesh's Logbook

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.