DEVFYI - Developer Resource - FYI

What does setFetchSize() really do?

JDBC Interview Questions and Answers

(Continued from previous question...)

What does setFetchSize() really do?

The API documentation explains it pretty well, but a number of programmers seem to have a misconception of its functionality. The first thing to note is that it may do nothing at all; it is only a hint, even to a JDBC Compliant driver. setFetchSize() is really a request for a certain sized blocking factor, that is, how much data to send at a time.
Because trips to the server are expensive, sending a larger number of rows can be more efficient. It may be more efficient on the server side as well, depending on the particular SQL statement and the DB engine. That would be true if the data could be read straight off an index and the DB engine paid attention to the fetch size. In that case, the DB engine could return only enough data per request to match the fetch size. Don't count on that behavior. In general, the fetch size will be transparent to your program and only determines how often requests are sent to the server as you traverse the data.
Also, both Statement and ResultSet have setFetchSize methods. If used with a Statement, all ResultSets returned by that Statement will have the same fetch size. The method can be used at any time to change the fetch size for a given ResultSet. To determine the current or default size, use the getFetchSize methods.

(Continued on next question...)

Other Interview Questions