Performance Considerations in JDBC

Pooling (Object Re-use)

Connection Pooling – Enabling Connection pooling allows the pool manager to keep connections in a ‘pool’ after they are closed. The next time a connection is needed, if the connection options requested match one in the pool then that connection is returned instead of incurring the overhead of establishing another actual socket connection to the server.

Statement Pooling – Setting the MaxPooledStatements connection option enables statement pooling. Enabling statement pooling allows the driver to re-use PreparedStatement objects. When PreparedStatements are closed they are returned to the pool instead of being freed and the next PreparedStatement with the same SQL statement is retrieved from the pool rather than being instantiated and prepared against the server.

MetaData Performance

• Specify as many ar guments to DatabaseMetaData methods as possible. This avoids unnecessary scans on the database. For example, don’t call getTables like this:

ResultSet rs = dbmd.getTables(null,null,null,null);

Specifying at least the schema will avoid returning information on all tables for every schema when the request is sent to the server:

ResultSet rs = dbmd.getTables(null,”test”,null,null);

• Most JDBC drivers populate the ResultSetMetaData object at fetch time because the needed data is returned in the server responses to the fetch request. Some underutilized pieces of ResultSetMetaData include:

ResultSetMetaData.getColumnCount()

ResultSetMetaData.getColumnName()

ResultSetMetaData.getColumnTypeNa

ResultSetMetaData.getColumnType()

ResultSetMetaData.getPrecision() ResultSetMetaData.getColumnDisplaySize

ResultSetMetaData.getScale()

Commit mode

When writing a JDBC application, make sure you consider how often you are committing transactions. Every commit causes the driver to send packet requests over the socket. Additionally, the database performs the actual commit which usually entails disk I/O on the server. Consider removing autocommit mode for your application and using manual commit instead to better control commit logic:

Connection.setAutoCommit(false);

Network Traffic Reduction

• Use addBatch() instead of using PreparedStatements to insert.
• Eliminate unused column data from your SQL statements
• Ensure that your database is set to the maximum packet size and that the driver matches that packet size