New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Batch Insert & Select Performance on Mybatis vs Jdbc (comes from Issue 580 on google) #98

Closed
KenTell opened this Issue Nov 13, 2013 · 15 comments

Comments

Projects
None yet
4 participants
@KenTell

KenTell commented Nov 13, 2013

Sorry for so late to test the performance.

I have test the Batch Insert & Select Performance with lang="raw" on mybatis 3.2.3. The below is test result:
Mybatis Jdbc
Data quantity 100000 rows 100000 rows
batch size/fetch size 1000 rows 1000 rows
Insert cost 2841 ms 1843 ms
Select cost 2345 ms 1328 ms

Compare above with the test result shown on Issue 580 on google, I find the performance is improved much. Even so, the performance is still bad.

@KenTell

This comment has been minimized.

Show comment
Hide comment

KenTell commented Nov 13, 2013

@emacarron

This comment has been minimized.

Show comment
Hide comment
@emacarron

emacarron Nov 13, 2013

Member

Hi Ken,

Thanks for testing! Do you have the values for iBATIS 2 for that same test?

Member

emacarron commented Nov 13, 2013

Hi Ken,

Thanks for testing! Do you have the values for iBATIS 2 for that same test?

@KenTell

This comment has been minimized.

Show comment
Hide comment
@KenTell

KenTell Nov 15, 2013

I have added the ibatis test as below:
batch size & fetch size is 1000.
version                insert cost/ms select cost/ms
jdbc                   1053           808
ibatis2.3.5            1730           2097
mybatis3.2.3           3694           2298
mybatis3.2.3(with raw) 2200           2342

KenTell commented Nov 15, 2013

I have added the ibatis test as below:
batch size & fetch size is 1000.
version                insert cost/ms select cost/ms
jdbc                   1053           808
ibatis2.3.5            1730           2097
mybatis3.2.3           3694           2298
mybatis3.2.3(with raw) 2200           2342
@KenTell

This comment has been minimized.

Show comment
Hide comment
@KenTell

KenTell Nov 15, 2013

Another case, data rows is 1000000, batch size & fetch size is 1000:
version                insert cost/ms select cost/ms
jdbc                   8721           6603
ibatis2.3.5            11268          15153
mybatis3.2.3           23790          
mybatis3.2.3(with raw) 11796          14061

KenTell commented Nov 15, 2013

Another case, data rows is 1000000, batch size & fetch size is 1000:
version                insert cost/ms select cost/ms
jdbc                   8721           6603
ibatis2.3.5            11268          15153
mybatis3.2.3           23790          
mybatis3.2.3(with raw) 11796          14061
@KenTell

This comment has been minimized.

Show comment
Hide comment
@KenTell

KenTell Nov 15, 2013

The performance is good now, thanks.

KenTell commented Nov 15, 2013

The performance is good now, thanks.

@emacarron

This comment has been minimized.

Show comment
Hide comment
@emacarron

emacarron Nov 15, 2013

Member

When having a look into this is for the first time (mybatis 3.1) I saw it was that mybatis was recalculating the SQL (even when it is not dynamic) and that was taking a very long time.

The custom dynamic language of iBATIS seems to be faster than the XML + Ognl solution of mybatis.

That is why we added the "raw" language that bypasses any SQL dynamic generation. I do not like too much to have to do "something" for it to work fast. Ideally that "raw" language should be calculated automatically. That is, if the language parser finds that the statement its not dynamic, it can pre-compile it what means that the statement will be parsed during startup and not during execution. This is something we can improve indeed.

Seeing your numbers it looks like mybatis is running more or less alike ibatis. (sligthliy slower in the insert and slightly faster in the select) but definitely in the same range. Not too bad in my opinion :)

Member

emacarron commented Nov 15, 2013

When having a look into this is for the first time (mybatis 3.1) I saw it was that mybatis was recalculating the SQL (even when it is not dynamic) and that was taking a very long time.

The custom dynamic language of iBATIS seems to be faster than the XML + Ognl solution of mybatis.

That is why we added the "raw" language that bypasses any SQL dynamic generation. I do not like too much to have to do "something" for it to work fast. Ideally that "raw" language should be calculated automatically. That is, if the language parser finds that the statement its not dynamic, it can pre-compile it what means that the statement will be parsed during startup and not during execution. This is something we can improve indeed.

Seeing your numbers it looks like mybatis is running more or less alike ibatis. (sligthliy slower in the insert and slightly faster in the select) but definitely in the same range. Not too bad in my opinion :)

@emacarron

This comment has been minimized.

Show comment
Hide comment
@emacarron

emacarron Nov 15, 2013

Member

Seems we posted at the same time. Good you know you are happy with the results!!

And thank you very much for your help. You discovered a very important performance issue.

Member

emacarron commented Nov 15, 2013

Seems we posted at the same time. Good you know you are happy with the results!!

And thank you very much for your help. You discovered a very important performance issue.

@emacarron emacarron closed this Nov 15, 2013

@emacarron emacarron reopened this Nov 17, 2013

@emacarron emacarron closed this in 5f9a168 Nov 17, 2013

@emacarron

This comment has been minimized.

Show comment
Hide comment
@emacarron

emacarron Nov 17, 2013

Member

Hi again. With this change, "raw" is automatically applied if the statement has not any dynamic content. So there is no longer need to specify "raw" to make an statement run fast.

The downside is that the attribute parameterType was formerly ignored and now is not, so this can produce errors for those who have wrong settings.

Before this change the parameterType attribute in XMLs was ignored and the TypeHandler to use was got out of the actual parameter. That means that you could set the paremeterType to Integer, but pass a Long and it worked despite the configuration was indeed wrong.

Now, static statements are pre-parsed and the TypeHandler is calculated during startup. That means that if you set a parameterType it cannot be wrong.

The change has not turned tha parameterType to mandatory. It is still optional and can be omitted. If it is, MyBatis will calculate it during the execution as before.

A new snapshot is available in https://github.com/mybatis/mybatis-3/releases

Member

emacarron commented Nov 17, 2013

Hi again. With this change, "raw" is automatically applied if the statement has not any dynamic content. So there is no longer need to specify "raw" to make an statement run fast.

The downside is that the attribute parameterType was formerly ignored and now is not, so this can produce errors for those who have wrong settings.

Before this change the parameterType attribute in XMLs was ignored and the TypeHandler to use was got out of the actual parameter. That means that you could set the paremeterType to Integer, but pass a Long and it worked despite the configuration was indeed wrong.

Now, static statements are pre-parsed and the TypeHandler is calculated during startup. That means that if you set a parameterType it cannot be wrong.

The change has not turned tha parameterType to mandatory. It is still optional and can be omitted. If it is, MyBatis will calculate it during the execution as before.

A new snapshot is available in https://github.com/mybatis/mybatis-3/releases

@saurabhnm

This comment has been minimized.

Show comment
Hide comment
@saurabhnm

saurabhnm Feb 5, 2014

Hey Ken,
I am not able to achieve the performance levels mentioned using mybatis 3.2.3 for batch insert.
It would be great if you share the code/configurations used for test

saurabhnm commented Feb 5, 2014

Hey Ken,
I am not able to achieve the performance levels mentioned using mybatis 3.2.3 for batch insert.
It would be great if you share the code/configurations used for test

@emacarron

This comment has been minimized.

Show comment
Hide comment
@emacarron

emacarron Feb 5, 2014

Member

First of all, try 3.2.4 that needs no extra config.

Member

emacarron commented Feb 5, 2014

First of all, try 3.2.4 that needs no extra config.

@saurabhnm

This comment has been minimized.

Show comment
Hide comment
@saurabhnm

saurabhnm Feb 6, 2014

I tried 3.2.4 with lang=raw. But still not able to achieve the claimed performance levels
For 500 records its takes 12 secs
but for 1000 records it takes 200 secs when session.commit() is executed.
Just For your reference I have set my transaction manager as JDBC.

saurabhnm commented Feb 6, 2014

I tried 3.2.4 with lang=raw. But still not able to achieve the claimed performance levels
For 500 records its takes 12 secs
but for 1000 records it takes 200 secs when session.commit() is executed.
Just For your reference I have set my transaction manager as JDBC.

@emacarron

This comment has been minimized.

Show comment
Hide comment
@emacarron

emacarron Feb 6, 2014

Member

3.2.4 does not require you to set "raw" it will apply it as long there is
not any dynamic content in the SQL.

Note that commit flushes batches, so make sure you do not call commit on
each statement.

2014-02-06 saurabhnm notifications@github.com:

I tried 3.2.4 with lang=raw. But still not able to achieve the claimed
performance levels
For 500 records its takes 12 secs
but for 1000 records it takes 200 secs when session.commit() is executed.
Just For your reference I have set my transaction manager as JDBC.

Reply to this email directly or view it on GitHubhttps://github.com/mybatis/mybatis-3/issues/98#issuecomment-34298195
.

Member

emacarron commented Feb 6, 2014

3.2.4 does not require you to set "raw" it will apply it as long there is
not any dynamic content in the SQL.

Note that commit flushes batches, so make sure you do not call commit on
each statement.

2014-02-06 saurabhnm notifications@github.com:

I tried 3.2.4 with lang=raw. But still not able to achieve the claimed
performance levels
For 500 records its takes 12 secs
but for 1000 records it takes 200 secs when session.commit() is executed.
Just For your reference I have set my transaction manager as JDBC.

Reply to this email directly or view it on GitHubhttps://github.com/mybatis/mybatis-3/issues/98#issuecomment-34298195
.

@saurabhnm

This comment has been minimized.

Show comment
Hide comment
@saurabhnm

saurabhnm Feb 6, 2014

Thanks a lot emacarron for your quick replies. I found the problem.
Performance hit was happening because of usage of nested loop for pushing insert records into batch set statements.
For example :
Performance hampering order
insert into table 1
insert into table 2
insert into table 2
insert into table 3
insert into table 3
insert into table 2
insert into table 2
insert into table 1

Performance enhancing order should be
insert into table 1
insert into table 1
insert into table 2
insert into table 2
insert into table 2
insert into table 2
insert into table 3
insert into table 3

saurabhnm commented Feb 6, 2014

Thanks a lot emacarron for your quick replies. I found the problem.
Performance hit was happening because of usage of nested loop for pushing insert records into batch set statements.
For example :
Performance hampering order
insert into table 1
insert into table 2
insert into table 2
insert into table 3
insert into table 3
insert into table 2
insert into table 2
insert into table 1

Performance enhancing order should be
insert into table 1
insert into table 1
insert into table 2
insert into table 2
insert into table 2
insert into table 2
insert into table 3
insert into table 3

@emacarron

This comment has been minimized.

Show comment
Hide comment
@emacarron

emacarron Feb 15, 2014

Member

Hi. Thanks for the feedback.

Everytime the statement changes the peding statements buffer is flushed (batched) so batch should be used by sending groups of the same statement (as you found by yourself).

Member

emacarron commented Feb 15, 2014

Hi. Thanks for the feedback.

Everytime the statement changes the peding statements buffer is flushed (batched) so batch should be used by sending groups of the same statement (as you found by yourself).

qlp added a commit to qlp/mybatis-3 that referenced this issue Jun 18, 2014

Closes #98. Detect and pre-process static statements during startup so
the execution becames much faster (x2). No need to use "raw" anymore
because now "raw" is automatically applied.

qlp added a commit to qlp/mybatis-3 that referenced this issue Jun 18, 2014

qlp added a commit to qlp/mybatis-3 that referenced this issue Jun 18, 2014

@JinJoyce

This comment has been minimized.

Show comment
Hide comment
@JinJoyce

JinJoyce Apr 17, 2017

@emacarron Hi. Could you please help me to see the other similar problem?

  1. I'm using mybatis 3.2.2, get the result about 3mins;
  2. change to JDBC, 0.67s;
  3. execute in database server directly, 0.40s.

mapper file as below:
`

AND SUBS.ECCUSTID = #{eccustId,jdbcType=VARCHAR}

<sql id="whereCondition">
    <if test="msisdn != null and msisdn != ''">
        AND SUBS.MSISDN = #{msisdn,jdbcType=VARCHAR}
    </if>
    <if test="dealEndTime != null and dealEndTime != ''">
        AND SMS.DEALENDTIME <![CDATA[ >= ]]> TO_DATE(#{dealEndTime,jdbcType=VARCHAR},'YYYY-MM')
        AND SMS.DEALENDTIME <![CDATA[ < ]]> ADD_MONTHS(TO_DATE(#{dealEndTime,jdbcType=VARCHAR}, 'YYYY-MM'),1)
    </if>
</sql>

<sql id="sortCondition">
    ORDER BY MSISDN,REMARK,ICCID,DEALENDTIME,SENDER,RECEIVER,SMSTYPE,DEALENDTIME DESC
</sql>

<select id="querySmsHistoryListByPage" resultType="cn.iot.m2m.bisserver.beans.device.smshistory.SmsHistoryVo"
        parameterType="java.util.Map">
    SELECT R2.* FROM (SELECT R1.*, ROWNUM NUM FROM (SELECT HIS.MSISDN,HIS.REMARK,HIS.ICCID,HIS.DEALENDTIME,
    DECODE(HIS.SMSTYPE, '01', HIS.SERVCODE) AS SENDER,
    DECODE(HIS.SMSTYPE, '00', HIS.SERVCODE) AS RECEIVER,
    HIS.SMSTYPE FROM (SELECT SMS.SMSTYPE,SMS.SERVCODE,SMS.DEALENDTIME,SUBS.MSISDN,SUBS.ICCID,
    SUBS.REMARK
    FROM SMSTABLE SMS, SUBSTABLE SUBS WHERE SMS.MSISDN=SUBS.MSISDN
    AND SMS.SMSTYPE NOT IN ('10','11')
    <include refid="whereSql"/>
    <include refid="whereCondition"/>) HIS
    <include refid="sortCondition"/>) R1
    <![CDATA[ WHERE ROWNUM <= #{lastIndex,jdbcType=NUMERIC} ]]> ) R2
    WHERE R2.NUM >= #{startIndex,jdbcType=NUMERIC}
</select>`

thanks in advance!!

JinJoyce commented Apr 17, 2017

@emacarron Hi. Could you please help me to see the other similar problem?

  1. I'm using mybatis 3.2.2, get the result about 3mins;
  2. change to JDBC, 0.67s;
  3. execute in database server directly, 0.40s.

mapper file as below:
`

AND SUBS.ECCUSTID = #{eccustId,jdbcType=VARCHAR}

<sql id="whereCondition">
    <if test="msisdn != null and msisdn != ''">
        AND SUBS.MSISDN = #{msisdn,jdbcType=VARCHAR}
    </if>
    <if test="dealEndTime != null and dealEndTime != ''">
        AND SMS.DEALENDTIME <![CDATA[ >= ]]> TO_DATE(#{dealEndTime,jdbcType=VARCHAR},'YYYY-MM')
        AND SMS.DEALENDTIME <![CDATA[ < ]]> ADD_MONTHS(TO_DATE(#{dealEndTime,jdbcType=VARCHAR}, 'YYYY-MM'),1)
    </if>
</sql>

<sql id="sortCondition">
    ORDER BY MSISDN,REMARK,ICCID,DEALENDTIME,SENDER,RECEIVER,SMSTYPE,DEALENDTIME DESC
</sql>

<select id="querySmsHistoryListByPage" resultType="cn.iot.m2m.bisserver.beans.device.smshistory.SmsHistoryVo"
        parameterType="java.util.Map">
    SELECT R2.* FROM (SELECT R1.*, ROWNUM NUM FROM (SELECT HIS.MSISDN,HIS.REMARK,HIS.ICCID,HIS.DEALENDTIME,
    DECODE(HIS.SMSTYPE, '01', HIS.SERVCODE) AS SENDER,
    DECODE(HIS.SMSTYPE, '00', HIS.SERVCODE) AS RECEIVER,
    HIS.SMSTYPE FROM (SELECT SMS.SMSTYPE,SMS.SERVCODE,SMS.DEALENDTIME,SUBS.MSISDN,SUBS.ICCID,
    SUBS.REMARK
    FROM SMSTABLE SMS, SUBSTABLE SUBS WHERE SMS.MSISDN=SUBS.MSISDN
    AND SMS.SMSTYPE NOT IN ('10','11')
    <include refid="whereSql"/>
    <include refid="whereCondition"/>) HIS
    <include refid="sortCondition"/>) R1
    <![CDATA[ WHERE ROWNUM <= #{lastIndex,jdbcType=NUMERIC} ]]> ) R2
    WHERE R2.NUM >= #{startIndex,jdbcType=NUMERIC}
</select>`

thanks in advance!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment