No way to set FetchSize to Spring SimpleJdbcCall

Our project uses Oracle Store procedure heavily, and due to its read-more-write-less trait, one SP involves several SQLs which load data from different tables separately for the sake of data retrieving logic located in one place, different SQL statement in SP is for different part of a page. There is Network issue and DB server is far from China, it is time-consuming to load large chunk of data in a SP. I would like to find one way to setFetchSize for Cursor which is returned from SP to JVM. But failed as SimpleJdbcCall ignoring JdbcTemplate fetch size.

Later I downloaded Spring source code to find the reason.

As we use SimpleJdbcCall to execute SP, we know there are 2 ways to instantiate SimpleJdbcCall, one with DataSource, the other with JdbcTemplate. We adopt later to get an instance in order to inherit or propogate its non-default FetchSize. I debug the code in the hope of hunting out where SimpleJdbcCall loses the FetchSize.

Method execute() is the entrance of SimpleJdbcCall to execute SP:

public class SimpleJdbcCall extends AbstractJdbcCall implements SimpleJdbcCallOperations {.....public Map<String, Object> execute(Object... args) {return doExecute(args);}public Map<String, Object> execute(Map<String, ?> args) {return doExecute(args);}public abstract class AbstractJdbcCall {....protected Map<String, Object> doExecute(Object[] args) {checkCompiled();Map<String, ?> params = matchInParameterValuesWithCallParameters(args);return executeCallInternal(params);}protected Map<String, Object> doExecute(Map<String, ?> args) {checkCompiled();Map<String, ?> params = matchInParameterValuesWithCallParameters(args);return executeCallInternal(params);}private Map<String, Object> executeCallInternal(Map<String, ?> params) {CallableStatementCreator csc = getCallableStatementFactory().newCallableStatementCreator(params);if (logger.isDebugEnabled()) {logger.debug("The following parameters are used for call " + getCallString() + " with: " + params);int i = 1;for (SqlParameter p : getCallParameters()) {logger.debug(i++ + ": " +  p.getName() + " SQL Type "+ p.getSqlType() + " Type Name " + p.getTypeName() + " " + p.getClass().getName());}}return getJdbcTemplate().call(csc, getCallParameters());}

Please pay attention to this line:

getJdbcTemplate().call(csc, getCallParameters());

This shows SimpleJdbcCall will use its Constructor parameter JdbcTemplate instance to execute SP, below is the call logic in JdbcTemplate class:

Return clause invokes JdbcTemplate.execute(), and provide CallBack function to handle data, before we dive intto extractOutputParameters(), let us see execute() logic in JdbcTemplate.class:

The statement to invoke SP is contructed in execute method, and then to handle result, the handling logic is provided in call() method. Before to see result handling logic, we go to see how SP calling statement contruction, it is in createCallableStatement() method:

The SP calling statement is constructed with generic JDBC API, very simple, and other auxiliary parameters are using default values. Then go back to execute method, the line : "applyStatementSettings(cs);" will inherit FetchSize if it is set before.

protected void applyStatementSettings(Statement stmt) throws SQLException {int fetchSize = getFetchSize();if (fetchSize > 0) {stmt.setFetchSize(fetchSize);}int maxRows = getMaxRows();if (maxRows > 0) {stmt.setMaxRows(maxRows);}DataSourceUtils.applyTimeout(stmt, getDataSource(), getQueryTimeout());}

Now it is fine to see what happens with result handling @ extractOutputParameters:

In extractOutputParameters method, the FetchSize of input param cs is 800 from Expressions Window and this accounts for the value inherited from JdbcTemplate instance. 

But its TYPE is a WrappedCallableStatementJDK6, so the constructed ResultSet out is Oracle's native implementation, hence the FetchSize is 10 - default value for it. 

HERE WE LOSES FETCH SIZE!!!!!!

Yes, we can also use Spring's StoredProcedure to execute SP, then set appreciate JdbcTemplate instance with desired FetchSize as StoredProcedure offers API to reset JdbcTemplate. If not set, a brand new instance is inherited from its parent class.

But code logic goes into JdbcTemplate.call() method, then it goes the same trap as SimpleJdbcCall.

------------------------------------------------------------

Attach processResultSet and extractData for reference:

In extractData(), it iterates ResultSet with FetchSize==10 as once fetching size from DB server (10 records cached in Client's memory waiting for processing each time) , after iteration done, all the data to this SQL is taken from Server and wrapped in the returned Map in processResultSet() with the name which is OUT type in SP signature as key. Suppose one SP contains 3 SQLs say A, B, C, if A returns 30 records, B 24, C 45, then the total round trips are (30/10 + ceiling(24/10) + ceiling(45/10)) = (3+3+5) = 11. we can set FetchSize to 50, then only 3 round trips.

-------------------------------------------------------------

Summary:

1. There is no API of FetchSize opened for SimpleJdbcCall directly.
2. We can use either SimpleJdbcCall or StoreProcedure to execute SP which we can use JdbcTemplate to ship with FetchSize, but once this procedure gets executed, a new native ResultSet generated with default Oracle's FetchSize to retrieve data from DB server to Client memory.
3. If need FetchSize, use JdbcTemplate directly.

For our project, use above example, if A's execution time is longer than that of the rest, we extract A out and execute it against JdbcTemplate directly, which we can set FetchSize to tune it. Also there is no concurrency issue, we use multi-thread (JDK 1.5) to reduce sequential execution time. The performance goes up greatly.