Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

JDBC insert too slow

 
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!
 
author & internet detective
Posts: 39986
806
Eclipse IDE VI Editor Java
  • 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.
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could disable any indices and constraints.
 
Oh, sure, you could do that. Or you could eat some pie. While reading this tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic