SpringOne 2008 - Persistence Tuning for the Spring Environments by Tomas Risberg

a few best practices:
  • get your DBA involved early
  • take time to design your db: tables, indexes, data types, normalize, partition,... all have impact on the performance
  • consider batch vs online. (consider native db-tools for loading)
  • limit the number of rows / columns per table -> normalize
  • avoid nulls in where clause (nulls are not indexed!)
  • avoid starting with a wildcard in "like '%xyz'" tests (not indexed)
  • select the proper transaction manager: JTA and XA is the slowest solution --> prefer plain JMS and native JDBC transactions
  • avoid 'SERIALIZABLE' transaction isolation
  • use the readonly-flag (JDBC) whenever possible.
  • use a proper connection pool: Apache DBCP or Oracle Datasource Connection Pool (never use the 'standard' DriverManagerDatasource!)
  • early test your connections at startup: JDBC 4: isValid() or "select 1 from dual;"
  • use prepared statements --> optimized by db
  • setFetchSize()
  • use a RowMapper of BatchJdbcTemplate
  • caching
  • sql tuning: run EXPLAIN and ANALYZE commands
  • tune Oracle memory: SGA (=global + sessions) and disk (blocks/files); put logs on separate volume

No comments: