Friday, 29 August 2014

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

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!