MyBatis set fetch size on ResultSet as out parameter of procedure

Question

I have stored procedure that I need to call using MyBatis. Anyway I managed to call this stored procedure. Procedure has multiple out parameters. One of out parameter is oracle cursor. I need to iterate over Oracle Cursor, but when I do this without any fine-tuning of jdbc driver using fetchSize attribute, it goes row by row and this solution is very slow. I am able to set on procedure call fethcSize attribute:

<select id="getEvents" statementType="CALLABLE" parameterMap="eventInputMap" fetchSize="1000">
    {call myProc(?, ?, ?, ?, ?)}
</select>

But this doesn't helps at all. I think that this doesn't work because of multiple out parameters - so program doesn't know where this fetch size should be applied - on which out parameter. Is there any way to set fetch size on ResultSet(Oracle cursor)? Like when I use CallableStatemen from java.sql package I am able to set on ResultSet fetch size.

Here are mapping files and procedure call from program:

  <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
    PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
          "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="mypackage.EventDao">

    <resultMap id="eventResult" type="Event">
        <result property="id" column="event_id" />
        <result property="name" column="event_name" />
    </resultMap>

    <parameterMap id="eventInputMap" type="map" >
        <parameter property="pnNetworkId" jdbcType="NUMERIC" javaType="java.lang.Integer" mode="IN"/>
        <parameter property="pvUserIdentityId" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
        <parameter property="result" resultMap="eventResult" jdbcType="CURSOR" javaType="java.sql.ResultSet" mode="OUT" />
        <parameter property="success" jdbcType="INTEGER" javaType="java.lang.Integer" mode="OUT"/>
        <parameter property="message" jdbcType="VARCHAR" javaType="java.lang.String" mode="OUT"/>
    </parameterMap>



    <select id="getEvents" statementType="CALLABLE" parameterMap="eventInputMap" fetchSize="1000">
        {call myProc(?, ?, ?, ?, ?)}
    </select>
   </mapper>    

And call from program:

SqlSession session = sqlSessionFactory.openSession();
    Map<String, Object> eventInputMap = new HashMap<String, Object>();
        try {       
            EventDao ed = session.getMapper(EventDao.class);            
            eventInputMap.put("pnNetworkId", networkId);
            eventInputMap.put("pvUserIdentityId", identityId);          
            eventInputMap.put("success", 0);
            eventInputMap.put("message", null);         
            ed.getEvents(eventInputMap);
            session.selectList("EventDao.getEvents", eventInputMap);    
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            session.close();
        }

Thanks in advance!

Answer 1

The provided code works. I have even checked 3 ways to write it: like here with parameterMap, without parameterMap (mapping directly in the statement), and through annotations, everything works.

I used to thing the fetchSize setting was not propagated from main statement to OUT param resultSet, until I really tested that, lately. To apprehend whether the fetch size is used or not and how much effect it has, the result must contain a large enough number of rows. And of course, the poorest is the latency from app to DB, the more noticeable is the effect. For my test, the cursor used by procedure returned 5400 rows of 120 columns (but the most important is the row count). To give an order of magnitude, I have measured fetching times, i.e from the stored procedure return to the statement return, with a result list filled with data fetched from cursor. Then I log the instanciation of the first object mapped, this occurs near the beginning of the global fetching, probably after the first fetch:

public static boolean firstInstance = true;

public Item() {
    if (firstInstance) {
        LOGGER.debug("Item first instance");
        firstInstance=false;
    }
}

And I log again just at the end, after the session.selectList returns.

This is for test purpose only. Do not let that is your code. Find a clean way to do it.

Here are some timing depending on the configured fetch size:

- fetchSize=1    => 13000 ms
- fetchSize=10   =>  5300 ms
- fetchSize=100  =>  3800 ms
- fetchSize=300  =>  3700 ms
- fetchSize=500  =>  3650 ms
- fetchSize=1000 =>  3600 ms

Oracle JDBC driver default fetchSize is 10.

Testing with fetchSize=1 allows proving the supplied setting is used.

With 100, here, 30% are saved. Beyond, the gain is negligible (with this use case, and environment)

Anyway, it would be interesting to be able to know when procedure execution finishes and when result fetch starts. Unfortunately, Mybatis logs very few. I thought custom result handler could help, but looking at the source code of class org.apache.ibatis.executor.resultset.DefaultResultSetHandler, I notice that unlike method handleResultSet (used for simple select statements) that allows using a custom result handler, method handleRefCursorOutputParameter (used here for procedure OUT cursor) does not. Then no need to trying passing a custom result handler: it will be ignored. I am interested in a solution if anyone has one. But it seems an evolution request will be required.