MySql批量插入來實現(xiàn)優(yōu)化Sql執(zhí)行效率的實例代碼

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,'外幣匯率調(diào)整，重新計算人民幣底價','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,'外幣匯率調(diào)整，重新計算人民幣底價','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,'外幣匯率調(diào)整，重新計算人民幣底價','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,'外幣匯率調(diào)整，重新計算人民幣底價','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,'外幣匯率調(diào)整，重新計算人民幣底價','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?=&gt;?o.CurrencyId?==?exchangeRate.CurrencyId?&amp;&amp;?o.Date?&gt;=?rate.BeginDate?&amp;&amp;?o.Date?<p>正常情況下大概20秒鐘就ok.<br></p><p>之前是用EF操作，查詢出來 ，要耗時，然后再組裝 update語句 ，然后再插入日志（每條數(shù)據(jù)5條日志），這個網(wǎng)絡(luò)交互的時間加上數(shù)據(jù)庫連接打開關(guān)閉的時間，總的執(zhí)行時間，大概10多分鐘。</p><p>用sql語句批量操作，可以說效率提升了 40倍，就是大量數(shù)據(jù)的傳輸和數(shù)據(jù)庫的處理次數(shù)耗時。</p><p>所以說，軟件開發(fā)不是開發(fā)完成就行，而是要解決性能上的問題，這才是開發(fā)的進階。</p>