What does Statement.setFetchSize(nSize) method really do in SQL Server JDBC driver?

Admin 1
Viewed 121573

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?

jdbc sql-server java
8 Answers

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.

user_212340

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

Ron_615720
Related