Hello,
Setting fetchSize with postgres does make a difference, even in a simple
JDBC select.
If you consider the following code copy-pasted from my test case:
PreparedStatement stmt =
sqlMapper.getCurrentConnection().prepareStatement("SELECT
itemID,sellingprice,margin,popularityrating,name,att1,att2,att3,att4,att5,longtailpos,timest
FROM itemdetails WHERE profileID= 10 ORDER BY longtailpos");
stmt.setFetchSize(1000);
stmt.execute();
ResultSet set = stmt.executeQuery();
int i = 0;
while( set.next())
{
i++;
System.out.println(set.getString(1));
}
It can iterates through approx 1'500'000 rows without any memory issues
(java process stays at approx 20mo while the loop executes). I can also see
that it takes no more than a few seconds before the first row is printed in
the output console, meaning that results are still being fetched from the DB
at the moment the resultset is accessed.
Now if I comment the line "stmt.setFetchSize(1000);" in the above code,
execution stops at "ResultSet set = stmt.executeQuery();" for approx 15
seconds and then an OutOfMemoryException is thrown from JDBC code. Also in
cayenne code, that would be before the resultset is even accessed (making
ResultIterator useless).
A similar problem is exposed here, with a sample stacktrace :
http://www.nabble.com/java.sql.Statement-generates-java.lang.OutOfMemoryError-in-big-tabe-td21568763.html
And the reason is detailed here :
http://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor
Perhaps other drivers that automatically use server side cursors aren't
affected or just ignore this setting but I can say for sure that this
setting is necessary with postGres.
Andrey, you say you keep getting OutOfMemory exceptions no matter which
value you set. Do you get these exceptions while iterating through the
results or during the call to executeQuery?
If you get it while looping through the resultset, it could mean that
somehow the rows objects aren't garbage collected, it can happen with
cayenne version 2.0 due to the dataContext holding strong references to the
dataObjects. I didn't want to write a fix for v3 at first, but I realized
that in v2, I had to manually unregister the dataObjects from the context in
order to keep the memory usage in safe areas.
If I can be of any help, please ask.
Best regards...
Stéphane
-- View this message in context: http://www.nabble.com/Re%3A-Big-selects-on-PostGres-%3A-Configuring-Statement.setFetchSize%28%29-of--a-selectquery-tp23652781p23703577.html Sent from the Cayenne - Dev mailing list archive at Nabble.com.
This archive was generated by hypermail 2.0.0 : Mon May 25 2009 - 05:32:56 EDT