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.)

Solution 1

There are a number of ways to optimize bulk inserts. Some are:

• LOAD DATA INFILE. There is a wrapper API for .NET. This is the fastest way, but has some limitations and semantic differences from simple inserts.

• Multiple-row INSERT statements:

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

  You should not insert 20.000.000 rows at once, but may want to try 1.000-10.000 for a very large speed-up. This is a simple and very unproblematic way to increase speed. A factor of 10 and sometimes way more is often possible.

• Locking the table (LOCK TABLES).

• Disabling indexes temporarily.

• MySQL options tuning.

• INSERT DELAYED (most likely not that useful here).

The documentation does give you more elaborate detail on the options. Some options depend on the table type (InnoDB vs. MyISAM).

A general suggestion: Always specify the columns that you insert in front of VALUES. This makes for more maintainable code.

Solution 2

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.

Solution 3

You should do bulk inserts. The ADO.NET way to do it is by using a DataAdapter.

For a MySQL specific solution, use the MySqlBulkLoader.

Solution 4

General rules :-

1. use load data infile
2. disable key during import, enable it back after all data has been import
3. run the script at the database server itself, connect using socket instead of tcp/ip

Most of the tips are explained at the documentation.

View more solutions

Comments

[Daniel Mošmondor]

general rule #2 won't apply here, since it is autonumber primary key