Mybatis is very much slower than Ibatis or Jdbc

[LI KEN]

Ｂａｔｃｈ　ｉｎｓｅｒｔ　1000000　ｒｏｗｓ　ｄａｔａ，ｔｈｅ　ｂａｔｃｈ　ｓｉｚｅ　ｉｓ　１０００，Mybatis　ｃｏｓｔｓ　283
　ｓｅｃｏｎｄｓ，Ｉｂａｔｉｓ　ｃｏｓｔｓ　137　ｓｅｃｏｎｄｓ．

Ｑｕｅｒｙ　1000000　ｒｏｗｓ　ｄａｔａ，　ｔｈｅ　ｆｅｔｃｈ　ｓｉｚｅ　ｉｓ　１０００，Ｍｙｂａｔｉｓ　ｃｏｓｔｓ　175　ｓｅｃｏｎ
ｄｓ，Ｉｂａｔｉｓ　ｃｏｓｔｓ　８１　ｓｅｃｏｎｄｓ．

Ｗｈｙ　Ｍｙｂａｔｉｓ　ｓｏ　ｓｌｏｗ？　Ｈｏｗ　ｃａｎ　Ｉ　ｏｐｔｉｍｉｚｅ　ｍｙｂａｔｉｓ，　ｏｒ　ｗｈｅｒｅ　ｄｏ　Ｉ　ｃｏｎｆｉｇｕｒ
ｅ　ｉｎｃｏｒｒｅｃｔｌｙ？

[Bogdan Tanase]

I don't think anyone can answer this, unless you can post a selfcontained test case...

On Thu, Apr 12, 2012 at 5:40 AM, LI KEN <[hidden email]> wrote:

Ｂａｔｃｈ　ｉｎｓｅｒｔ　1000000　ｒｏｗｓ　ｄａｔａ，ｔｈｅ　ｂａｔｃｈ　ｓｉｚｅ　ｉｓ　１０００，Mybatis　ｃｏｓｔｓ　283
　ｓｅｃｏｎｄｓ，Ｉｂａｔｉｓ　ｃｏｓｔｓ　137　ｓｅｃｏｎｄｓ．

Ｑｕｅｒｙ　1000000　ｒｏｗｓ　ｄａｔａ，　ｔｈｅ　ｆｅｔｃｈ　ｓｉｚｅ　ｉｓ　１０００，Ｍｙｂａｔｉｓ　ｃｏｓｔｓ　175　ｓｅｃｏｎ
ｄｓ，Ｉｂａｔｉｓ　ｃｏｓｔｓ　８１　ｓｅｃｏｎｄｓ．

Ｗｈｙ　Ｍｙｂａｔｉｓ　ｓｏ　ｓｌｏｗ？　Ｈｏｗ　ｃａｎ　Ｉ　ｏｐｔｉｍｉｚｅ　ｍｙｂａｔｉｓ，　ｏｒ　ｗｈｅｒｅ　ｄｏ　Ｉ　ｃｏｎｆｉｇｕｒ
ｅ　ｉｎｃｏｒｒｅｃｔｌｙ？

[LI KEN]

Sorry, I forgot to attach the code.

Mybatis code for batch insert:

...
    public final void batchInsert(final String statementId, final List
items,
            int batchSize) {
        SqlSession sqlSession = getSession();

        int size = items.size();
        for (int i = 0; i < size;) {
            sqlSession.update(statementId, items.get(i));
            i++;

            if (i % batchSize == 0 || i == size) {
                List<BatchResult> batchResults =
sqlSession.flushStatements();
                batchResults.clear();
                sqlSession.clearCache();
            }
        }
    }
...

Ibatis code for batch insert:

...
    public void batchInsert(final String id, final List parameterList,
            final int batchSize){
        this.getSqlMapClientTemplate().execute(new
SqlMapClientCallback() {
            public Object doInSqlMapClient(SqlMapExecutor executor)
                    throws SQLException {
                executor.startBatch();

                int size = parameterList.size();
                for (int i = 0; i < size; i++) {
                    executor.insert(id, parameterList.get(i));
                    if (i % batchSize == 0 || i == size - 1) {
                        executor.executeBatch();
                        executor.startBatch();
                    }
                }

                return null;
            }
        });
    }
...

[LI KEN]

Both Batch size and Fetch size are 1000 rows.

Mybatis code for fetch:

...
    public void selectAll() throws SQLException {
        getSqlSession().select(
 
"notes.dao.mybatis.dao.UserInfoMapper.selectAllUserInfos",
                new ResultHandler() {
                    public void handleResult(ResultContext context) {
                        UserInfo userInfo = (UserInfo) context
                                .getResultObject();
                        userInfo.getId();
                    }
                });
    }
...

Ibatis code for fetch:

...
    public void selectAll() {
 
getSqlMapClientTemplate().queryWithRowHandler("selectAllUserInfos",
                new RowHandler() {
                    public void handleRow(Object arg0) {
                        ((UserInfo) arg0).getId();
                    }
                });
    }
...

[Eduardo Macarron]

I would not be too worried if inserting one million records takes 2
minutes more but I do agree that it is better that it takes 2 minute
less if possible :)

