C# Foreach on List is very slow-CodePudding

I have a foreach that goes through response, response is a list that can take up to 230,000 records, foreach response it will filter and sum a second list called _MaterialIssued, this list contains max 30,000 records, in the following if it will only enter if Issued is bigger than 0 which will occur only about 15% of the time, next it will try to get the itemOnhand from the _Onhand list that contains max 17,000 records, in the following if it will go in about 85% of the time, when I wrote the code inside this block is where the performance dropped dramatically, in this if I will go back and filter response for all the child items and loop through them changing the _onhand list and the response list!

To go through this foreach depends on the machine I use but takes from 45 to 75 minutes and I cannot find what line is my bottleneck or how I can improve performance for this code block.

foreach (var b in response)
{
    var Issued = _MaterialIssued.Where(x => x.ItemId == b.ItemId && x.Job == b.Job).Sum(c => c.Qty);

    if (Issued > 0)
    {
        var prctIssued = Issued / b.QtyDemand;
        var childItems = response.Where(x => x.Job == b.Job && x.BomPath.StartsWith(b.BomPath));

        foreach (var child in childItems)
        {
            child.QtyIssued = child.QtyDemand * prctIssued;
        }
    }
    var itemOnhand = _OnHand.Where(x => x.ItemId == b.ItemId).FirstOrDefault();

    if (itemOnhand.Onhand > 0)
    {
        decimal prctOnhand = 1;
        var childItems = response.Where(x => x.Job == b.Job && x.BomPath.StartsWith(b.BomPath) && x.ItemId != b.ItemId && x.SiteRef == b.SiteRef && x.QtyIssued < x.QtyDemand);
        var DemandWithIssued = b.QtyDemand - b.QtyIssued;
        if (itemOnhand.Onhand < DemandWithIssued)
        {
            prctOnhand = itemOnhand.Onhand / DemandWithIssued;
        }

        itemOnhand.Onhand -= DemandWithIssued * prctOnhand;
        foreach (var child in childItems)
        {
            child.QtyParentAvailable = (child.QtyDemand - child.QtyIssued) * prctOnhand;
        }
    }
}

The model for _OnHand is

private class ItemOnhand
{
    public string ItemId { get; set; }
    public string SiteRef { get; set; }
    public decimal Onhand { get; set; }
}

The model for _MaterialIssued is

public class FiniteDemandBase
{
    public string ItemId { get; set; }
    public string Item { get; set; }
    public string JobId { get; set; }
    public string Job { get; set; }
    public DateTime StartDate { get; set; }
    public string SiteRef { get; set; }
    public decimal Qty { get; set; }
}

The model for response is

public class FiniteDemand
{
    public int Id { get; set; }
    public DateTime StartDate { get; set; }
    public string BomPath { get; set; }
    public string SiteRef { get; set; }
    public string Job { get; set; }
    public string ItemId { get; set; }
    public string JobId { get; set; }
    public string ParentItemId { get; set; }
    public decimal QtyPerUnit { get; set; }
    public decimal QtyDemand { get; set; }
    public decimal QtyOnhand { get; set; }
    public decimal QtyWip { get; set; }
    public decimal QtyOnhandRunning { get; set; }
    public decimal QtyInSchedule { get; set; }
    public decimal QtyIssued { get; set; }
    public decimal QtyParentAvailable { get; set; }
    public decimal QtyDemandNeeded { get; set; }
    public decimal QtyDemandNeededRunning { get; set; }
}

I tried changing the _OnHand list to a HashSet but the performance was the same.

CodePudding user response：

Let's break down your code into parts which contain an explicit of implicit iteration:

Iteration over response — n-times:

foreach (var b in response)
{

Nested iteration over _MaterialIssued — m-times:

    var Issued = _MaterialIssued.Where(x => x.ItemId == b.ItemId && x.Job == b.Job).Sum(c => c.Qty);

Nested iteration over response (again) — n-times:

        var childItems = response.Where(x => x.Job == b.Job && x.BomPath.StartsWith(b.BomPath));

Nested iteration over _OnHand — up to h-times:

    var itemOnhand = _OnHand.Where(x => x.ItemId == b.ItemId).FirstOrDefault();

Nested iteration over response (again) — n-times:

        var childItems = response.Where(x => x.Job == b.Job && x.BomPath.StartsWith(b.BomPath) && x.ItemId != b.ItemId && x.SiteRef == b.SiteRef && x.QtyIssued < x.QtyDemand);

Hence, the complexity of your algorithm is: O(n * (m n h n)) = O(2*n^2 n*(m h))!

If the number of responses is 230,000, we are speaking about 52.9 billion iterations! No wonder it runs for ages :)

CodePudding user response：

This sounds an awful lot like you're trying to manage a database. I would suggest you go all the way and do exactly that, and look into using something like MySQL instead.

E.g.:

Create a database and add tables in Visual Studio

Create a SQL Server Database programmatically by using ADO.NET and Visual C# .NET

Whilst, technically, a List can contain up to two billion elements, once you're above a few thousand you'll start to run into trouble. They aren't really meant for the sort of heavy data processing you're attempting. That is exactly what database engines are for.