MySql批量插入优化Sql执行效率实例详解
程序员文章站
2023-02-17 14:09:25
mysql批量插入优化sql执行效率实例详解
itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志。
updat...
mysql批量插入优化sql执行效率实例详解
itemcontractprice数量1万左右,每条itemcontractprice 插入5条日志。
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`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 0,c.remainprice,f.remainprice,c.remainisexpire,'外币汇率调整,重新计算人民币底价','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`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 1,c.buyoutprice,f.buyoutprice,c.buyoutisexpire,'外币汇率调整,重新计算人民币底价','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`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 2,c.reservedprice,f.reservedprice,c.reservedisexpire,'外币汇率调整,重新计算人民币底价','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`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 3,c.collectedprice,f.collectedprice,c.collectedisexpire,'外币汇率调整,重新计算人民币底价','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`(`contractpricetype`,`contractprice`,`fccontractprice`,`isexpire`,`logremark`,`createdbyname`,`createdbyid`,`createddate`,`logtypeid`,`providerid`,`stageid`,`date`,`currencyid`,`contractpriceid`,`stockpattern`,`itemid`) select 4,c.accessprice,f.accessprice,c.accessisexpire,'外币汇率调整,重新计算人民币底价','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("底价更新和日志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("底价更新影响行数:{0}", effctrows);
正常情况下大概20秒钟就ok.
之前是用ef操作,查询出来 ,要耗时,然后再组装 update语句 ,然后再插入日志(每条数据5条日志),这个网络交互的时间加上数据库连接打开关闭的时间,总的执行时间,大概10多分钟。
用sql语句批量操作,可以说效率提升了 40倍,就是大量数据的传输和数据库的处理次数耗时。
所以说,软件开发不是开发完成就行,而是要解决性能上的问题,这才是开发的进阶。
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!