Shulou(Shulou.com)05/31 Report--

This article shares with you the content of a sample analysis of the efficiency of mysql data insertion. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

When doing data insertion, I found that the previous office system did not consider the database performance at all, because the amount of data involved is small, time and efficiency can not be seen, but when the amount of data is so large that it needs to be inserted 10000 times per second, then you have to consider your sql statement. When inserting 100 pieces of data, you can think of a way to insert data:

1:for loops 100 times, inserting data again and again. The connection is inserted 100 times at a time, which is the most time-consuming and IO-and connection-consuming.

2: insert 100 data into a sql statement, then connect it once and insert the data. This time-consuming is better than the first one.

3: use things, insert 100 times, the last thing commit; this is faster than the second

4: multiple data insertion using insert statement itself

When the above methods are faced with a small amount of data, there is almost no difference, and we can't feel it at all. However, when the amount of data is slightly larger, such as 10000 pieces of data at a time. The speed and efficiency of insertion will come out.

This is the mysql instance class; this instance provides connections to mysql and database-related operations

Public class MySqlInstance {/ / connection string private static string mySqlConnectionStr = "Server = localhost;Database=test;Uid=root;Pwd=password.1;"; private static MySqlConnection _ mysqlConnect; private static MySqlConnection mysqlConnect {get {if (null = = _ mysqlConnect) {_ mysqlConnect = new MySqlConnection (mySqlConnectionStr);} return _ mysqlConnect;}} private static MySqlCommand _ mysqlCommand Private static MySqlCommand mysqlCommand {get {if (null = = _ mysqlCommand) {_ mysqlCommand = mysqlConnect.CreateCommand ();} return _ mysqlCommand;}} / / Open connection public static void OpenConnect () {mysqlConnect.Open ();} / / close connection public static void CloseConnect () {mysqlConnect.Close () } public static MySqlConnection Connection {get {return mysqlConnect;}} / / insert data in anti-injection mode / / insert using transaction 10000, and the last transaction commits public static int InsertData (string Command, List Params) {/ / Program time monitoring Stopwatch sw = new Stopwatch (); / / Program timing starts sw.Start () OpenConnect (); / / transaction start MySqlTransaction trans = mysqlConnect.BeginTransaction (); mysqlCommand.CommandText = Command; mysqlCommand.Parameters.AddRange (Params.ToArray ()); int count = 0; for (int I = 0; I

< 10000; i++) { if (mysqlCommand.ExecuteNonQuery() >

0) count++;} / / transaction commit trans.Commit (); CloseConnect (); mysqlCommand.Parameters.Clear (); / / timing stop sw.Stop (); TimeSpan ts2 = sw.Elapsed; Console.WriteLine (ts2.TotalMilliseconds); return count } / / query shows that the connection public static MySqlDataReader SelectData (string sql) {Stopwatch sw = new Stopwatch (); sw.Start (); / / OpenConnect (); MySqlCommand newcommond = new MySqlCommand (sql, mysqlConnect); MySqlDataReader data = newcommond.ExecuteReader (); / / CloseConnect (); sw.Stop (); TimeSpan ts2 = sw.Elapsed; Console.WriteLine (ts2.TotalMilliseconds) cannot be closed if you want to use it. Return data;} / public static DataSet SelectDataSet (string sql) {MySqlCommand newcommond = new MySqlCommand (sql, mysqlConnect) {MySqlCommand newcommond = new MySqlDataAdapter (); adapter.SelectCommand = newcommond; DataSet ds = new DataSet (); adapter.Fill (ds); return ds } / / unsafe insert has injected public static int InsertDataSql (string sql) {/ / OpenConnect (); mysqlCommand.CommandText = sql; int count = mysqlCommand.ExecuteNonQuery (); / / CloseConnect (); return count;} / / secure insert parameter use @ / / insert public static int InsertDataNoTran (string Command, List Params) {Stopwatch sw = new Stopwatch () 10000 times without transaction Sw.Start (); OpenConnect (); mysqlCommand.CommandText = Command; mysqlCommand.Parameters.AddRange (Params.ToArray ()); int count = 0; for (int I = 0; I

< 10000; i++) { if (mysqlCommand.ExecuteNonQuery() >

0) count++;} CloseConnect (); mysqlCommand.Parameters.Clear (); sw.Stop (); TimeSpan ts2 = sw.Elapsed; Console.WriteLine (ts2.TotalMilliseconds); return count;} / / one-time submission of 10000 insert statements to public static void test4 () {Stopwatch sw = new Stopwatch (); sw.Start (); MySqlInstance.OpenConnect () MySqlTransaction tran = MySqlInstance.Connection.BeginTransaction (); string command = string.Empty; for (int I = 0; I

< 10000; i++) { string temp = string.Format("insert into test.testtable(pname,pwd) value ('{0}','{1}'); \r\n", "name" + i, "password." + i); command += temp; } MySqlInstance.InsertDataSql(command); tran.Commit(); MySqlInstance.CloseConnect(); sw.Stop(); TimeSpan ts2 = sw.Elapsed; Console.WriteLine(ts2.TotalMilliseconds); } } 最后建立控制台程序，分别使用事务提交，不使用事务，和拼接10000条插入在组成事务，这三种方式做一个测试，打印出耗时。结果如图：

As you can see: 10000 inserts took only 4.7 seconds to use transaction commits, compared with 311 seconds without transactions, and 10000 insert statements took 7.3 seconds to assemble. This takes 7.3 seconds, in theory, it should be similar to using transactions in database sql execution. The time spent here is mainly used for string concatenation, while the client takes more time.

Paste the test program code:

Using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using MySql.Data;using MySql.Web;using MySql.Data.MySqlClient;using System.Diagnostics;using System.Data;namespace mysqlDEMO01 {class Program {static void Main (string [] args) {testInsert (); Console.ReadLine ();} / / use @ to prevent injection safely. Public static void testInsert () {List lmp = new List (); lmp.Add (new MySqlParameter ("@ pname", "hello2"); lmp.Add (new MySqlParameter ("@ pwd", "1232")); string command = "insert into test.testtable (pname,pwd) value (@ pname,@pwd);"; MySqlInstance.InsertData (command, lmp); List lmp2 = new List () Lmp2.Add (new MySqlParameter ("@ pname", "hello2"); lmp2.Add (new MySqlParameter ("@ pwd", "1232"); MySqlInstance.InsertDataNoTran (command, lmp2); test4 ();} Thank you for reading! This is the end of the article on "sample Analysis of mysql data insertion efficiency". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.