ResultSet.next (): Does it retrieve data from the OR buffer from the database?

Question

I have code like 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 this:

The operator fetch size is 10000. When execute.executeQuery () is executed, it returns a ResultSet cursor. It will have 10,000 lines in memory. When resultSet.next is called, it gets one line from the memory buffer. (one line per call). When there are no more rows in the memory, the query is run again, and 10,000 rows are retrieved from the database again and stored in the buffer. This continues until no rows are selected from the database.

So, if my understanding is correct, how many actual database calls will be available for entire rows of 210,000? Is it 21? (210000/10000)

Also, when and who calls the database (when all the lines in the buffer are all read) to get more lines (10000, in my case) and save to the buffer. Also when is the buffer flushed?

Please correct me if I am mistaken in my understanding.

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

Thanks for any / info pointers

Hello,

SD

Answer 1

Sorry, but your understanding is incorrect. There is no such thing as a "request fired again."

The request is executed once. To process the request, you will need an initial period of time (that you can do nothing but optimize your request), and then it will begin to create lines on the server that should be transferred to the client. While the lines are being transmitted, the server is likely to continue to generate more lines to be transmitted and to buffer them on the server. This server-side buffering is completely unrelated to the buffering we are talking about in this Q&A, and you have very little control over it. (Perhaps using the server configuration, if at all possible.) At some point, all the lines will be collected on the server, and then the only remaining task will be to transfer the remaining lines from the server to the client.

So, as far as the client can tell, as soon as he sent the request to the server, there is a certain delay when the server thinks about it, after which the lines become available at a speed that is usually as fast as the wire can carry them. So, the client begins to read these lines using resultSet.next() .

Without any buffering, each call to resultSet.next() sends a request from the client to the server, telling it to send the next line, and the server will respond only with this line. This would give the first line very quickly, but in the long run it would be very inefficient because it would cause too many round trips between the client and server.

When buffering, the first call to resultSet.next() will request a bunch of lines from the server. This will impose a penalty on the time to receive the first line, because you have to wait for 100 lines to be sent over the wire, but in the end it will significantly reduce the overall network costs, because there will be only one round-trip between the client and server for each group lines.

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

But if you're paranoid about performance, then a good strategy is to start with a rather small sample size (say 10) to quickly get the first row, and then double it until it reaches a certain maximum (say 100), for beyond which there is no improvement.

Answer 2

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

At the same time, calling db to read the next piece of data will not take more than a few ms (or less), most of the time will depend on the transmission speed and, possibly, on how you get the data from the result.

I think that as soon as you go to a few hundred records per call, you will reduce the size of the return value by getting a larger sample size.

About flushing the buffer, basically the garbage collection domain, after you lose the link to the result set.

Just make sure your FORWARD ONLY statement is for both performance and memory reasons.

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