Why MyBatis SELECT is slower than JDBC

Question

For an easiest query.

My JDBC code is like this:

String sql = "SELECT videos.id, videos.connector_id, videos.season_number FROM videos WHERE videos.connector_type = 'show';";
System.out.println("Create statement...");
Scanner scanner = new Scanner(System.in);
while (true) {
    int input = scanner.nextInt();
    long start = System.nanoTime();
    stmt = conn.prepareStatement(sql);
    ResultSet resultSet = stmt.executeQuery();
    System.out.println("Time 1: " + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - start));
}

And MyBatis code is like this:

<select id="queryTest" resultType="map">
SELECT videos.id, videos.connector_id, videos.season_number FROM videos WHERE connector_type = 'show';
</select>

try (SqlSession session = DatabaseConnFactory.getSqlSession()) {
    start = System.nanoTime();
    maps = session.selectList("queryTest", parameterWrapper);
    System.out.println("Time 1: " + TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - start));
}

Not considering the first query, for the following queries, JDBC always takes about 6ms~10ms, and MyBatis takes about 31~35ms, which is about 3 times.

For a more complex query (6 inner joins and an order by in it), JDBC only takes 25~30ms, while MyBatis needs 80~100ms.

I've tried to avoid using mapper, instead using session.selectList, session.select and session.selectCursor, but none of them has obvious better performance than MaBatis.Mapper.

How can I improve the performance of MyBatis?

Answer 1

Define a fetch size to your query as mentioned in below code:

<select id="queryTest" fetchSize="1000" resultType="map">

Default fetch size in jdbc for oracle is 10 and in sql developer it is 50 which makes it really slow if you have 1000s of records. So in that case you need to define a suitable fetch size as per your requirement.