Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to reduce time taken to insert 100000 records in database using PreparedStatement batch ?

 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


This program runs successfully.
It's taking 14 seconds to insert 100000 simple records of 4 columns i.e empid, empname, age, gender. How can I reduce the timing even records are complex and more than 100000 ?
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depending on the memory assigned to Java and network speed between Java and DB, batch size can be selected.
I will not set a batch size to 10 as it will do a lot of iterations between the network, and I will not set a batch size of 10000 to avoid out of memory error and this will put a lot of data in network at a time.
You can go for batch size of 500 so that it will be a balance between network iteration and avoid out of memory error.
So after every 500th addBatch(), do an executeBatch().
Again, there is no hard n fast rule, optimum-performance-batch-size can vary depending on factors.
 
Knute Snortum
Bartender
Pie
Posts: 2897
62
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'll copy this to the JDBC forum to see if we can get more responses.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Apologies for delay, had internet problem. Here in this example I made a batch of 5 records. After this I called executeBatch(). so
The list of commands associated with stmt will now be empty because the four commands added previously were sent to the database when stmt called the method executeBatch. You can at any time explicitly empty this list of commands with the method clearBatch.
JLS link still after inserting 5000 records means 1000 batch I'm explicitly clearing batch commands.
So after every 500th addBatch(), do an executeBatch().
do you mean after 500 records ? because what I'm thinking is
above five pstmt.addBatch() is one batch of 5 records hope I'm correct ? and inserting them once when I call executeBatch() so one batch of 5 records right ?
 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
do you mean I should put this code instead of code pstmt.executeBatch(); on line no 101 in loop?
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes exactly.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes I did that still takes 14074 milliseconds. Do I have to use procedures here. I used that many moons ago but right now I don't remember its usage. will figure it out once I finish with Servlet and JSP.
Why I'm curious about this stuff is because when I was working as trainee in a company I had a client project in which I had to insert 80000- 90000 records in a table, these records were result of executing select SQL query from different tables, using batch it used to take 17 minutes something like that. By the time I could solve that the senior found the solution but I didn't how he solved. anyway if you find solution please let me know, Thank you.
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stored Procedure is not a solution in this scenario. They are meant for different purpose.
By the way 14 secs(only for insert) sound OK to me.
 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ya I think so.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic