Controlling JDBC Driver setting "fetchSize" in a SelectQuery
------------------------------------------------------------
Key: CAY-1225
URL: https://issues.apache.org/jira/browse/CAY-1225
Project: Cayenne
Issue Type: Improvement
Components: Cayenne Core Library
Affects Versions: 2.0 branch
Reporter: Stephane Claret
Priority: Critical
ResultIterator is a nice API for dealing with large SELECT queries,
unfortunately some JDBC drivers that don't automatically use server-side
cursors (eg PostgreSQL JDBC driver) wont'give access to the ResultSet (at JDBC
level) until the whole query has finished executing and all rows have been
returned.
It basically mean that if you execute a SelectQuery returning 1'500'000 rows,
there's a high chance of getting a OutOfMemoryException in the JDBC driver code
(Statement.Excecute), before Cayenne even gets a chance to call the
ResultSet.Next().
It makes the ResultIterator API much less efficient than it could be because :
-Even if rows are processed one by one, they need to be stored in local memory,
causing heap size issues.
-In some scenarios, user code could start working with the resulting rows
immediately as they become available, while the select query is still being
executed by the database engine.
To solve this problem, there should be a way to have the SelectQuery object
calling Statement.setFetchSize() before it executes.
A SelectQuery.setFetchSize( int ) method would be perfect because it's often
needed to configure this setting on a per-query basis.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
