SpringOne08: Persistence Optimizations

DBA-Developer relationship, try to improve it... ok... too bad DBAs are not there to here that ;)

Operational considerations:
- Data volumes, row sizes
- Growth estimates, update frequencies
- Availability requirements
- Testing/QA requirements

Guidelines:
- appropriate data types
- small rows
- split large tables with one-to-one table containing fewer used fields
- improve indexes
- normalize data
- partition data (if available)

Most important: application tuning:
- keep transactions short
- bulk processes => off-hours
- understand locking (on writes, on reads...escalation of row locks to table locks in case of many reads...)

ORM tuning:
- watch Criteria, no NULLS in the WHERE clause, they are not indexed
- like with % at the beginning: no index either
- pick an appropriate TransactionManager (only use XA when accessing JMS and database together)
- use READONLY, try to avoid SERIALIZABLE
- use lazy-loading
- use caching

JDBC Tuning:
- set the fetch size on connections (defaults to 10 for Oracle driver...) lessens the network roundtrips

Hibernate Tuning:
- use "select" fetch mode for relationships needed sometimes - results in 1 or 2 queries.
- use "join" fetch mode for relationships needed all the time (try to limit to one per object)
- use "batch-size" to prefetch a number of proxies and/or collections

Bulk operations:
- do it in the database to avoid network (SQL statement, stored procedure, native data load tools...)
- if you need to do it from the application: JdbcTemplate (batchUpdate), SimpleJdbcInsert (executeBatch), Spring Batch (BatchSqlUpdateItemWriter), Hibernate (set hibernate.jdbc.batch_size and flush and clear session after each batch)

SQL Tuning:
- Capture SQL
- Run EXPLAIN
- adjust: analyse, tweak optimizer, add index
- repeat until adequate performance
- ASK for help from your DBA ;)

OpenSource JDBC monitors:
- p6spy
- Elvyx
- JDBCSpy

Database specific tools:
- MySQL (slow query log with --log-slow-queries or --log-queries-not-using-indexes)
- Oracle: Enterprise Manager, Statspack
- Oracle XA: Monitor Top SQL

Interesting tips for Oracle:
- Use the call to a function setting the module name in Oracle in the validation query for the connection
- Use explain and autotrace in Oracle SQL Developer
- Use ANALYSE TABLE [table] COMPUTE STATISTICS
- Use ANALYSE INDEX [index] COMPUTE STATISTICS

Database Tuning:
- Memory (Global Area SGA, Session Area PGA)
- Disk (Block size, File placement)

Talks about a ProxyDatasource during Q&A. This seems to allow to transmit the credentials of the interactive user. Could be interesting for refactoring of legacy Oracle Forms applications.