Bad Performance for JDBC Insert

[Pete Neu]

Hello,

I'm programming a tool which for test purposes fills a database with
dummy data. I use a JDBC driver. I aim to insert about 1.000.000 datasets
but right now it's taking too long altough my routine is very small.

for (int i= 0 ; i < numberOfJoins ; i ++)
{
RandomGUID myGUID = new RandomGUID();
statement.setString( 1, myGUID.toString());
statement.setString( 2 , (String)parentkey.get(i));
statement.setString( 3 , (String)objectkeys.get(i) );
statement.execute();
}
What I'm doing is create a GUI and then retrieve to other GUIDS from two
very large LinkedLists.

The Query looks like this
"Insert into " + tableName + " ( PRIMARYKEY, PARENTKEY, OBJECTKEY ) VALUES ( ? , ? , ? ) " ;

Right now it's taking me for 100.000 datasets about 6.5 minutes. For 50.000
it's only 2.2 minutes. Does anyone have any idea how to improve on this?

[Jeroen Wenting]

Sounds more like your database is slow to handle the load than that your application is slow to tell the database what to do...

[Pete Neu]

Is there anything that I can do to take the load of the database?
I'm using MaxDB.

[Sripathi Krishnamurthy]

when you are doing so many inserts, it is advisable to use a Batch command.
try this,

connection.setAutoCommit(false);
for (int i= 0 ; i < numberOfJoins ; i ++)
{
RandomGUID myGUID = new RandomGUID();
statement.setString( 1, myGUID.toString());
statement.setString( 2 , (String)parentkey.get(i));
statement.setString( 3 , (String)objectkeys.get(i) );
 
statement.addbatch(); //the batch statement for PreparedStatement does not take any arguements.
 
if(i!=0 && i%1000==0)//for every 1000 records, insert as a batch
{
statement.executeBatch();
statement.clearBatch();
connection.commit();
}
if(i%1000 != 0 && i==numberOfJoins-1)//for every 1000 records, insert as a batch
{
statement.executeBatch();
statement.clearBatch();
connection.commit();
}
}  

[ March 22, 2005: Message edited by: Sripathi K S ]

[Ilja Preuss]

Moving to JDBC...

[Pete Neu]

I tried it. The Batch job saves me about 1.5 minutes. The rate for
100.000 inserts is now 4.5 minutes. Can I do anything else?

[Adeel Ansari]

Originally posted by Pete Neu:
I tried it. The Batch job saves me about 1.5 minutes. The rate for
100.000 inserts is now 4.5 minutes. Can I do anything else?

Disable the indexes before batch insert, or any DML in batch, after execution enable it.

[Pete Neu]

if I get you right I should do something like this:

 alter index t_idx unusable; 

The point is I don't have any indexes in the table.
Or do you mean some different index?

[Shailesh Chandra]

Originally posted by Pete Neu:

The point is I don't have any indexes in the table.

Pete,

very first I would suggest that you test how much time your database is taking while inserting 100000 record without any java code.

create a temporary table and insert 100000 rows into table something like

insert into myTempTable select * from My table

and insert 100000 rows in this operation, here you can find out exact time taken by database to perform insert operation.

If insertion time is high at database end then first look at database tuning.

because we can not rule out possibility of what Jeroen said

by the way which database are you using

if data insertion time is very less then problem is somewhere else.

thanks
[ March 23, 2005: Message edited by: Shailesh Chandra ]

[Sripathi Krishnamurthy]

there are a couple of more which you try

1) while doing inserts, is the database doing any other operations?
2) Is the server where the database is hosted doing any heavy load operations? (if db is also in local, you can skip this)
3) Is the client which is running the java program doing any heavy duty operations?
4) also check the network speed and see if there are any hassles with network.(if db is also in local, you can skip this)

These are worth checking. Also you can increase the batch update from 1000 to say 5000 and then check the performance.

[Pete Neu]

The database and the application server are
installed on the same machine. There is no
network traffic.

I can't change anything in the database
because I can't tell if the user is going
to use the same instance. The only option is to
improve on anything related to the Java Code.

The database I have to use is MaxDB.

[Adeel Ansari]

Originally posted by Pete Neu:
I can't change anything in the database
because I can't tell if the user is going
to use the same instance.

What do you really mean?

[Pete Neu]

I'm just trying to say that I can't
configure the database because
the potential user will install
his own database.

[Adeel Ansari]

Originally posted by Pete Neu:
I'm just trying to say that I can't configure the database because the potential user will install his own database.

Whatever. Couldn't you guide the user through DB design/creation. In your case I think you probably need to handover some document/diagrams, like ERD or SQL Scripts etc., for DB creation.

(Nice point Sripathy, really appreciated. I was thinking of select.)

[Edited after sripathys reply]
Moreover, having no indexes is likely results in bad performance upon retrieval. I am not talking about full table scans.
[ March 24, 2005: Message edited by: Adeel Ansari ]

[Sripathi Krishnamurthy]

Moreover, having no indexes is likely results in bad performance.

If there are 100000 rows to be inserted to a table and the table has index for a column, then the insert will be slow.(I cant really say how slow, but I am sure it will be slow since it has to create index and update to the index file for each row)
So how can having no index result in bad performance?

[Pete Neu]

I got only yesterday around to try the "alter index unusable" statement.
The performance for the insert did not improve significantly. It's around
a few milliseconds although I can't say if this isn't related to some OS conditions.

Is there anything else I can try?

[Paul Sturrock]

Did you try Shailesh Chandra's first bit of advice? Try the bulk insert in the DB first to see if the DB is struggling.