Mybatis is very much slower than Ibatis or Jdbc
3,687 views
Skip to first unread message
LI KEN
Apr 11, 2012, 10:40:47 PM4/11/12
to mybatis-user
Ｂａｔｃｈ ｉｎｓｅｒｔ 1000000 ｒｏｗｓ ｄａｔａ，ｔｈｅ ｂａｔｃｈ ｓｉｚｅ ｉｓ １０００，Mybatis ｃｏｓｔｓ 283
ｓｅｃｏｎｄｓ，Ｉｂａｔｉｓ ｃｏｓｔｓ 137 ｓｅｃｏｎｄｓ．
ｓｅｃｏｎｄｓ，Ｉｂａｔｉｓ ｃｏｓｔｓ 137 ｓｅｃｏｎｄｓ．
Ｑｕｅｒｙ 1000000 ｒｏｗｓ ｄａｔａ， ｔｈｅ ｆｅｔｃｈ ｓｉｚｅ ｉｓ １０００，Ｍｙｂａｔｉｓ ｃｏｓｔｓ 175 ｓｅｃｏｎ
ｄｓ，Ｉｂａｔｉｓ ｃｏｓｔｓ ８１ ｓｅｃｏｎｄｓ．
Ｗｈｙ Ｍｙｂａｔｉｓ ｓｏ ｓｌｏｗ？ Ｈｏｗ ｃａｎ Ｉ ｏｐｔｉｍｉｚｅ ｍｙｂａｔｉｓ， ｏｒ ｗｈｅｒｅ ｄｏ Ｉ ｃｏｎｆｉｇｕｒ
ｅ ｉｎｃｏｒｒｅｃｔｌｙ？
Bogdan Tanase
Apr 12, 2012, 2:22:26 AM4/12/12
to mybati...@googlegroups.com
I don't think anyone can answer this, unless you can post a selfcontained test case...
LI KEN
Apr 12, 2012, 2:38:35 AM4/12/12
to mybatis-user
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;
}
});
}
...
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
Apr 12, 2012, 2:45:51 AM4/12/12
to mybatis-user
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();
}
});
}
...
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
Apr 12, 2012, 3:34:09 AM4/12/12
to mybati...@googlegroups.com
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 :)
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..)
KEN
Apr 12, 2012, 3:53:37 AM4/12/12
to mybatis-user
Inserting one million records will takes 146 minutes more than Ibatis,
but Ibatis only takes 137 ｓｅｃｏｎｄｓ.
but Ibatis only takes 137 ｓｅｃｏｎｄｓ.
Eduardo Macarron
Apr 12, 2012, 8:01:40 AM4/12/12
to mybati...@googlegroups.com
Sorry. I am a bit lost with the numbers, note you wrote 283 seconds vs
137 seconds...
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
Apr 12, 2012, 11:55:25 PM4/12/12
to mybatis-user
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 ......
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 ......
LI KEN
Apr 12, 2012, 11:49:49 PM4/12/12
to mybatis-user
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
reply the message? So I use another account to reply this message, so
sad ......
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.
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().
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
mybatis code to test it.
reply the message? So I use another account to reply this message, so
sad ......
Eduardo Macarron
Apr 13, 2012, 11:33:39 AM4/13/12
to mybati...@googlegroups.com
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
Apr 15, 2012, 10:48:36 PM4/15/12
to mybatis-user
Hi Eduardo,
I have sent the profiling results of Ibatis test by Email, please help
to upload here.
Thanks!
I have sent the profiling results of Ibatis test by Email, please help
to upload here.
Thanks!
KEN
Apr 16, 2012, 6:44:53 AM4/16/12
to mybatis-user
Hi Eduardo,
From the visualvm snapshot, I find that every upate action will parse
sql and create a statement． Ｗhether could I ｐａｒｓｅ ｓｑｌ ｆｉｒｓｔ ｔｉｍｅ， ｔｈｅ ｆ
ｏｌｌｏｗ ａｃｔｉｏｎ ｉｎ ｔｈｅ ｂａｔｃｈ ｉｎｓｅｒｔ ｏｎｌｙ ａｄｄ ｂａｔｃｈ ａｓ ｊｄｂｃ． Ｉｆ ｗｅ ｃａｎ ｄｏ ｉ
ｔ， ｉｔ ｗｉｌｌ ｓａｖｅ ｍｕｃｈ ｔｉｍｅ ｆｏｒ ｂａｔｃｈ ｉｎｓｅｒｔ．
Eduardo Macarron
Apr 16, 2012, 8:05:08 AM4/16/12
to mybati...@googlegroups.com
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.
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.
KEN
Apr 16, 2012, 9:23:26 PM4/16/12
to mybatis-user
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>
...
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>
...
KEN
Apr 16, 2012, 10:30:34 PM4/16/12
to mybatis-user
The above is mybatis mapper code, I also post the ibatis mapper code
as below:
...
<insert id="insertUserInfo" parameterClass="userInfo">
</insert>
<select id="selectAllUserInfos" resultClass="userInfo"
fetchSize="1000">
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#)
(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
</insert>
<select id="selectAllUserInfos" resultClass="userInfo"
fetchSize="1000">
Eduardo Macarron
Apr 17, 2012, 3:16:10 AM4/17/12
to mybati...@googlegroups.com
> But in my test code, there is not any dynamic tag. Then how to avoid
> it? Please refer to the below code:
> 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
Mar 14, 2013, 5:17:55 AM3/14/13
to mybati...@googlegroups.com
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!
Eduardo Macarron
Mar 17, 2013, 3:48:58 AM3/17/13
to mybati...@googlegroups.com
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!
Eduardo Macarron
Mar 17, 2013, 3:50:07 AM3/17/13
to mybati...@googlegroups.com
Sorry, i meant "Try 3.2.1 to see _if_ the performance got to a good level" :)
2013/3/17 Eduardo Macarron <eduardo....@gmail.com>
Reply all
Reply to author
Forward
0 new messages