iBatis performance FYI

[Brent Ryan <br...@blackboard.com>]

FYI,
 
I recently created a program that loads millions of line from a text
file into a large database schema.  I'm using a custom app server called
siwiz (downloads available here http://kuenzli.org/siwiz) and the latest
version of iBatis.  I was able to do about 890 inserts/updates per
second using iBatis which I thought was pretty impressive.  This was
using a dual processor Linux server running Oracle 9i.
 
Later,
Brent



This e-mail is intended only for the personal and confidential use of the recipient(s) named above. 
It may include Blackboard confidential and proprietary information, and is not for redistribution.

Re: iBatis performance FYI

[Christopher Lamey <cl...@localmatters.com>]

On Fri, 2006-04-28 at 10:07 -0600, Larry Meadors wrote:
> That is not to bad, for reference, I did a jdbc import of 180,674
> records in 186.620s, which is only 1032/second.
> 
> Any tips learned along the way that you'd like to share?

Hello all,

I would be very interested in hearing anyone's experience here.  I've
got to cram millions of records into a postgres db as fast as possible.
I don't have test machines setup yet, but on my laptop 3G P4 I'm seeing
encouraging numbers.  Roughly around 3.7ms per input file line, each of
which breaks down into somewhere around 10 to 20 inserts and an update.

Ideally I'd use the psql COPY command, which looks to bypasses the SQL
parser and so is extremely fast (like Oracle's sqlldr or sybase/sql
server's bc), but I need to do programmatic things with the data.

I've dropped as many constraints (keys and indexes) as I can and tuned
postgres for inserts.  The postgres tuning involved increasing the right
values so that lots of inserts don't cause lots of sync and I/O
operations.

iBATIS-wise, I'm batching INSERT statements using a runtime parameter
for the batch size.  A batch size of 100 seems to be working well for
me, but this is dependent on the database and machine configuration so
I'll tune it on more realistic hardware.

Other than batches, are there iBATIS features at which I should be
looking for INSERT performance?

Cheers,
topher

Re: iBatis performance FYI

[Larry Meadors <lm...@apache.org>]

That is not to bad, for reference, I did a jdbc import of 180,674
records in 186.620s, which is only 1032/second.

Any tips learned along the way that you'd like to share?

Larry