MySql批量插入优化Sql执行效率实例详解
作者:lqh 时间:2024-01-18 07:52:06
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倍,就是大量数据的传输和数据库的处理次数耗时。
所以说,软件开发不是开发完成就行,而是要解决性能上的问题,这才是开发的进阶。
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
标签:MySql,优化Sql
0
投稿
猜你喜欢
js replace()去除代码中空格的实例
2024-04-29 13:36:26
实例讲解在MySQL中如何导出整个数据库
2009-09-01 10:03:00
python中的Elasticsearch操作汇总
2022-01-29 10:44:45
Python基于列表list实现的CRUD操作功能示例
2022-06-15 16:02:14
python对RabbitMQ的简单入门使用教程
2021-01-13 17:53:44
浅谈几种常用的JS类定义方法
2024-04-22 12:56:56
Python爬虫之UserAgent的使用实例
2022-03-10 15:41:39
两个命令把 Vim 打造成 Python IDE的方法
2022-01-20 09:31:31
解决python打不开文件(文件不存在)的问题
2021-10-15 02:39:46
python实现Excel文件转换为TXT文件
2021-01-15 05:24:54
python简单图片操作:打开\\显示\\保存图像方法介绍
2021-03-29 15:52:28
Python+OpenCV目标跟踪实现基本的运动检测
2023-05-14 14:30:54
用VB生成DLL封装ASP代码一个例子:连接access数据库等
2008-04-07 13:06:00
基于vue cli 通过命令行传参实现多环境配置
2024-04-29 13:08:30
在MySQL中创建实现自增的序列(Sequence)的教程
2024-01-25 02:42:13
Python如何读取相对路径文件
2023-12-06 17:03:18
python 装饰器的实际作用有哪些
2023-04-10 15:00:50
Vue.js自定义事件的表单输入组件方法
2024-05-05 09:12:24
Golang 的defer执行规则说明
2023-07-14 08:31:10
详解JavaScript基于面向对象之继承
2024-04-29 13:23:06