What does Statement.setFetchSize(nSize) method rea

Question

I have this really big table with some millions of records every day and in the end of every day I am extracting all the records of the previous day. I am doing this like:

String SQL =  "select col1, col2, coln from mytable where timecol = yesterday";
Statement.executeQuery(SQL);

The problem is that this program takes like 2GB of memory because it takes all the results in memory then it processes it.

I tried setting the Statement.setFetchSize(10) but it takes exactly the same memory from OS it does not make any difference. I am using Microsoft SQL Server 2005 JDBC Driver for this.

Is there any way to read the results in small chunks like the Oracle database driver does when the query is executed to show only a few rows and as you scroll down more results are shown?

Answer 1

Sounds like mssql jdbc is buffering the entire resultset for you. You can add a connect string parameter saying selectMode=cursor or responseBuffering=adaptive. If you are on version 2.0+ of the 2005 mssql jdbc driver then response buffering should default to adaptive.

http://msdn.microsoft.com/en-us/library/bb879937.aspx

Answer 2

You need to ensure that auto-commit on the Connection is turned off, or setFetchSize will have no effect.

dbConnection.setAutoCommit(false);

Edit: Remembered that when I used this fix it was Postgres-specific, but hopefully it will still work for SQL Server.

Answer 3

Statement interface Doc

SUMMARY: void setFetchSize(int rows) Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed.

Read this ebook J2EE and beyond By Art Taylor

Answer 4

Try this:

String SQL = "select col1, col2, coln from mytable where timecol = yesterday";

connection.setAutoCommit(false);
PreparedStatement stmt = connection.prepareStatement(SQL, SQLServerResultSet.TYPE_SS_SERVER_CURSOR_FORWARD_ONLY, SQLServerResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(2000);

stmt.set....

stmt.execute();
ResultSet rset = stmt.getResultSet();

while (rset.next()) {
    // ......

Answer 5

In JDBC, the setFetchSize(int) method is very important to performance and memory-management within the JVM as it controls the number of network calls from the JVM to the database and correspondingly the amount of RAM used for ResultSet processing.

Inherently if setFetchSize(10) is being called and the driver is ignoring it, there are probably only two options:

1. Try a different JDBC driver that will honor the fetch-size hint.
2. Look at driver-specific properties on the Connection (URL and/or property map when creating the Connection instance).

The RESULT-SET is the number of rows marshalled on the DB in response to the query. The ROW-SET is the chunk of rows that are fetched out of the RESULT-SET per call from the JVM to the DB. The number of these calls and resulting RAM required for processing is dependent on the fetch-size setting.

So if the RESULT-SET has 100 rows and the fetch-size is 10, there will be 10 network calls to retrieve all of the data, using roughly 10*{row-content-size} RAM at any given time.

The default fetch-size is 10, which is rather small. In the case posted, it would appear the driver is ignoring the fetch-size setting, retrieving all data in one call (large RAM requirement, optimum minimal network calls).

What happens underneath ResultSet.next() is that it doesn't actually fetch one row at a time from the RESULT-SET. It fetches that from the (local) ROW-SET and fetches the next ROW-SET (invisibly) from the server as it becomes exhausted on the local client.

All of this depends on the driver as the setting is just a 'hint' but in practice I have found this is how it works for many drivers and databases (verified in many versions of Oracle, DB2 and MySQL).

Answer 6

It sounds to me that you really want to limit the rows being returned in your query and page through the results. If so, you can do something like:

select * from (select rownum myrow, a.* from TEST1 a )
where myrow between 5 and 10 ;

You just have to determine your boundaries.