.net - How to insert a very large number of records into a MySql database as fast as possible

Question

I have a database table like below:

create table temperature
(id int unsigned not null auto_increment primary key,
temperature double
);

And in my program I got about 20 million temperature to insert into the table. I worke in .Net environment, use Connector/Net connecting to MySql. The code was like below:

List<double> temps = new List<double>();
...
string connStr = "server=localhost;user=name;database=test;port=3306;password=*****;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
    conn.Open();

    //temps.Count is about 20 million
    for (int i = 0; i < temps.Count; i++)
    {
        string sql1 = "INSERT INTO temperature VALUES (null, "+temps[i]+")";
        MySqlCommand cmd1 = new MySqlCommand(sql1, conn);
        cmd1.ExecuteNonQuery();
    }

}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}
conn.Close();

How can i insert so many lines data as fast as possible? (It can only insert 2000 records every minute in my computer.)

Answer 1

you can use the concept of bulk insert which executes many inserts at the same time minimizing overhead of calling ExecuteNonQuery multiple times.

in MySQL this is called LOAD DATA, check here for details: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

in MS SQL Server this is called bulk insert and it's known as such, that's why I've mentioned it with this name.