wood burning stoves 2.0*
The moose likes JDBC and the fly likes Bad Performance for JDBC Insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Bad Performance for JDBC Insert" Watch "Bad Performance for JDBC Insert" New topic
Author

Bad Performance for JDBC Insert

Pete Neu
Ranch Hand

Joined: Feb 18, 2005
Posts: 86
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
Ranch Hand

Joined: Oct 12, 2000
Posts: 5093
Sounds more like your database is slow to handle the load than that your application is slow to tell the database what to do...


42
Pete Neu
Ranch Hand

Joined: Feb 18, 2005
Posts: 86
Is there anything that I can do to take the load of the database?
I'm using MaxDB.
Sripathi Krishnamurthy
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
when you are doing so many inserts, it is advisable to use a Batch command.
try this,

[ March 22, 2005: Message edited by: Sripathi K S ]
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Moving to JDBC...


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Pete Neu
Ranch Hand

Joined: Feb 18, 2005
Posts: 86
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
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
Ranch Hand

Joined: Feb 18, 2005
Posts: 86
if I get you right I should do something like this:



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

Joined: Aug 13, 2004
Posts: 1081

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 ]

Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Sripathi Krishnamurthy
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
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
Ranch Hand

Joined: Feb 18, 2005
Posts: 86
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
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
Ranch Hand

Joined: Feb 18, 2005
Posts: 86
I'm just trying to say that I can't
configure the database because
the potential user will install
his own database.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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
Ranch Hand

Joined: Mar 07, 2005
Posts: 232
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
Ranch Hand

Joined: Feb 18, 2005
Posts: 86
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
Bartender

Joined: Apr 14, 2004
Posts: 10336

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.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Bad Performance for JDBC Insert
 
Similar Threads
Model to add to a database
Using single quotes in a SQL statement
out of range
problem with prepared statement
escaping quotes, single quotes in a string