Inserting 40,000 records in MYSQL from Asp.Net

Recently I got a task of importing about 40,000 records for a fixed length delimited file to MY SQL database. Copying the data into a list of Business object was not a problem, but to insert about 40000 records in MYSQL in an asp.net application looked time consuming. More over this event would take place regularly and over 200 times a day and also user need to process the data after it has been uploaded.

My earlier post on multiple insert with Stored procedure seems would not be that effective here as all the data was different in the records.

I first started by using a simple Insert query and then pass the data as parameters. The first efforts completed the job in about 500+ seconds (measurement of the time was being done by the stopwatch class). And I knew for sure this is not going to work for me.  Then I realized that in MYSQL we can use one insert statement to insert multiple values by keep on passing the values in the insert statement. Below is an example.

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Hence I went ahead and created the string to insert the values in a loop. I made sure that Insert data in a batch (size of batch was configurable). I started with 20 records per batch and then 50 and hundred.
Till then it was good. The amount of time taken for these 40,000 plus records came down to about 50 seconds or so. But this was not good enough so I kept on increasing the numbers in batch  

After the hundred records for the batch I start to see a decline in performance of the insertion and the time taken to complete the job always started to increase. I analyzed the code and quickly realized that the problem was not with MYSQL but my usage of start to concatenate the query in the loop. As soon as I converted the string to stringBuilder class (to create the query) I could see that the improvement in performance and with a batch size of 5,000 to 10,000 per batch I could easily insert 40,000 plus records in less than 3 seconds in MYSQL database from Asp.net.

Vikram