Handling big data on Mybatis

Sometimes, we need to query big data from database. In that case, OutOfMemory error or frequent full GC can happen.

Suppose that there are several million records and we need to query all data to process some logic.

Example sql map

Example dao

	public List selectBigData1() {
		return this.sqlSession.selectList("test.selectBigData1");
	}

When this method is called, OutOfMemory Exception can happen. The following heap dump shows that ArrayList is causing the exception. (This screen is from IBM Heap Analyzer)

Solution 1 – Using ResultHandler

Mybatis supports ResultHandler for handling big ResultSet. When query result is processed, ResultHandler’s callback is called for each record instead of making ArrayList.

Sample ResultHandler

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import java.util.Map;

public class BigDataResultHandler implements ResultHandler<Map>{

	private int count = 0;

	@Override
	public void handleResult(ResultContext<? extends Map> resultContext) {
		if(resultContext.getResultObject() != null) {
			count++;
		}
	}

	public int getTotalCount() {
		return this.count;
	}
}

Sample dao

	public void setResultHandler(BigDataResultHandler resultHandler) {
		this.resultHandler = resultHandler;
	}

	public void selectBigData2() {
		this.sqlSession.select("test.selectBigData1", this.resultHandler);
	}

Notice that this dao return type is void.

Solution 2 – Tuning fetch size

Sometimes, ResultHandler might not be sufficient to avoid memory issue. For some database, program with ResultHandler could cause OutOfMemory error.

Above heap dump shows that OutOfMemory is occured at JDBC driver’s PreparedStatement. (Default fetch size of Oracle JDBC driver is “10”. Above error is made by changing default value) In this case, tuning fetch size of ResultSet solves memory issue. ResultSet’s fetch size can be set at JDBC driver property or Mybatis mapper.

Above mapper has “fetchSize” attribute, which is used as a hint to JDBC driver

Conclusion

When querying data, memory is allocated at 1) JDBC driver and 2) Mybatis result handler.

JDBC driver’s memory usage is dependent on each database’s default fetch size and need to be changed.

Mybatis result handler’s memory can be tuned by implementing ResultHandler.

Advertisement