How to insert 20 million records into MySql database as quickly as possible

Question

I have a database table as shown below:

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

And in my program, I got about 20 million temperatures to insert into the table. I work in a .Net environment, using Connector / Net to connect to MySql. The code was as follows:

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 as many rows of data as quickly as possible? (It can only insert 2,000 entries every minute on my computer.)

Answer 1

you can use a concept bulk insertthat does a lot of insertion while minimizing the overhead of a call ExecuteNonQueryseveral times.

MySQL LOAD DATA, : http://dev.mysql.com/doc/refman/5.5/en/load-data.html

MS SQL Server bulk insert, , .

Answer 2

. :

• LOAD DATA INFILE. API- .NET. , .

• INSERT:

  INSERT INTO temperature (temperature) VALUES (1.0), (2.0), (3.0), ...

  20.000.000 , 1.000-10.000 . . 10, .

• (LOCK TABLES).

• .

• MySQL.

• INSERT DELAYED ( , ).

. (InnoDB MyISAM).

: , VALUES. .

Answer 3

. ADO.NET DataAdapter.

MySQL MySqlBulkLoader.

Answer 4

: -

• , ,
• script , tcp/ip

.