• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC insert too slow

 
Piyush Mattoo
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can decrease the number of database roundtrips by using executeBatch instead. This will increase performance.
 
Piyush Mattoo
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could disable any indices and constraints.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic