aspose file tools*
The moose likes JDBC and the fly likes JDBC insert too slow Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC insert too slow" Watch "JDBC insert too slow" New topic
Author

JDBC insert too slow

Piyush Mattoo
Ranch Hand

Joined: Mar 12, 2007
Posts: 30
I have to insert 100K records into a table having 4 fields. Normal JDBC Insert seems to load 2K records per minute. Any ideas how to improve the execution time?
The flow is something like this:
Open a connection.
Read the record from a .txt file inside a loop.
Issue executeUpdate to insert the record.
Commit after every 10 records. (Tried autocommitting or commiting after x records; where x were different combinations)
Outside the loop, when all the input have been read; i close the connection and file reader, Buffered reader objects.
In the finally block, i close the Statement, connection objects.


TIA!
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

You can decrease the number of database roundtrips by using executeBatch instead. This will increase performance.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Piyush Mattoo
Ranch Hand

Joined: Mar 12, 2007
Posts: 30
Yeah..i tried executing about 100 records together in a batch and committing them but there seems to be no improvement whatsoever.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You could disable any indices and constraints.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC insert too slow