When reading large datasets (e.g. inside CreateOrUpdateTask.getRowReader() / JdbcExtractor.getReader()), we are using Cayenne iterator, still the underlying DB may read the entire ResultSet in memory. This causes two problems:
- Excessive memory use (and potential OutOfMemory exceptions)
- Read connection inactivity timeouts (as the read connection sits idle for the duration of the LinkMove job).
A Cayenne side of this should use SQLSelect.statementFetchSize(batchSize). But some DBs may require extra settings at the JDBC level for this flag to take effect. Specifically MySQL requires this:
stmt = conn.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);So the goal of this task is to either figure out a transparent solution for JDBC-level result streaming or develop a set of simple recipes for MySQL, PostgreSQL, SQLServer as most common DB engines on how to solve it (e.g. via URL parameters, etc.) Or provide some combination of the two. (See a MySQL recipe below in comments).
When reading large datasets (e.g. inside
CreateOrUpdateTask.getRowReader()/JdbcExtractor.getReader()), we are using Cayenne iterator, still the underlying DB may read the entire ResultSet in memory. This causes two problems:A Cayenne side of this should use
SQLSelect.statementFetchSize(batchSize). But some DBs may require extra settings at the JDBC level for this flag to take effect. Specifically MySQL requires this:So the goal of this task is to either figure out a transparent solution for JDBC-level result streaming or develop a set of simple recipes for MySQL, PostgreSQL, SQLServer as most common DB engines on how to solve it (e.g. via URL parameters, etc.) Or provide some combination of the two. (See a MySQL recipe below in comments).