- 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.
- 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.
- 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
- 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.