This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
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


Win a copy of Java 8 in Action this week in the Java 8 forum!
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

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
 
Similar Threads
data visibility across weblogic pool connection and JDBC connection
Weblogic pool connection Vs JDBC Connection
Error connect to db after several transaction
JDBC insert too slow
Problem updating batch records in Oracle through Java - URGENT