Performance of some inserts

[M. Walter]

Hi,

I'm new to mybatis. I wrote a little Java application which
sequentially inserts 22 records into three tables with 50 fields in
total. All in all 66 records are inserted, in every table 22 records.
Now I wonder why this takes nearly 8 seconds. Every single INSERT
statement takes between 60 and 160 milliseconds. I have no BLOBs or
other very large data. Some Strings, Integer, Long and Date. That's
it.

As described in the documentation I create a new SqlSession instance
before every INSERT and close it afterwards in the finally block. The
SqlSessionFactory is created only once with the singleton pattern.
I'm using H2 database in embedded mode.

Has anybody a hint what I'm doing wrong or why this is so slow? The
thing is if I use plain JDBC the INSERT statements are not faster.
Maybe my expectations were too high?

[François Schiettecatte]

You say that if you 'use plain JDBC the INSERT statements are not faster', if that is the case then the overhead is not MyBatis.

What performance do you get if you run the statements on the command line? (if there is such a thing in H2, I am not familiar with it.)

François


On Feb 13, 2012, at 5:23 PM, M. Walter wrote:

> Hi,
>
> I'm new to mybatis. I wrote a little Java application which
> sequentially inserts 22 records into three tables with 50 fields in
> total. All in all 66 records are inserted, in every table 22 records.
> Now I wonder why this takes nearly 8 seconds. Every single INSERT
> statement takes between 60 and 160 milliseconds. I have no BLOBs or
> other very large data. Some Strings, Integer, Long and Date. That's
> it.
>
> As described in the documentation I create a new SqlSession instance
> before every INSERT and close it afterwards in the finally block. The
> SqlSessionFactory is created only once with the singleton pattern.
> I'm using H2 database in embedded mode.
>
> Has anybody a hint what I'm doing wrong or why this is so slow? The
> thing is if I use plain JDBC the INSERT statements are not faster.
> Maybe my expectations were too high?

[Richard Yee]

Most of the overhead probably comes from making the JDBC connection each time.
See if you can time how long it takes just to do the insert call and not the opening and closing of the connection.

 

-Richard

 

2012/2/13 François Schiettecatte <[hidden email]>

You say that if you 'use plain JDBC the INSERT statements are not faster', if that is the case then the overhead is not MyBatis.

What performance do you get if you run the statements on the command line? (if there is such a thing in H2, I am not familiar with it.)

François

On Feb 13, 2012, at 5:23 PM, M. Walter wrote:

> Hi,
>
> I'm new to mybatis. I wrote a little Java application which
> sequentially inserts 22 records into three tables with 50 fields in
> total. All in all 66 records are inserted, in every table 22 records.
> Now I wonder why this takes nearly 8 seconds. Every single INSERT
> statement takes between 60 and 160 milliseconds. I have no BLOBs or
> other very large data. Some Strings, Integer, Long and Date. That's
> it.
>
> As described in the documentation I create a new SqlSession instance
> before every INSERT and close it afterwards in the finally block. The
> SqlSessionFactory is created only once with the singleton pattern.
> I'm using H2 database in embedded mode.
>
> Has anybody a hint what I'm doing wrong or why this is so slow? The
> thing is if I use plain JDBC the INSERT statements are not faster.
> Maybe my expectations were too high?

[Chris]

+1

It is very bad form to create a new database connection with each insert :)  There is a TON of stuff going on on both the client and server when you do that.  I would see how long it takes when you reuse the connection for all inserts.  For production, start investigating how to incorporate a connection pool into your solution.

Chris

---

From: Richard Yee <[hidden email]>
To: [hidden email]
Sent: Monday, February 13, 2012 5:09 PM
Subject: Re: Performance of some inserts

Most of the overhead probably comes from making the JDBC connection each time.
See if you can time how long it takes just to do the insert call and not the opening and closing of the connection.

 

-Richard

 

2012/2/13 François Schiettecatte <[hidden email]>

You say that if you 'use plain JDBC the INSERT statements are not faster', if that is the case then the overhead is not MyBatis.

What performance do you get if you run the statements on the command line? (if there is such a thing in H2, I am not familiar with it.)

François

On Feb 13, 2012, at 5:23 PM, M. Walter wrote:

> Hi,
>
> I'm new to mybatis. I wrote a little Java application which
> sequentially inserts 22 records into three tables with 50 fields in
> total. All in all 66 records are inserted, in every table 22 records.
> Now I wonder why this takes nearly 8 seconds. Every single INSERT
> statement takes between 60 and 160 milliseconds. I have no BLOBs or
> other very large data. Some Strings, Integer, Long and Date. That's
> it.
>
> As described in the documentation I create a new SqlSession instance
> before every INSERT and close it afterwards in the finally block. The
> SqlSessionFactory is created only once with the singleton pattern.
> I'm using H2 database in embedded mode.
>
> Has anybody a hint what I'm doing wrong or why this is so slow? The
> thing is if I use plain JDBC the INSERT statements are not faster.
> Maybe my expectations were too high?

[Eduardo Macarron]

"As described in the documentation I create a new SqlSession instance
before every INSERT.."

The doc says that you should create an SqlSession for each request,
not for each statement. See "sope and lifecycle"
http://www.mybatis.org/core/getting-started.html

So maybe you can reuse the session for some inserts. You may also want
to turn the log on to see where is the time being consumed.

On 13 feb, 23:23, "M. Walter" <[hidden email]> wrote:

> Hi,
>
> I'm new to mybatis. I wrote a little Java application which
> sequentially inserts 22 records into three tables with 50 fields in
> total. All in all 66 records are inserted, in every table 22 records.
> Now I wonder why this takes nearly 8 seconds. Every single INSERT
> statement takes between 60 and 160 milliseconds. I have no BLOBs or
> other very large data. Some Strings, Integer, Long and Date. That's
> it.
>
> As described in the documentation I create a new SqlSession instance
> before every INSERT and close it afterwards in the finally block. The
> SqlSessionFactory is created only once with the singleton pattern.
> I'm using H2 database in embedded mode.
>
> Has anybody a hint what I'm doing wrong or why this is so slow? The
> thing is if I use plain JDBC the INSERT statements are not faster.
> Maybe my expectations were too high?

[François Schiettecatte]

Actually I create a new sql session instance with every statement and it does not add to the overhead from what I can tell.

Similarly sql session instance does not automatically create a new database connection, it acquires a connection from the underlying pool, I think the built-in pool in mybatis will acquire a connection, but c3p0 will usually have spare connections for you if properly configured.

And with mysql the cost of getting a new database connection is very small, cant speak for other databases.

I think your problem lies further down the pipe than mybatis. I would start with running the statements against the bare metal, with mysql it would be the 'mysql', not sure what it would be with H2, and work your way up the pipe.

François


On Feb 14, 2012, at 2:13 AM, Eduardo wrote:

> "As described in the documentation I create a new SqlSession instance
> before every INSERT.."
>
> The doc says that you should create an SqlSession for each request,
> not for each statement. See "sope and lifecycle"
> http://www.mybatis.org/core/getting-started.html
>
> So maybe you can reuse the session for some inserts. You may also want
> to turn the log on to see where is the time being consumed.
>
> On 13 feb, 23:23, "M. Walter" <[hidden email]> wrote:
>> Hi,
>>
>> I'm new to mybatis. I wrote a little Java application which
>> sequentially inserts 22 records into three tables with 50 fields in
>> total. All in all 66 records are inserted, in every table 22 records.
>> Now I wonder why this takes nearly 8 seconds. Every single INSERT
>> statement takes between 60 and 160 milliseconds. I have no BLOBs or
>> other very large data. Some Strings, Integer, Long and Date. That's
>> it.
>>
>> As described in the documentation I create a new SqlSession instance
>> before every INSERT and close it afterwards in the finally block. The
>> SqlSessionFactory is created only once with the singleton pattern.
>> I'm using H2 database in embedded mode.
>>
>> Has anybody a hint what I'm doing wrong or why this is so slow? The
>> thing is if I use plain JDBC the INSERT statements are not faster.
>> Maybe my expectations were too high?

[M. Walter]

Well okay that you guys for your input. I will do some more logging
and time measurement and keep you informed...
A friend of mine told me to use collection inserts if possible. Maybe
this would speed up things as well...

[M. Walter]

Looking at my Mybatis config file again I stumbled upon the dataSource
type. I used "UNPOOLED" here because I have a very simple Java
application (I actually read the documentation you see :-). I tried
"POOLED" and ...whoa! Now it only takes a single second to create my
tables and insert the rows! So POOLED seems to be 7 times faster.
Well, maybe there are more performance gains by implementing your
hints but this will do for the moment. Thank you very much!