• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Bad Performance for JDBC Insert

 
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 5093
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is there anything that I can do to take the load of the database?
I'm using MaxDB.
 
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moving to JDBC...
 
Pete Neu
Ranch Hand
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 232
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 232
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 86
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
We can fix it! We just need some baling wire, some WD-40, a bit of duct tape and this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic