16.3 Performance Checklist

I/O can show up as significant in profiling even if I/O is not itself the bottleneck.

It is worthwhile to have separate measurements available for the JDBC subsystems.

Use JDBC wrappers to measure the performance of database calls.

ResultSet.next( ) can spend a significant amount of time fetching rows from the database.

JDBC wrappers are simple, robust, and low-maintenance.

Get the JDBC driver right. Using the wrong driver can destroy JDBC performance.

Use JDBC 3.0 or the latest JDBC version if possible.

Use connection pooling.

Optimize the SQL to apply set-based (multi-row) processing rather than one row at a time. Use temporary tables and conditional expressions for extra efficiency. Avoid expensive expressions like upper( ). Use extra fields, like a COUNT field.

Avoid moving, deleting, and adding rows where possible: use preinserted and null value rows. Avoid joins, use indexes.

Use the EXPLAIN statement to examine the SQL operation.

Don't use SELECT * ..., use SELECT Field1, Field1, ....

Access fields by index, not by name (i.e., resultSet.getString(1) not resultSet.getString("field1")).

Cache row and field data rather than re-query to access the same data. Using a wrapper, you can transparently cache rows and tables.

Consider using an in-memory (replicated) database.

Use parameterized PreparedStatements except where a statement will be executed only a few times and there is no spare time to initialize a PreparedStatement. Reuse the connection associated with the PreparedStatement unless the connection pool supports PreparedStatement pooling (as JDBC 3.0 does).

Create SQL query strings statically, or as efficiently as possible if created dynamically.

Tune batched row access using fetch sizing.

Batch updates with executeBatch( ), explicitly managing the transaction by turning off auto-commit.

Try to closely match Java data types and database data types. Converting data between badly matching types is slow.

Avoid using slow metadata calls, particularly getBestRowIdentifier( ), getColumns( ), getCrossReference( ), getExportedKeys( ), getImportedKeys( ), getPrimaryKeys( ), getTables( ), and getVersionColumns( ).

Use metadata queries to reduce the amount of transfers needed in subsequent database communications.

Consider using stored procedures to move some execution to the database server. Don't use a stored procedure to replace any simple SQL calls. Stored procedures are best used to avoid moving data back and forth across the network.

Take manual control of transactions with explicit begin and commit calls, turning off auto-commit mode, and combining close transactions to minimize the overall transaction costs.

Use the lowest transaction isolation level that won't corrupt the application.

Avoid letting the user control when a transaction terminates.

Use optimistic transactions when reads predominate over writes.

Savepoints probably have high overheads.

Small, lightly used databases can be efficiently located on the same machine as the application server; otherwise, another machine is probably better.