Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error inserting data into database after few successfull insert.

 
Jigar Naik
Ranch Hand
Posts: 762
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I'm trying to insert 3000 reocrds into MySQL Database from CSV file using JDBC but after seccessfull insertion of aprox 1300 records, Its giving me the connection refuse to connect.

Stack Trace :

 
Scott Selikoff
author
Saloon Keeper
Posts: 4015
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Usually this happens because of a timeout value somewhere in the connection between the java process and the server. First, I'd check the MySQL configuration and increase timeouts there. Then I'd examine the web server for similar timeouts.

Also, how are grouping your inserts? One record per insert statement, multiple records per insert statement, or all records within a single insert statement?

You can either find the timeout value and increase, or insert 1,000 records at a time. It's been my experience that the optimal solution to bulk insertion for network bandwidth and database performance, is to insert about 500 records per insert statement (although keep the 500 value configurable). Often in larger scale environments this prevents timeouts from ever being hit. In one case of inserting data on a shared server I saw performance like this for bulk inserts:

1 record per insert statement: 1 hour to complete
500 records per insert statement: 35 seconds to complete
All records (50k) per insert statement: never completed do to web server timeout

In short, bulk inserts require more thought and planning if they are a common occurance than traditional inserts.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic