MySql batch insert optimization SQL Execution efficiency example details, SQL Execution Efficiency

MySql batch insert optimization SQL Execution efficiency example details, SQL Execution Efficiency

Detailed description of MySql batch insert optimization SQL Execution efficiency instances

The number of itemcontractprice is about 10 thousand. Five logs are inserted for each itemcontractprice.

UpdateInsertSql. appendFormat ("UPDATE itemcontractprice AS p inner join foreigncurrency AS f ON p. foreignCurrencyId = f. contractPriceId SET p. remainPrice = f. remainPrice * {0}, p. buyOutPrice = f. buyOutPrice * {0}, p. reservedPrice = f. reservedPrice * {0}, p. collectedPrice = f. collectedPrice * {0}, p. accessPrice = f. accessPrice * {0} WHERE p. currencyId = {1} AND p. date between' {2: yyyy-MM-dd} 'AND' {3: yyyy-MM-dd} '; ", rate. exchangeRate, exchangeRate. currencyId, rate. beginDate, rate. endDate); updateInsertSql. appendFormat ("insert into 'itemcontractpricelog '('tractpricetype', 'tractprice', 'fctractprice', 'isexpire ', 'logremark', 'createdbyname', 'createdbyid ', 'createddate', 'logtypeid', 'providerid', 'stageid', 'date', 'currencyid', 'contractpriceid', 'stockpattern ', 'itemid') SELECT 0, c. remainPrice, f. remainPrice, c. remainIsExpire, 'foreign currency exchange rate adjustment, recalculating the renminbi reserve price', 'job', 0, NOW (), 5, c. providerId, c. stageId, c. date, c. currencyId, c. contractPriceId, 0, c. itemId FROM itemcontractprice AS c inner join foreigncurrency AS f ON c. foreignCurrencyId = f. contractPriceId WHERE c. currencyId = {0} AND c. date between' {1: yyyy-MM-dd} 'AND' {2: yyyy-MM-dd} '; ", exchangeRate. currencyId, rate. beginDate, rate. endDate); updateInsertSql. appendFormat ("insert into 'itemcontractpricelog '('tractpricetype', 'tractprice', 'fctractprice', 'isexpire ', 'logremark', 'createdbyname', 'createdbyid ', 'createddate', 'logtypeid', 'providerid', 'stageid', 'date', 'currencyid', 'contractpriceid', 'stockpattern ', 'itemid') SELECT 1, c. buyOutPrice, f. buyOutPrice, c. buyOutIsExpire, 'foreign currency exchange rate adjustment, re-calculate the renminbi reserve price', 'job', 0, NOW (), 5, c. providerId, c. stageId, c. date, c. currencyId, c. contractPriceId, 0, c. itemId FROM itemcontractprice AS c inner join foreigncurrency AS f ON c. foreignCurrencyId = f. contractPriceId WHERE c. currencyId = {0} AND c. date between' {1: yyyy-MM-dd} 'AND' {2: yyyy-MM-dd} '; ", exchangeRate. currencyId, rate. beginDate, rate. endDate); updateInsertSql. appendFormat ("insert into 'itemcontractpricelog '('tractpricetype', 'tractprice', 'fctractprice', 'isexpire ', 'logremark', 'createdbyname', 'createdbyid ', 'createddate', 'logtypeid', 'providerid', 'stageid', 'date', 'currencyid', 'contractpriceid', 'stockpattern ', 'itemid') SELECT 2, c. reservedPrice, f. reservedPrice, c. reservedIsExpire, 'foreign currency exchange rate adjustment, re-calculate the renminbi reserve price', 'job', 0, NOW (), 5, c. providerId, c. stageId, c. date, c. currencyId, c. contractPriceId, 0, c. itemId FROM itemcontractprice AS c inner join foreigncurrency AS f ON c. foreignCurrencyId = f. contractPriceId WHERE c. currencyId = {0} AND c. date between' {1: yyyy-MM-dd} 'AND' {2: yyyy-MM-dd} '; ", exchangeRate. currencyId, rate. beginDate, rate. endDate); updateInsertSql. appendFormat ("insert into 'itemcontractpricelog '('tractpricetype', 'tractprice', 'fctractprice', 'isexpire ', 'logremark', 'createdbyname', 'createdbyid ', 'createddate', 'logtypeid', 'providerid', 'stageid', 'date', 'currencyid', 'contractpriceid', 'stockpattern ', 'itemid') SELECT 3, c. collectedPrice, f. collectedPrice, c. collectedIsExpire, 'foreign currency exchange rate adjustment, recalculating the renminbi reserve price', 'job', 0, NOW (), 5, c. providerId, c. stageId, c. date, c. currencyId, c. contractPriceId, 0, c. itemId FROM itemcontractprice AS c inner join foreigncurrency AS f ON c. foreignCurrencyId = f. contractPriceId WHERE c. currencyId = {0} AND c. date between' {1: yyyy-MM-dd} 'AND' {2: yyyy-MM-dd} '; ", exchangeRate. currencyId, rate. beginDate, rate. endDate); updateInsertSql. appendFormat ("insert into 'itemcontractpricelog '('tractpricetype', 'tractprice', 'fctractprice', 'isexpire ', 'logremark', 'createdbyname', 'createdbyid ', 'createddate', 'logtypeid', 'providerid', 'stageid', 'date', 'currencyid', 'contractpriceid', 'stockpattern ', 'itemid') SELECT 4, c. accessPrice, f. accessPrice, c. accessIsExpire, 'foreign currency exchange rate adjustment, recalculating the renminbi reserve price', 'job', 0, NOW (), 5, c. providerId, c. stageId, c. date, c. currencyId, c. contractPriceId, 0, c. itemId FROM itemcontractprice AS c inner join foreigncurrency AS f ON c. foreignCurrencyId = f. contractPriceId WHERE c. currencyId = {0} AND c. date between' {1: yyyy-MM-dd} 'AND' {2: yyyy-MM-dd} '; ", exchangeRate. currencyId, rate. beginDate, rate. endDate); // var curContractPriceList = itemContractPriceList. where (o => o. currencyId = exchangeRate. currencyId & o. date> = rate. beginDate & o. date <= rate. endDate ). toList (); logger. infoFormat ("base price update and log SQL: {0}", updateInsertSql. toString (); // if (curContractPriceList. count = 0) continue; int effctRows = 0; using (var tran = UnitOfWorkManager. begin () {effctRows = taskRepository. executeSql (updateInsertSql. toString (), false); tran. complete ();} logger. infoFormat ("base price update affected rows: {0}", effctRows );

Normally, it takes about 20 seconds.

Previously, EF operations were used to query the logs, which took a long time, then assembled the update statement, and inserted the logs (five logs per piece of data ), the network interaction time plus the time when the database connection is opened and closed. The total execution time is about 10 minutes.

Batch operations with SQL statements can be said to have increased the efficiency by 40 times, that is, the time consumed for massive data transmission and database processing.

Therefore, software development should not be completed, but should solve performance problems. This is the advanced development stage.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!