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.
