aspose file tools*
The moose likes Oracle/OAS and the fly likes JDBC inserts too slow Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Reply locked New topic
Author

JDBC inserts 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!
denis sorn
Ranch Hand

Joined: Apr 30, 2008
Posts: 33
I never worked with oracle, but maybe I had similar problem with MySQL and INNODB engine. The problem was that innodb logs each insert. Because mysql and innodb support multiple inserts with one command I ended with creating big SQL statement (with StringBuffer) which inserts 30 40k rows at once.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30780
    
157

Please don't post the same question in multiple forums. I'll lock this one for you.


[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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC inserts too slow