| 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: 26144
|
|
|
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: jrebel
|
|
subject: JDBC insert too slow
|
|
|