Understand the functionality of the Statement.setFetchSize(nSize) method in the SQL Server JDBC driver

Question

I have a very large table with millions of records every day, and at the end of each day I pull all the records from the previous day. This is how I do it:

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

The problem is that this program requires about 2GB of memory because it puts all the results in memory and then processes them.

I tried setting Statement.setFetchSize(10) but it gets the exact same memory from the OS, no difference. I use the 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, showing only a few rows when the query is executed, and more results as you scroll down?

Answer 1

In JDBC, the setFetchSize(int) method is very important for performance and memory management within the JVM because it controls the number of network calls from the JVM to the database and the corresponding amount of data. RAM used for result set processing.

Essentially, if setFetchSize(10) is called and the driver ignores it, there are only two possible options:

1. Try using a different JDBC driver that supports getting size hints.
2. View driver-specific properties on Connection (URL and/or property mapping when creating Connection instance).

RESULT-SET is the number of rows marshalled on the database in response to the query. ROW-SET is a block of rows extracted from RESULT-SET on each call from JVM to DB. The number of these calls, and the RAM required for processing, depends on the fetch-size setting.

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

The default fetch-size is 10, which is quite small. In the case posted, the driver seems to be ignoring the fetch size setting, retrieving all data in one call (requires lots of RAM, optimally minimal network calls).

What happens under ResultSet.next() is that it doesn't actually get one row at a time from the RESULT-SET. It gets that data from the (local) ROW-SET and gets the next ROW-SET (invisible) from the server when the data on the local client is exhausted.

All of this depends on the driver, as the setting is just a "hint", but in practice I have found that this is how many drivers and databases work (proven in many versions of Oracle, DB2 and MySQL).