Most Efficient Way to Insert Rows into MySQL Database

Most efficient way to insert Rows into MySQL Database

Here is my "multiple inserts"-code.

The insertion of 100k rows took instead of 40 seconds only 3 seconds!!

public static void BulkToMySQL()
{
    string ConnectionString = "server=192.168.1xxx";
    StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES ");           
    using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
    {
        List<string> Rows = new List<string>();
        for (int i = 0; i < 100000; i++)
        {
            Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test")));
        }
        sCommand.Append(string.Join(",", Rows));
        sCommand.Append(";");
        mConnection.Open();
        using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
        {
            myCmd.CommandType = CommandType.Text;
            myCmd.ExecuteNonQuery();
        }
    }
}

The created SQL-statement looks like this:

INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;

Update: Thanks Salman A I added MySQLHelper.EscapeString to avoid code injection which is internally used when you use parameters.

Quickest way to Insert mass data Into Mysql database

Using one INSERT statement with multiple rows is faster than one INSERT statement per row.
This will reduce calls to the database.

Example:

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

Efficient multiple row inserts in mysql

The second is more efficient.

The first method creates a connection each time you want to insert a row, where the second uses a single connection to insert all your rows. There is, however, a max_allowed_packet that limits the length of the client's INSERT statement.

How can I insert rows into mysql table faster using python?

If some of your rows succeeds but the some fails, Do you want your database to be left in a corrupt state? if no, try to commit out of the loop. like this:

for x in sData:
    tupleList = tuple(x)
    sql = "INSERT INTO streams (id, user_id, user_name, game_id, community_ids, type, title, viewer_count, started_at, language, thumbnail_url, tag_ids, time_stamp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    val = tupleList
    try:
        mycursor.execute(sql, val)
    except:
        # do some thing
        pass
try:
    mydb.commit()
except:
    test = 1

And if you don't. try to load your cvs file into your mysql directly.

LOAD DATA INFILE "/home/your_data.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Also, to make you more clear. I've define three ways to insert those data, if you insistent to use python, since you have some processing with your data.

Bad way

In [18]: def inside_loop(): 
    ...:     start = time.time() 
    ...:     for i in range(10000): 
    ...:         mycursor = mydb.cursor() 
    ...:         sql = "insert into t1(name, age)values(%s, %s)" 
    ...:         try: 
    ...:             mycursor.execute(sql, ("frank", 27)) 
    ...:             mydb.commit() 
    ...:         except: 
    ...:             print("Failure..") 
    ...:     print("cost :{}".format(time.time() - start)) 
    ...: 

Time cost:

In [19]: inside_loop()                                                                                                                                                                                                                        
cost :5.92155909538269 

Okay way

In [9]: def outside_loop(): 
   ...:     start = time.time() 
   ...:     for i in range(10000): 
   ...:         mycursor = mydb.cursor() 
   ...:         sql = "insert into t1(name, age)values(%s, %s)" 
   ...:         try: 
   ...:             mycursor.execute(sql, ["frank", 27]) 
   ...:         except: 
   ...:             print("do something ..") 
   ...:              
   ...:     try: 
   ...:         mydb.commit() 
   ...:     except: 
   ...:         print("Failure..") 
   ...:     print("cost :{}".format(time.time() - start))

Time cost:

In [10]: outside_loop()                                                                                                                                                                                                                       
cost :0.9959311485290527

Maybe, there are still having some better way, even best. (i.e, use pandas to process your data. and try redesign your table ...)

What's the most efficient way to insert thousands of records into a table (MySQL, Python, Django)

You can write the rows to a file in the format
"field1", "field2", .. and then use LOAD DATA to load them

data = '\n'.join(','.join('"%s"' % field for field in row) for row in data)
f= open('data.txt', 'w')
f.write(data)
f.close()

Then execute this:

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

Reference

---

Related Topics