You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Ralf Assmann <ra...@innovations.de> on 2006/09/01 14:21:48 UTC
Bad Performance Using Ibatis?
Hi there,
making a performance test with ibatis, we got a very slow runtime.
Inserting 100000 rows into a table, we took more than twice the time
than using own-written prepared statements. The time was definitely
lost during the transaction time writing the data to the database,
calling sqlMap.insert(...).
Does anyone know if this runtime is normal while using ibatis or is
there any xml-parameter to optimize it?
Many thanx,
Ralf
Re: Bad Performance Using Ibatis?
Posted by Ralf Assmann <ra...@innovations.de>.
No, I am not batching the insert. This will not work with the
application we want to use ibatis for ... If a make the test with
batching, ibatis is also slower than jdbc-statements (prepared
statements), but not twice the time.
Therefore, autocommitting is false. The commit will be done by
sqlMap.commitTransaction(). This works, because without using this in
the test, no data are in the database table.
Chris Lamey schrieb:
> Are you batching the inserts? If not, then a commit is done after each
> insert, which is very slow.
>
> On Fri, 2006-09-01 at 14:21 +0200, Ralf Assmann wrote:
>
>> Hi there,
>>
>> making a performance test with ibatis, we got a very slow runtime.
>> Inserting 100000 rows into a table, we took more than twice the time
>> than using own-written prepared statements. The time was definitely
>> lost during the transaction time writing the data to the database,
>> calling sqlMap.insert(...).
>>
>> Does anyone know if this runtime is normal while using ibatis or is
>> there any xml-parameter to optimize it?
>>
>> Many thanx,
>>
>>
>> Ralf
>>
>>
Re: Bad Performance Using Ibatis?
Posted by Ralf Assmann <ra...@innovations.de>.
A commit will be done every 1000 inserts. Also, there are not
differences in doing that with ibatis and "plain jdbc-statements". Both
will do the same, but ibatis needs twice the time.
Larry Meadors schrieb:
> ...and if you do batch it, you may still want to commit after ever
> 100-200 inserts to keep the database from creating a massive rollback
> segment.
>
> Also, if you are inserting 100,000 rows, you may want to look into
> using a native data loader tool. It'll be WAAY faster.
>
> Larry
>
>
> On 9/1/06, Chris Lamey <cl...@localmatters.com> wrote:
>> Are you batching the inserts? If not, then a commit is done after each
>> insert, which is very slow.
>>
>> On Fri, 2006-09-01 at 14:21 +0200, Ralf Assmann wrote:
>> > Hi there,
>> >
>> > making a performance test with ibatis, we got a very slow runtime.
>> > Inserting 100000 rows into a table, we took more than twice the time
>> > than using own-written prepared statements. The time was definitely
>> > lost during the transaction time writing the data to the database,
>> > calling sqlMap.insert(...).
>> >
>> > Does anyone know if this runtime is normal while using ibatis or is
>> > there any xml-parameter to optimize it?
>> >
>> > Many thanx,
>> >
>> >
>> > Ralf
>> >
>>
Re: Bad Performance Using Ibatis?
Posted by Larry Meadors <lm...@apache.org>.
...and if you do batch it, you may still want to commit after ever
100-200 inserts to keep the database from creating a massive rollback
segment.
Also, if you are inserting 100,000 rows, you may want to look into
using a native data loader tool. It'll be WAAY faster.
Larry
On 9/1/06, Chris Lamey <cl...@localmatters.com> wrote:
> Are you batching the inserts? If not, then a commit is done after each
> insert, which is very slow.
>
> On Fri, 2006-09-01 at 14:21 +0200, Ralf Assmann wrote:
> > Hi there,
> >
> > making a performance test with ibatis, we got a very slow runtime.
> > Inserting 100000 rows into a table, we took more than twice the time
> > than using own-written prepared statements. The time was definitely
> > lost during the transaction time writing the data to the database,
> > calling sqlMap.insert(...).
> >
> > Does anyone know if this runtime is normal while using ibatis or is
> > there any xml-parameter to optimize it?
> >
> > Many thanx,
> >
> >
> > Ralf
> >
>
Re: Bad Performance Using Ibatis?
Posted by Chris Lamey <cl...@localmatters.com>.
Are you batching the inserts? If not, then a commit is done after each
insert, which is very slow.
On Fri, 2006-09-01 at 14:21 +0200, Ralf Assmann wrote:
> Hi there,
>
> making a performance test with ibatis, we got a very slow runtime.
> Inserting 100000 rows into a table, we took more than twice the time
> than using own-written prepared statements. The time was definitely
> lost during the transaction time writing the data to the database,
> calling sqlMap.insert(...).
>
> Does anyone know if this runtime is normal while using ibatis or is
> there any xml-parameter to optimize it?
>
> Many thanx,
>
>
> Ralf
>
Re: Bad Performance Using Ibatis?
Posted by Ralf Assmann <ra...@innovations.de>.
Thanks for this thread, I have read it before :-)
You are right, doing a performance test, I made a for-loop using jdbc
vs. ibatis. And I used startTransaction etc. The code looks anything
like this:
sqlMap.startTransaction();
for-loop, inserting data using ibatis; doing sqlMap.commitTransaction()
every 1000 inserts
sqlMap.commitTranscation() after the last insert
sqlMap.endTransaction();
Or is it necessary to start a transaction every 1000 inserts?
By the way, using "plain jdbc-statements", I did the same (without
start- and end transaction of course).
Clinton Begin schrieb:
>
> Yay! It's our monthly performance concern!
>
> Rest assured, for 99% of cases, iBATIS is within milliseconds of JDBC
> performance.
>
> This comes up once a month, where someone creates a couple of for{}
> loops with JDBC vs. iBATIS....
>
> 9 times out of 10 the code is the problem -- inappropriate transaction
> handling, is the most common mistake.
>
> In any case, you might want to read this thread.
>
> http://www.mail-archive.com/user-java@ibatis.apache.org/msg04760.html
>
> The key point: "
> When I excluded the
> startTransaction/commitTransaction/endTransaction...my process took 5
> minutes to run. When I included it, I got my full process to run under 1
>
> minute. This is directly equal to the speed I found when using straight
> Jdbc and/or Spring Jdbc Templates.
> "
>
> Batching and reflection optimization are icing on the cake. Even
> without them, iBATIS should be nearly as fast as JDBC ( i.e. not
> noticably slower).
>
> Cheers,
> Clinton
>
> On 9/1/06, *Ralf Assmann* <ralf.assmann@innovations.de
> <ma...@innovations.de>> wrote:
>
>
> Hi there,
>
> making a performance test with ibatis, we got a very slow runtime.
> Inserting 100000 rows into a table, we took more than twice the time
> than using own-written prepared statements. The time was definitely
> lost during the transaction time writing the data to the database,
> calling sqlMap.insert(...).
>
> Does anyone know if this runtime is normal while using ibatis or is
> there any xml-parameter to optimize it?
>
> Many thanx,
>
>
> Ralf
>
>
Re: Bad Performance Using Ibatis?
Posted by Clinton Begin <cl...@gmail.com>.
Yay! It's our monthly performance concern!
Rest assured, for 99% of cases, iBATIS is within milliseconds of JDBC
performance.
This comes up once a month, where someone creates a couple of for{} loops
with JDBC vs. iBATIS....
9 times out of 10 the code is the problem -- inappropriate transaction
handling, is the most common mistake.
In any case, you might want to read this thread.
http://www.mail-archive.com/user-java@ibatis.apache.org/msg04760.html
The key point: "
When I excluded the
startTransaction/commitTransaction/endTransaction...my process took 5
minutes to run. When I included it, I got my full process to run under 1
minute. This is directly equal to the speed I found when using straight
Jdbc and/or Spring Jdbc Templates.
"
Batching and reflection optimization are icing on the cake. Even without
them, iBATIS should be nearly as fast as JDBC (i.e. not noticably slower).
Cheers,
Clinton
On 9/1/06, Ralf Assmann <ra...@innovations.de> wrote:
>
>
> Hi there,
>
> making a performance test with ibatis, we got a very slow runtime.
> Inserting 100000 rows into a table, we took more than twice the time
> than using own-written prepared statements. The time was definitely
> lost during the transaction time writing the data to the database,
> calling sqlMap.insert(...).
>
> Does anyone know if this runtime is normal while using ibatis or is
> there any xml-parameter to optimize it?
>
> Many thanx,
>
>
> Ralf
>
>
RE: Bad Performance Using Ibatis?
Posted by Zsolt <zk...@intland.com>.
You probably need bulk insert.
insert into ... values(...),(...)
That is very fast.
Zsolt
>-----Original Message-----
>From: Ralf Assmann [mailto:ralf.assmann@innovations.de]
>Sent: Friday, September 01, 2006 2:22 PM
>To: user-java@ibatis.apache.org
>Subject: Bad Performance Using Ibatis?
>
>
>Hi there,
>
>making a performance test with ibatis, we got a very slow runtime.
>Inserting 100000 rows into a table, we took more than twice the time
>than using own-written prepared statements. The time was definitely
>lost during the transaction time writing the data to the database,
>calling sqlMap.insert(...).
>
>Does anyone know if this runtime is normal while using ibatis or is
>there any xml-parameter to optimize it?
>
>Many thanx,
>
>
>Ralf