bulk insert to mysql

Question

Hello.

I am fairly new to MySql but am looking for a way to speed up my inserts. I've earlier used bulkinsert to insert fast into MS SQL but we are looking at migrating to MySQL and the simple insert (for each(DataRow row in DataTable.Rows) takes way too long.

I will insert (approx 2 times/day) a few hundred thousand rows to remote servers. And several of them.

I've searched all over the web to find an easy way to do this without much success. In my testcase I insert about 200 000 rows and it takes about 15 minutes to a remote server.

Is there an eqvivalent to bulkinsert to use against mysql db? Or is the best way to first write it to a file and then use a sql procedure for the insert? I would prefer the first method but performance is most important.

Thanks for any tips!
Best regards,
Joakim.

Crish · Answer

hi Only one possible solution is you need to make string for multiple rows in your code and then pass this string in stored procedure . In stored procedure you need to split all the rows and then insert it in database. Ex. if you can make one string using 100 different rows using || separator then pass this string in SP. Split string in SP and insert that 100 rows. You will get performance.