Do you have any profiling data? (VisualVM for example..)

[LI KEN]

Inserting one million records will takes 146 minutes more than Ibatis,
but Ibatis only takes 137　ｓｅｃｏｎｄｓ.

[Eduardo Macarron]

Sorry. I am a bit lost with the numbers, note you wrote 283 seconds vs
137 seconds...

But ok, if we are taking about 146 minutes I would bet the problem is
not in MyBatis.

First of all, I see you are using Spring.

The way MyBatis-Spring works for batches is not the same than for the
Spring official support for iBATIS 2 as you have already noticed.

There is not any "doInSqlMapClient", staments are automatically
batched if you are using a batch SqlSession, and will be flushed
automatically when Spring ends the transaction.

The important point here is that: if you are not starting correctly
the Spring transaction you are not doing any batch!

So, if you want to benchmark MyBatis vs iBATIS I would consider doing
it in a simpler scenario, thus without Spring.

Or if you want to use Spring for any reason ensure that TX is
correctly configured. You will see easily if it is working fine
enabling the log.

Let us know your progress and do not hesitate to ask for help.

[LI KEN-2]

I have profiled the test by VisualVM. It is unable to share the data
here. From profiling data, I find out  the 88.7% time cost by
org.apache.ibatis.session.defaults.DefaultSqlSession.update(String
statement, Object parameter), it is mybatis core code.

In the test case, I use spring to manage the transaction, I monitored
the entire process in Oracle database, and also trace out the log.
There is only one transaction, and mybatis send the data to database
by every batch 1000 rows, please
notice the code:sqlSession.flushStatements().

But to mak sure spring do not affect the test, I will use the pure
mybatis code to test it.

The most important problem is, why my google account can not view or
reply the message? So I use another account to reply this message, so
sad ......

[LI KEN-2]

I have profiled the test, but it is unable to upload the data here.

In the test case, I use spring to
manage the transaction, I monitored the entire process in Oracle
database, and also trace out the log. There is only one transaction,
and mybatis send the data to database by every batch 1000 rows, please
notice the code:sqlSession.flushStatements().

From the profiling data, the 88.7% time cost by
org.apache.ibatis.session.defaults.DefaultSqlSession.update(String
statement, Object parameter), it is mybatis core code.

But to mak sure spring do not affect the test, I will use the pure
mybatis code to test it.

The most important problem is, my google account cannot view or reply
this message, so I reply with another account, so sad ......

[Eduardo Macarron]

Hi Li, this is the visualvm snapshot you sent me.

It looks that most of the time is spent in parsing the SQL sentence.
We should have a look at that. Maybe we could do a fast parsing if
dynamic expresions like ${} are not detected.

I will re-open the issue you filled.

Thank you for the detailed info.

[LI KEN-2]

Hi Eduardo,

I have sent the profiling results of Ibatis test by Email, please help
to upload here.

Thanks!

[LI KEN-2]

Hi Eduardo,

From the visualvm snapshot, I find that every upate action will parse
sql and create a statement． Ｗhether could I　ｐａｒｓｅ　ｓｑｌ　ｆｉｒｓｔ　ｔｉｍｅ，　ｔｈｅ　ｆ
ｏｌｌｏｗ　ａｃｔｉｏｎ　ｉｎ　ｔｈｅ　ｂａｔｃｈ　ｉｎｓｅｒｔ　ｏｎｌｙ　ａｄｄ　ｂａｔｃｈ　ａｓ　ｊｄｂｃ．　Ｉｆ　ｗｅ　ｃａｎ　ｄｏ　ｉ
ｔ，　ｉｔ　ｗｉｌｌ　ｓａｖｅ　ｍｕｃｈ　ｔｉｍｅ　ｆｏｒ　ｂａｔｃｈ　ｉｎｓｅｒｔ．

[Eduardo Macarron]

Yes. The problem is that the sql is parsed each time it is executed.
Maybe we could add a detection to avoid the dynamic parsing in case
there is not any specific dynamic tag like "< " in the SQL.

That should MB3 work at least as fast as IB2.

El día 16 de abril de 2012 12:44, KEN <[hidden email]> escribió:
> Hi Eduardo,
>
> From the visualvm snapshot, I find that every upate action will parse
> sql and create a statement． Ｗhether could I　ｐａｒｓｅ　ｓｑｌ　ｆｉｒｓｔ　ｔｉｍｅ，　ｔｈｅ　ｆ
> ｏｌｌｏｗ　ａｃｔｉｏｎ　ｉｎ　ｔｈｅ　ｂａｔｃｈ　ｉｎｓｅｒｔ　ｏｎｌｙ　ａｄｄ　ｂａｔｃｈ　ａｓ　ｊｄｂｃ．　Ｉｆ　ｗｅ　ｃａｎ　ｄｏ　ｉ
> ｔ，　ｉｔ　ｗｉｌｌ　ｓａｖｅ　ｍｕｃｈ　ｔｉｍｅ　ｆｏｒ　ｂａｔｃｈ　ｉｎｓｅｒｔ．

[LI KEN-2]

