Setting the Number of Rows to Prefetch When Executing a SQL Query
When a SQL query is executed, the number of rows of data that a driver
physically copies from the database to the client is called the
fetch size. If you are performance-tuning a particular query,
you might be able to improve performance by adjusting the fetch
size to better match the use of the query.
The fetch size can be set on a statement, in which case, all
result sets created from that statement will use that fetch size. The
fetch size can also be set on a result set at any time. In this case,
the next time data needs to be fetched from the database, the driver
will copy over as many rows as is specified by the current fetch size.
try {
// Get the fetch size of a statement
Statement stmt = connection.createStatement ();
int fetchSize = stmt.getFetchSize();
// Set the fetch size on the statement
stmt.setFetchSize(100);
// Create a result set
ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
// Change the fetch size on the result set
resultSet.setFetchSize(100);
} catch (SQLException e) {
}
Post a comment