MySQL database inset Performance Optimization

MySQL database inset Performance Optimization

When using MySQL, we will inevitably encounter a large volume of data inset. The simplest method is to write an insert statement, and then assign values to the variables in a loop to insert data into the database in batches:

// Save rddform for (int I = 0; I <rddformlist. count; I ++) {string literal text = "insert into rddform (ID, CreatedTime, ModifiedTime, CreatedBy, ModifiedBy, FormType) values ('" + rddformlist [I]. rddFormGuid + "','" + rddformlist [I]. createTime + "','" + rddformlist [I]. modifyTime + "','" + rddformlist [I]. createBy + "','" + rddformlist [I]. modifyBy + "'," + rddformlist [I]. formType + ")"; MySqlCommand mysqlcom = new MySqlCommand (plain text, mysqlcon); mysqlcom. transaction = trans; // bind the Transaction mysqlcom. executeNonQuery ();}

After the method is tested, the performance is not good. The local data is about 20 pieces of insertdata per second, and the cloud data is about one second of insert3 data. It takes about one and a half minutes for two thousand pieces of data to be stored locally, and about 10 minutes on the cloud. Obviously, the speed is too slow.

Now we change to merge and insert multiple data records, that is, insert all data using one insert statement:

// Save rddform for (int I = 0; I <rddformlist. count; I ++) {strRddForm. append ("('" + rddformlist [I]. rddFormGuid + "','" + rddformlist [I]. createTime + "','" + rddformlist [I]. modifyTime + "','" + rddformlist [I]. createBy + "','" + rddformlist [I]. modifyBy + "','" + rddformlist [I]. formType + "')"); if (I <rddformlist. count-1) {strRddForm. append (",") ;}} MySqlCommand rddformcom = new MySqlCommand (strRddForm. toString (), mysqlcon); rddformcom. transaction = trans; // bind the Transaction rddformcom. executeNonQuery ();

After the test, the first two thousand pieces of local data were inserted in seconds, and there was about one second on the cloud. There was no reason to reject this speed, and the Code was changed. Of course, the speed may be based on personal machines and network environment factors. The following is a comparison of the performance of online cool people.

Note: