resultSet.next() : Does it fetch data from buffer OR from database?

Question

I have a code like this below,

try (Connection connection = this.getDataSource().getConnection();
         PreparedStatement statement = connection.prepareStatement(sqlQuery);) {


        try {
            statement.setFetchSize(10000); // Set fetch size
            resultSet = statement.executeQuery();

            while (true) {
                resultSet.setFetchSize(10000);
                boolean more = resultSet.next();
                if (! more) {
                    break;
                }
                // populating an arraylist from the value from resultSet
            }
        }
        catch (Exception e) {
            LOGGER.error("Exception : "+e);
        }
    } catch (SQLException e) {
        LOGGER.error("Exception : "+e);
    }

My understanding is as follows,

The statement fetch size is 10000. when statement.executeQuery() is executed, it returns the ResultSet cursor. It will have 10000 rows in memory. When resultSet.next is called, it gets one row from the memory buffer. (one row per call). When no more rows in memory, the query is fired again and 10000 rows are fetched from database again and stored in buffer. This continues until there is no rows to be fetched from DB

So if my understanding is correct, how many actual DB calls will be there for a total rows of 210000? Is it 21 ? (210000 / 10000)

Also when and who calls the DB (when rows in buffer are all read) to get more rows (10000, in my case) and store in buffer. Also when is the buffer cleared ?

Please correct me if I am wrong in my understanding.

I need to work with millions of data in Oracle Database.

Thanks for any pointers/info

Regards,

SD

Answer 1

Sorry, but your understanding is wrong. There is no such thing as "query is fired again".

The execution of the query is done once. This will take an initial amount of time to process the query, (which you can't do anything about other than optimizing your query,) and then it will begin producing rows on the server, which need to be transferred to the client. While the rows are being transferred, the server will probably be continuing to generate more rows to be transferred, and buffering them on the server. This server-side buffering is totally unrelated to the kind of buffering that we are talking about in this Q&A, and you have very little control over it. (Perhaps by means of server configuration, if at all.) At some point all rows will have been collected on the server, and then the only remaining thing to do will be to transfer the remaining rows from the server to the client.

So, as far as the client can tell, once it has sent the query to the server, there is a certain delay while the server is thinking about it, after which rows are becoming available at a rate which is usually as fast as the wire can carry them. So, the client starts reading these rows with resultSet.next().

Without any buffering, each call to resultSet.next() would send a request from the client to the server, telling it to send the next row, and the server would respond with just that row. That would yield the first row very quickly, but it would be very inefficient in the long run, because it would be causing too many round-trips between the client and the server.

With buffering, the first call to resultSet.next() will request a bunch of rows from the server. This will impose a penalty on the time to receive the first row, because you are going to have to wait for 100 rows to be sent over the wire, but in the long run it will significantly reduce total network overhead, because there will be only one round-trip between the client and the server per bunch-of-rows.

The ideal strategy for resultSet.setFetchSize() is to leave it as it is and not worry too much about it.

But if you are paranoid about performance, then a good strategy would be to begin with a fairly small fetch size, (say 10,) so as to get your first row quickly, and then keep doubling it until it reaches a certain maximum (say 100,) beyond which there is really no improvement.

Answer 2

The only people who can reply to your question are the authors of the Oracle JDBC driver.

That being said a call to db to read the next chunk of data won't take more then a few ms (or less), the bulk of the time will depend on the transfer rate, and possibly how you get data from the resultset.

I think that once you go above a few hundred record per call you are into diminishing return setting a bigger fetch size.

About clearing the buffer, that's mostly garbage collection domain, once you loose reference to the resultset.

Just make sure your statement is FORWARD ONLY, both for performance reason and memory footprint.

connection.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY );