aspose file tools*
The moose likes JDBC and the fly likes Error inserting data into database after few successfull insert. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error inserting data into database after few successfull insert." Watch "Error inserting data into database after few successfull insert." New topic
Author

Error inserting data into database after few successfull insert.

Jigar Naik
Ranch Hand

Joined: Dec 12, 2006
Posts: 755
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 :



Jigar Naik


Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error inserting data into database after few successfull insert.