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.