How to set fetchSize for iBatis select statement

Question

I'm using iBatis as ORM framework in Java. I have a select statement

<select id="getList" resultMap="correctMap">
    SELECT *
    FROM SOME_TABLE
</select>

And I'm using queryForList method:

List<MappedObject> list = getSqlMapClientTemplate().queryForList("getList");

But it retrieves a big amount of data and performance of this query is pretty slow.

My assumption about this issues that iBatis has default fetch size (e.g. like in JDBS is 10) so that is why it so slow. So I want to set bigger fetch size (1000 for example). How I can do so?

Or am I looking in a wrong way?

NOTE: I need all data so set max results in queryForList method is not a appropriate solution for me.

List queryForList(String id,
                  Object parameterObject,
                  int skip,
                  int max) 

Solution 1

<select id="SELECT_TABLE" parameterType="String" fetchSize="500" resultType="hashmap">
    SELECT * FROM TABLE WHERE NAME = #{value}
</select>

Solution 2

Yes you can set fetchSize at a higher level and need not worry about doing for every select.

Step 1

Create a file mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="lazyLoadingEnabled" value="false"/>
        <setting name="defaultFetchSize" value="5000"/>
    </settings>
</configuration>

You can add any value supported in mybatis http://www.mybatis.org/mybatis-3/configuration.html

Step 2

Load this as a resource in your Config file. This is Spring 4 example

@Value("classpath:mybatis-config.xml")
private Resource myBatisResource ;

Step 3 : Pass to you SqlSessionFactoryBean

sessionFactory.setConfigLocation(myBatisResource);

Note: I did this with myBatis 3.3.0. It does not work with myBatis 3.4.4(there is open defect)

This will ensure that all select statements have a fetchSize property assigned to them.

Comments

[nkukhar]

Is it possible to set fetch size to global config not in each query config?

[RokL]

No way that I know of. You could implement Plugin interceptor (MyBatis 3 user manual page 17) which might be able to set this property on all select queries passing through it. This might not exist for your version.

[Niroshan Abayakoon]

Can I set the fetchSize value dynamically ?

[Pierre]

for the record (my situation), retrieving 109k records was taking 1m40s. Setting fetchSize=75 reduced it to 15s, fetchsize=500 reduced it to 4 seconds, fetchSize=1000 reduced it to 2.8 seconds and fetchSize=2000 reduced the retrieval of the 109,000 rows to 2.3 seconds. That's on a VM-based oracle server. Your mileage may vary (also depends on the specific SQL) but, for me, the sweet spot was at fetchsize between 500 and 1000.