Slow process to update 14 million rows

BL18 0
Viewed 90

I need to update the values and location/other information for 14 million records. I have the 14 million records in interval_list. Each iteration I use the MeterID from the Interval object to get the correct location and multiplier. I than update the location and update the value based on the multiplier. This code takes 4+ hours to run. Is there a better way to do this? Thanks.

foreach (Interval interval in interval_list)
{       
    var result = from m in meterList
                 where m.MeterID.Equals(interval.MeterID)
                 where m.StartDate < (interval.UTCDateTime.ToLocalTime())
                 where m.FinalDate > (interval.UTCDateTime.ToLocalTime())
                 select m;

    if (result.Count() == 1) //Ignore if > 1
    {
        int mult1 = result.ElementAt(0).Mult1;
        int mult2 = result.ElementAt(0).Mult2;
        interval.ServiceID = result.ElementAt(0).Locserv;
                                       
        // With updating check to see if value is already adjusted by mult

        if (interval.Mult1 > 1)
        {
            interval.Value = interval.Value / interval.Mult1;
            interval.Value = interval.Value * mult1;
        }

        interval.Mult1 = mult1; //MULT1
        interval.Mult2 = mult2; //MULT2
    }
}

public class Interval
{
    public string MeterID { get; set; }
    public DateTime UTCDateTime { get; set; }
    public float Value { get; set; }
    public DateTime LocalDateTime { get; set; }
    public string ServiceID { get; set; }
    public string Account { get; set; }
    public string Rate { get; set; }
    public int Mult1 { get; set; }
    public int Mult2 { get; set; }
}

public class Meters
    {
        public string MeterID { get; set; }
        public string Locserv { get; set; }
        public DateTime StartDate { get; set; }
        public DateTime FinalDate { get; set; }
        public int Mult1 { get; set; }
        public int Mult2 { get; set; }

    }
performance linq list c#
2 Answers

You have to look at what you can do with this linq select:

    var result = from m in meterList
             where m.MeterID.Equals(interval.MeterID)
             where m.StartDate < (interval.UTCDateTime.ToLocalTime())
             where m.FinalDate > (interval.UTCDateTime.ToLocalTime())
             select m;

As I said before the result.Count() will check ALL items in meterList. That means that each Count() checks 22,000 items (with three where checks for each) and this is done 14 million times. Which totals in each item in meterList is checked 308,000,000,000 times. And there are also three where in that linq query, why not just use &&? I don't know much about the internals of linq queries, but if that is not optimized away internally it looks like it creates three state machines that handles the query.

I would have a look at optimizing meterList. Before the 14 million loop, I would turn it into a Dictionary<MeterId, List<MeterItem>>. That way you will only get the items for that MeterId almost instantly. And also the list to check for StartDate/EndDate are reduced.

If the intersection of MeterId in meterList and interval_list is significantly smaller than the full lists, you could filter the lists to only contain the MeterIds that will make a hit in the result. But I do not think that will make such a big difference compared to the idea above.

Also the Count() should not keep counting if it finds more than one record. That could make a big difference depending on what the data looks like.

But without knowing more about what the data looks like, there is a risk that none of these ideas helps much at all.

emilsteen0

LINQ/Entity Framework is very poor at bulk operations. You would be much better off using a bare SQL query to do this.

Guessing from your existing code, it might look like this

UPDATE il
SET
-- alternatively use SELECT
    ServiceID = m.Locserv,
    Value = CASE WHEN i.Mult1 > 1
        THEN (i.Value / i.Mult1) * m.Mult1
        ELSE i.Value
        END
    Mult1 = m.Mult1,
    Mult2 = m.Mult2
FROM interval_list i
CROSS APPLY (
    SELECT
      Mult1 = MIN(m.Mult1),
      Mult2 = MIN(m.Mult2),
      Locserv = MIN(m.Locserv)
    FROM meterList m
    WHERE m.MeterID = i.MeterID
      AND m.StartDate < GETDATE()
      AND m.FinalDate > GETDATE()
    HAVING COUNT(*) = 1
) m;
Charlieface0
Related