What does Statement.setFetchSize(nSize) method really do?
-
hi. 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);but 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'm using "Microsoft SQL Server 2005 JDBC Driver" for this. i there any way to read the results in small chunks like oracle does when the query is executed it show only a few rows and as you scroll down more results are shown? -
hi. 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);but 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'm using "Microsoft SQL Server 2005 JDBC Driver" for this. i there any way to read the results in small chunks like oracle does when the query is executed it show only a few rows and as you scroll down more results are shown?According to the
javadoc: _Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed forResultSetobjects genrated by thisStatement. If the value specified is zero, then the hint is ignored. The default value is zero. @param rows the number of rows to fetch @exception SQLException if a database access error occurs, this method is called on a closed <code>Statement</code> or the conditionrows >= 0I think you have to set the fetch size before executing the query and it does depend on the driver implimentation and the database if the entire query is executed up front or not. I cannot tell you the level of implimentation provided by SQL Server; I'm guessing around nothing.
Panic, Chaos, Destruction. My work here is done.
_