JDBC TUNING

JDBC TUNING

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

•Avoid moving, deleting, and adding rows where possible

•Avoid joins and use indexes

•Don‘t use SELECT *..

•Use SELECT FIELD1, FIELD2, ..

•Access fields of a ResultSet using index and using name

     •rs.getString(1) and not rs.getString(“empno”)

•Cache row and field data

•Rather than re-query across the same data

•Consider using an in-memory (replicated) database

•Prefer parameterized PreparedStatement aginst Statement

•Tune batched row access using fetch sizing

•Use batch updates

•Try to closely match Java datatypes with database datatypes.

•Converting data between matching types is slow

•Avoid using slow metadata method calls:

•getBestRowIdentifier()

•getColumns()

•getCrossReference()

•getExportedKeys()

•getImportedKeys()

•getPrimaryKeys() and

•getTables()

•Take manual control of transactions by:

•explicit begin and commit calls

•Turning off auto-commit mode

•Combining close transactions to minimize the overall   transaction costs

•Use the lowest transaction isolations that won‘t corrupt the application

•Avoid letting the user control when the transaction terminates

•No user interactions between the transaction boundaries

•Savepoints have high overheads

•Consider using stored procedures to move some execution to the database server

•Stored procedures are best used to avoid moving data back and forth across the network

•Don‘t use a stored procedure to replace any simple SQL calls