But in my test code, there is not any dynamic tag. Then how to avoid
it? Please refer to the below code:

...
        <insert id="insertUserInfo" parameterType="UserInfo"
                statementType="PREPARED">
                insert into userinfo
       
(id,id2,name1,name2,name3,name4,name5,name6,name7,name8,name9,name10,name11,name12,name13,name14,name15,name16,name17,name18,name19,name20,unit1,unit2,unit3,unit4,unit5,unit6,unit7,unit8,unit9,unit10,unit11,unit12,unit13,unit14,unit15,unit16,unit17,unit18,unit19,unit20)
                values
       
(#{id},#{id2},#{name1},#{name2},#{name3},#{name4},#{name5},#{name6},#{name7},#{name8},#{name9},#{name10},#{name11},#{name12},#{name13},#{name14},#{name15},#{name16},#{name17},#{name18},#{name19},#{name20},#{unit1},#{unit2},#{unit3},#{unit4},#{unit5},#{unit6},#{unit7},#{unit8},#{unit9},#{unit10},#{unit11},#{unit12},#{unit13},#{unit14},#{unit15},#{unit16},#{unit17},#{unit18},#{unit19},#{unit20})
        </insert>
        <select id="selectAllUserInfos" resultType="UserInfo"
fetchSize="1000"
                useCache="false" flushCache="false" statementType="STATEMENT"> <!
[CDATA[
                select
id,id2,name1,name2,name3,name4,name5,name6,name7,name8,name9,name10,name11,name12,name13,name14,name15,name16,name17,name18,name19,name20,unit1,unit2,unit3,unit4,unit5,unit6,unit7,unit8,unit9,unit10,unit11,unit12,unit13,unit14,unit15,unit16,unit17,unit18,unit19,unit20
                from userinfo ]]>
        </select>
...

[LI KEN-2]

The above is mybatis mapper code, I also post the ibatis mapper code
as below:

...
        <insert id="insertUserInfo" parameterClass="userInfo">
                insert into userinfo
       
(id,id2,name1,name2,name3,name4,name5,name6,name7,name8,name9,name10,name11,name12,name13,name14,name15,name16,name17,name18,name19,name20,unit1,unit2,unit3,unit4,unit5,unit6,unit7,unit8,unit9,unit10,unit11,unit12,unit13,unit14,unit15,unit16,unit17,unit18,unit19,unit20)
                values
       
(#id#,#id2#,#name1#,#name2#,#name3#,#name4#,#name5#,#name6#,#name7#,#name8#,#name9#,#name10#,#name11#,#name12#,#name13#,#name14#,#name15#,#name16#,#name17#,#name18#,#name19#,#name20#,#unit1#,#unit2#,#unit3#,#unit4#,#unit5#,#unit6#,#unit7#,#unit8#,#unit9#,#unit10#,#unit11#,#unit12#,#unit13#,#unit14#,#unit15#,#unit16#,#unit17#,#unit18#,#unit19#,#unit20#)
        </insert>

        <select id="selectAllUserInfos" resultClass="userInfo"
                fetchSize="1000">
                <![CDATA[
                select
id,id2,name1,name2,name3,name4,name5,name6,name7,name8,name9,name10,name11,name12,name13,name14,name15,name16,name17,name18,name19,name20,unit1,unit2,unit3,unit4,unit5,unit6,unit7,unit8,unit9,unit10,unit11,unit12,unit13,unit14,unit15,unit16,unit17,unit18,unit19,unit20
from userinfo
                ]]>
        </select>
...

[Eduardo Macarron]

> But in my test code, there is not any dynamic tag. Then how to avoid
> it? Please refer to the below code:

I am afraid you can't. This is how MB is working right now so we need
to change that behaviour first.

[Mukarram]

Hello Eduardo,

I am also experiencing the slight degradation of performance of MB3 as compared to IB2 as Ken mentioned and the optimization you suggested seems like a good idea. If everyone agrees on it, can you give some pointers on where to start to make those changes so that we can take a shot at proposing a patch for it (ofcourse if it is not already worked on in one of the logged issues).

Thanks in advance!

On Tuesday, 17 April 2012 12:46:10 UTC+5:30, Eduardo wrote:

> But in my test code, there is not any dynamic tag. Then how to avoid
> it? Please refer to the below code:

I am afraid you can't. This is how MB is working right now so we need
to change that behaviour first.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Eduardo Macarron]

Hi Mukarram

3.2 has two performance improvements:

- new raw lang that skips any dynamic parsing (this is what Ken pointed)

- mapper method caching

Try 3.2.1 to see the performance got to a good level. You may configure the default language to raw or set it individually in the statements you want.

And sorry for the late reply!

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Eduardo Macarron]

Sorry, i meant "Try 3.2.1 to see _if_ the performance got to a good level" :) 

2013/3/17 Eduardo Macarron <[hidden email]>

Hi Mukarram

3.2 has two performance improvements:

- new raw lang that skips any dynamic parsing (this is what Ken pointed)

- mapper method caching

Try 3.2.1 to see the performance got to a good level. You may configure the default language to raw or set it individually in the statements you want.

And sorry for the late reply!

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.