Slow JDBC batch insert
Posted by: Igor Racic
Date: September 30, 2009 03:08AM

Hi,

I am doing JDBC bulk insert tests on dual core CPU, 2GB RAM.
Inserting 200 000 lines takes around 50 seconds on MySQL 5.0.22, used both JDBC drivers 5.0.8 and 5.1.10. (if I remembered correctly 3rd parameter)
I tried to use MyISAM, innodb, memory but without improvement. Also tried changing parameters as adviced in MySQL docs.

When I do insert on Oracle XE 10.2, I get < 10 seconds.
Both are running on *same machine*, same filesystem - so, physical parameters are same for both - except memory settings).

Results are repeated in each test run.

Table is just
create table T ( a numeric(10,0), v varchar(30) /* default 'ABCD' */ ) ENGINE=xyz
(no constraints, no indexes etc)

Test goes as
- setAutocommit(false)
- prepareStatement
- loop in
set Bind values,
addBatch
executeBatch each 100 lines (tested until 20000)
commit each 1000000 lines
- end loop

All is done using 1 thread (no concurrency)

Is that normal or I am missing something big here ?
Is problem with server version ?

I am looking for ideas for improvements using JDBC approach
(means, currently I won't test load from file etc etc)


Thank you and regards,
Igor

Options: ReplyQuote


Subject
Written By
Posted
Slow JDBC batch insert
September 30, 2009 03:08AM
October 05, 2009 02:31AM
October 05, 2009 02:41AM
October 05, 2009 04:10AM
October 05, 2009 05:38AM
October 06, 2009 03:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.