What does Statement.setFetchSize(nSize) method really do?

Answer 1

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 2

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.

Answer 3

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 4

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 5

The fetchSize parameter is a hint to the JDBC driver as to many rows to fetch in one go from the database. But the driver is free to ignore this and do what it sees fit. Some drivers, like the Oracle one, fetch rows in chunks, so you can read very large result sets without needing lots of memory. Other drivers just read in the whole result set in one go, and I'm guessing that's what your driver is doing.

You can try upgrading your driver to the SQL Server 2008 version (which might be better), or the open-source jTDS driver.