JDBC, JPA, DB Performance Tips

JDBC

• Always close or release the resources when you are finished with them.
• Use the latest version versions available of SDK/JRE and JDBC Drivers as they performance improvements over previous release.
• Use type-4 driver when possible, if DB and Java run on the same machine type, type-2 driver could be faster (evaluate before deciding).
• In some cases having multiple connection pools may help where each pool can support a particular connection feature like read-only and a general connection pool.
• Access fields by name has cost use the column number if possible (e.g. resulstSet.getString(0)).
• Use Prepared Statement wherever possible as it prepares the query plan once and reuses the same on subsequent executions while Statement prepares the query plan on every requests.
• Try to use the same Prepared Statement for repeated execution of an SQL as it is subject to driver and DB to ensure that any Prepared Statement that uses a particular SQL also uses the same query plan.
• Creating and executing a Statement once is faster than creating and executing a Prepared Statement.
• Increase the default fetch size if you regularly access more than the default fetch size data.
• Consider the batch update wherever applicable.
• Minimize the data conversion overhead, use the type-correct get() rather than getObject().
• ResultSetMetaData is more efficient than DatabaseMetaData.
• Not correctly handling JDBCException can leave resources in use, but idle.
• A Stored Procedure can improve performance if it reduces:
• A complete series of SQL Statements.
• Multiple calls to the DB.
•  Application side processing when there is spare CPU capacity on the DB server.

JPA

• Use cascade=”none” so that developers will not be able to use the association for any other reason except fetch.
• Wherever possible, navigate from Child to Parent entity.
• Use component tag for Tables having many columns to organize data.
• Use fetch=”join” to have a single query fetch relation (to take care of N+1 problem).
• Wherever possible, use Named Queries as most JPA provides pre-parse/compile and cache names queries.
• Use Criteria query only when dynamically generation of query required.
• Wherever possible, use setReadOnly(true) to gain performance.
• Go for version Optimistic lock instead of Pessimistic.
• For Pessimistic lock set lock timeout to zero or minimum.
• Do not accept default configurations of JPA frameworks like Hibernate, etc without understanding what they are for.

DB

• Use DB Indexes for large Tables.
• Use EXPLAIN SQL command to check query plan.
• Separate Tables of frequently and infrequently used data as it allows DB to optimize its handling of frequently used data.
• For Tables having huge data a Table Partition approach may help (evaluate before use). 
• For postgres table partitioning you can refer to: http://myblogmchopker.blogspot.in/2014/06/postgres-performance-improvement-tips.html

General

• Minimize Data Transfer.
• Fetch only required columns, rows instead of fetching all data and performing filtering in application.
• Cache small read-only Tables and Tables that are updated infrequently.
• You can not use ResultSet objects as cache objects as it uses resources that need to be closed.
• An in-memory DB like HSQLDB could also be looked for database caching.
• Do check cache hits and miss ratios during performance testing.
• Avoid distributed transactions unless they are absolutely required.

Thank You!