如何优化foreach语句的EF性能

Question

这是我的代码；正如您所看到的，我为每一行保存更改，但我想提高性能，因为我每次都有大量数据，比如50.000或100.000行或更多。这需要很多时间才能完成。

如何提高EFSaveChanges的性能？我尝试了一些第三方库的bulksave、bulkupdate，但它没有在数据库中更新。更新50.000行需要2个小时。我想为这种方法增加时间。

private void TransferOrders()
{
using (var context = new BbsfDbContext())
{
context.DisableFilter(AbpDataFilters.MayHaveTenant);
context.DisableFilter("LanguageSpecificFilter");
var sapOrders = context.SapOrders
.Where(p => p.VBTYP != null && 
p.VBTYP.ToLower() == OrderDocumentType && 
p.IsRead == false)
//.Where(p => p.VBTYP != null && p.VBTYP.ToLower() == OrderDocumentType && p.Id == 3025)
.Where(p => !ActiveUsersOnly || context.Users.Where(u => u.IsActive).Select(a => a.MainVendor.SapCode).Contains(p.KUNNR))
.OrderBy(p => p.CreatedDate)
.ToList();
if (sapOrders.Any())
{
foreach (var item in sapOrders)
{
try
{
var order = context.Orders.FirstOrDefault(p => p.SapCode == item.VBELN);
var isExist = context.SapOrderDetails.Any(p => p.DOCNUM == item.DOCNUM);
if (isExist)
{
var salesOrganization = context.SalesOrganizations.FirstOrDefault(p => p.SapCode == item.VKORG);
if (salesOrganization == null)
continue;
var distributionChannel = context.DistributionChannels.FirstOrDefault(p => p.SapCode == item.VTWEG);
if (distributionChannel == null)
continue;
var salesDepartment = context.SalesDepartments.FirstOrDefault(p => p.SapCode == item.SPART);
if (salesDepartment == null)
continue;
var salesOffice = context.SalesOffices
.FirstOrDefault(p => p.SapCode == item.VKBUR &&
p.SalesOrganization.Id == salesOrganization.Id &&
p.DistributionChannel.Id == distributionChannel.Id &&
p.SalesDepartment.Id == salesDepartment.Id);
if (salesOffice == null)
continue;
var ordererCustomer = context.Customers
.FirstOrDefault(p => p.SapCode == item.KUNNR &&
p.SalesOrganization.Id == salesOrganization.Id &&
p.DistributionChannel.Id == distributionChannel.Id &&
p.SalesDepartment.Id == salesDepartment.Id &&
p.SalesOffice.Id == salesOffice.Id);
var recipientCustomer = context.Customers
.FirstOrDefault(p => p.SapCode == item.KUNWE &&
p.SalesOrganization.Id == salesOrganization.Id &&
p.DistributionChannel.Id == distributionChannel.Id &&
p.SalesDepartment.Id == salesDepartment.Id &&
p.SalesOffice.Id == salesOffice.Id);
if (recipientCustomer == null)
recipientCustomer = context.Customers
.FirstOrDefault(p => p.SapCode == item.KUNWE &&
p.SalesOrganization.Id == salesOrganization.Id &&
p.DistributionChannel.Id == distributionChannel.Id &&
p.SalesDepartment.Id == salesDepartment.Id &&
p.SalesOffice == null);
if (ordererCustomer == null || recipientCustomer == null)
continue;
if (order == null)
{
order = new Order
{
SapCode = item.VBELN,
SapOrderDate = item.AUDAT,
DocumentType = context.DocumentTypes.FirstOrDefault(p => p.SapCode == item.VBTYP),
SalesDocument = context.SalesDocuments.FirstOrDefault(p => p.SapCode == item.AUART),
BaseAmount = item.NETWR,
TotalTax = item.MWSBT,
Currency = context.CurrencyDefinitions.FirstOrDefault(p => p.SapCode == item.WAERK),
SalesOrganization = salesOrganization,
DistributionChannel = distributionChannel,
SalesDepartment = salesDepartment,
SalesGroup = context.SalesGroups.FirstOrDefault(p => p.SapCode == item.VKGRP && p.SalesOffice.Id == salesOffice.Id),
SalesOffice = salesOffice,
RequestedDeliveryDate = item.VDATU,
SASNo = item.BSTNK,
SASOrderDate = item.BSTDK ?? item.AUDAT,
OrdererCustomer = ordererCustomer,
RecipientCustomer = recipientCustomer,
//PRSDT
Status = OrderStatus.Approved,
Type = OrderType.MainVendor,
DeliveryAddress = context.CustomerAddressBooks.FirstOrDefault(p => p.MainVendor.Id == ordererCustomer.Id && p.SubVendor.Id == recipientCustomer.Id),
CreationTime = DateTime.Now,
LastModificationTime = DateTime.Now,
CreatorUserId = context.Users.First(p => p.UserName == AbpUserBase.AdminUserName).Id,
LastModifierUserId = context.Users.First(p => p.UserName == AbpUserBase.AdminUserName).Id,
IsSubVendorOrder = false,
IsSameDayDelivery = false,
RepresentativeId = context.Users.First(p => p.UserName == AbpUserBase.AdminUserName).Id
//ProductionSite
//RejectionReason =//todo:bu silinmeli iptal kalem bazında burada statu olmalı
};
var savedOrder = context.Orders.Add(order);
context.SaveChanges();
order.SASNo = BbsfConsts.KeasOrderNumberPrefix + savedOrder.Id;
}
else
{
order.SapOrderDate = item.AUDAT;
order.DocumentType = context.DocumentTypes.FirstOrDefault(p => p.SapCode == item.VBTYP);
order.SalesDocument = context.SalesDocuments.FirstOrDefault(p => p.SapCode == item.AUART);
order.BaseAmount = item.NETWR;
order.TotalTax = item.MWSBT;
order.Currency = context.CurrencyDefinitions.FirstOrDefault(p => p.SapCode == item.WAERK);
order.SalesOrganization = salesOrganization;
order.DistributionChannel = distributionChannel;
order.SalesDepartment = salesDepartment;
order.SalesGroup = context.SalesGroups.FirstOrDefault(p => p.SapCode == item.VKGRP && p.SalesOffice.Id == salesOffice.Id);
order.SalesOffice = salesOffice;
order.RequestedDeliveryDate = item.VDATU;
order.SASNo = BbsfConsts.KeasOrderNumberPrefix + order.Id;
//order.SASOrderDate = item.BSTDK.HasValue ? item.BSTDK : item.AUDAT;
order.OrdererCustomer = ordererCustomer;
order.RecipientCustomer = recipientCustomer;
//PRSDT
//order.Status = OrderStatus.Approved;
order.DeliveryAddress = context.CustomerAddressBooks.FirstOrDefault(p => p.MainVendor.Id == ordererCustomer.Id && p.SubVendor.Id == recipientCustomer.Id);
order.LastModifierUserId = context.Users.First(p => p.UserName == AbpUserBase.AdminUserName).Id;
order.LastModificationTime = DateTime.Now;
//ProductionSite
//RejectionReason =//todo:bu silinmeli iptal kalem bazında burada statu olmalı
}
}
else
{
if (order != null)
{
var orderDetails = context.OrderDetails.Where(p => p.OrderId == order.Id).ToList();
orderDetails?.ForEach(p => context.OrderDetails.Remove(p));
context.SaveChanges();
context.Orders.Remove(order);
context.SaveChanges();
}
}
item.IsRead = true;
item.ModifiedDate = DateTime.Now;
context.SaveChanges();
}
catch (Exception ex)
{
logger.Error(ex, MethodBase.GetCurrentMethod().Name + " Error During IDOCOperations " + ex.Message);
continue;
}
}
}
}
}

Answer 1

乍一看，不同的操作似乎可以分组，然后在一个步骤中执行。

例如：

• 创建一个必须使用item.IsRead = true; item.ModifiedDate = DateTime.Now;更新的所有项目的列表，并在一步中一起执行所有内容
• 以同样的方式，创建一个包含要删除的所有订单的列表，然后在最后的一个步骤中执行

我不知道这是否适用于上下文和/或应用程序，这只是的一个想法

Answer 2

首先，检查全局和属性级别的Lazy Load功能配置。

当您在sapOrders中执行ToList((时，您正在执行查询(并将结果加载到内存中(，当您获得Orders、SalesOrganizations等时，您可能正在执行辅助查询…

看看这篇文章来改进你的循环。

另一方面，您可以在循环中使用异步查询和并行编程，因此，对于其中的每个非依赖任务，您可以创建一个异步task方法并同时运行所有这些方法。

这里和这里你有一些文章，我希望能帮助你。

祝你好运！

Answer 3

如果你可以避免在循环中调用.SaveChanges()，而是在最后调用，你会过得更好，因为你可以避免多次往返于DB。也就是说，如果你在循环50000多个项目，你可能会想在某种程度上批量处理，也许每1000个你称之为

不幸的是，您需要在保存时创建订单的结果才能存储在另一列中，这很复杂。也许，如果您跟踪正在创建的订单，那么当您进行批量保存时，您可以在完成后立即为每个刚刚创建的订单和另一个.BulkSaveChanges()执行一组SASNo？

对于您以后的保存更改(删除订单详细信息、保存、删除订单、保存(，我认为没有必要分多个步骤进行，但也许我的EF已经过时了，它会抱怨的。理想情况下，我会删除所有对.SaveChanges()的调用，并在每1000次的批量操作中这样做。

假设BulkSaveChanges能够处理所有这些，那么上述操作将显著减少DB网络调用的数量。基本上，我的目标是低于预期，但最终，如果没有EF，这可能会做得更好/更快。

using (var context = new BbsfDbContext())
{
var sapOrders = ...;
var ordersCreated = new List<..>(); // might wanna initialized this with a size if you have a rough gauge on what % will need creation of loop
//if (sapOrders.Any()) // not needed
//{
foreach (var item in sapOrders.Select((x, index) => new { x, index }))
{
try
{
var order = ...;
var isExist = ...;
if (isExist)
{
// ...
if (order == null)
{
order = new Order { ... };
var savedOrder = context.Orders.Add(order);
//context.SaveChanges();
//order.SASNo = BbsfConsts.KeasOrderNumberPrefix + savedOrder.Id;
ordersCreated.Add(order);
}
else
{
// Do updates
// ...
}
}
else
{
//if (order != null) // shouldn't need this
//{
var orderDetails = context.OrderDetails.Where(p => p.OrderId == order.Id).ToList();
orderDetails?.ForEach(p => context.OrderDetails.Remove(p));
//context.SaveChanges();
context.Orders.Remove(order);
//context.SaveChanges();
//}
}
// ...
if (index % 1000 == 0)
{
context.BulkSaveChanges(); // bulk save of 1000 loops of changes
foreach (var orderCreated in ordersCreated)
{
orderCreated.SASNo = BbsfConsts.KeasOrderNumberPrefix + savedOrder.Id;
}
context.BulkSaveChanges(); // bulk save of x num of SASNo sets
}
}
catch (Exception ex)
{
// ...
}
}
}
}
}