JDBC Driver for SQL/MX Programmer's Reference

Guidelines for Statement Pooling
Enable statement pooling by setting the DataSource object maxStatements property to
an integer value greater than 0 and, also, by enabling connection pooling. See Connection
Pooling for more information.
●
Enabling statement pooling for your JDBC applications might dramatically improve the
performance.
●
Explicitly close a prepared statement by using the Statement.close method because
PreparedStatement objects that are not in scope are also not reused unless the
application explicitly closes them.
●
To ensure that your application reuses a PreparedStatement, call either of the following:
Statement.close method—called by the application❍
Connection.close method—called by the application. All the
PreparedStatement objects that were in use are ready to be reused when the
connection is reused.
❍
●
Controlling the Performance of ResultSet
Processing
To improve JDBC application performance of result fetches for statements that are expected to
return more than two rows, the application should set the fetch size before executing the statement.
This operation works because the ResultSet getter methods have been modified in the JDBC/MX
driver to optimize database interactions. The JDBC/MX driver uses the fetch-size setting to
determine the size of memory used for reading and buffering data.
The application can control the ResultSet fetch size by using the setFetchSize() method of
the Statement class, PreparedStatement class, and ResultSet class.
Considerations:
Applications that use SQL/MX tables, rather than SQL/MP tables, have improved
performance only for result fetches that have greater than two rows returned. The default
JDBC/MX fetch size is set to 1.
●
Once the application sets the fetch size to a value greater than 2 for a statement, the
application should not reset the value back to 2 or less. If the application does so, the
application will experience a slight degradation in performance as compared to using the
default value.
●
Setting the fetch size greater than 2 for statements that return fewer than two rows causes a
slight performance degradation, as compared to using the default fetch size.
●
Setting the fetch size to a value greater than the number of rows returned by a statement
causes the JDBC/MX driver to use more memory, but does not affect the API's functionality.
●