fetchsize in resultset set to 0 by default

jdbc fetch size oracle
jdbctemplate fetchsize
jdbc fetch size postgresql
simplejdbccall set fetch size
set fetch direction resultset
spring jdbctemplate large result set
db2 jdbc driver fetch size
fetchsize sql

I have to query a database and result set is very big. I am using MySQL as data base. To avoid the "OutOfMemoryError" after a lot of search I got two options: One using LIMIT(specific to database) and other is using jdbc fetchSize attribute.

I have tested the option 1(LIMIT) an it is working but it is not the desired solution. I do not want to do it.

Using jdbc I found out that ResultSet size is set to 0 by default. How can I change this to some other value. I tried the following:

a) First Try:

rs = preparedStatement.executeQuery();
rs.setFetchSize(1000);  //Not possible as exception occurs before.

b) Second T Even if this is not there then also I need to communicate to databse multiple timry:

rs.setFetchSize(1000);  //Null pointer exception(rs is null).
rs = preparedStatement.executeQuery();

c) Third Try:

preparedStatement = dbConnection.createStatement(query);
preparedStatement.setFetchSize(1000);

None of this is working. Any help appreciated!

Edit:

I do not want a solution using limit because: a) I have millions of rows in my result set. Now doing multiple query is slow. My assumption is that database takes multiple queries like

SELECT *  FROM a LIMIT 0, 1000
SELECT *  FROM a LIMIT 1000, 2000

as two different queries.

b) The code is looks messy because you need to have additional counters.

The MySQL JDBC driver always fetches all rows, unless the fetch size is set to Integer.MIN_VALUE.

See the MySQL Connector/J JDBC API Implementation Notes:

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

Result Set Enhancements, Fetch Size. By default, when Oracle JDBC executes a query, it receives the result set 10 rows at a time from the database cursor. This is the default Oracle  Fetch size is also used in a result set. When the statement object executes a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.

Besides all you should change your query like

SELECT * FROM RandomStones LIMIT 1000;

Or

PreparedStatement stmt = connection.prepareStatement(qry);
stmt.setFetchSize(1000);
stmt.executeQuery();

To set the fetch size for a query, call setFetchSize() on the statement object prior to executing the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

Tuning View Object Performance by Adjusting the Row Fetch Size, You can specify a number -1 or greater. The default is -1 meaning fetch all rows. Zero (0) means fetch no rows in the result set. Note that this setting does not effect  Actually the statement "JDBC driver will use the default value which is zero" is incorrect/impossible. The "fetch size" is the number of rows from the result-set that are fetched from the DB by the driver as results set rows are needed/processed on the client side. If the size were zero it would not fetch any rows.

In MySQL connector-j 5.1 implementation notes, they said there are 2 ways to handle this situation.

enter image description here

The 1st is to make the statement retrieve data row by row, the 2nd can support batch retrieve.

JDBC fetch size - commonly forgotten setting, JDBC fetch size parameter can significantly reduce the result set fetching Yet, the default value can result in a poor performance on some jdbc drivers. local and remote Oracle 11g database; jdbc driver used: 11.1.0.7.0  fetchSize can be set on each Statement or PreparedStatement or even on ResultSet. By default, ResultSet uses fetchSize of Statement from which is born. The default value for Statement or PreparedStatement is jdbc driver specific; fetchSize is only a hint for the driver – the Oracle driver respects this setting, while other drivers may ignore

Expert Oracle JDBC Programming, This is because the fetch size set at the CallableStatement level never really worked. Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.00 0.00 0 5 0 at the CallableStatement level it was 20, and at the ResultSet level it was  java.sql.SQLException: Invalid operation for forward only resultset it's due to by default it is ResultSet.TYPE_FORWARD_ONLY, which means you can only use rs.next() the solution is: stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

setFetchSize Method (SQLServerResultSet), setFetchSize Method (SQLServerResultSet) Setting the fetch size to zero restores the default fetch size for the cursor type that is currently in  3 I found that the default fetchsize for the resultset is 10. If I change the fetchSize to 5, and the cursor fetches 8 records, I could get the first 5 records while iterating the resultset. 4 If the cursor fetched (10 or more records) or (5 or less) I could get all the records while iterating the resultset.

Oracle & Postgres JDBC Fetch Size - Franck Pachot, TL;DR — By default PostgreSQL ResultSet is not a server cursor but a and we have a clue with the fetch size that is set to zero by default. My assumption about this issues that iBatis has default fetch size (e.g. like in JDBS is 10) so that is why it so slow. So I want to set bigger fetch size (1000 for example). How I can do so? Or am I looking in a wrong way? NOTE: I need all data so set max results in queryForList method is not a appropriate solution for me.

Comments
  • Can't we retrieve in chunks rather than row by row?
  • @AmanDeepGautam Not in the MySQL JDBC driver, that implementation is either everything, or one by one.
  • it is working. Thats's what I have written in question. But I do not want that