CODEWITHSUNDEEP
performanceentity-frameworkoptimizationlinq-to-entities
JeremyJAlpha
JeremyJAlpha

Reputation: 53

Why is adding objects to my model so slow?

I have a SimID query that joins a given list of cell phone numbers with their respective IDs in my DB, if the number doesn't exist in the DB it returns 0 as an ID. If I watch my query while debugging, it executes perfectly and very quickly. However when I loop through the results of the query and create a new object and add it to my model it takes 3 minutes to create 458 new objects.

I am new to EF and Linq what am I doing wrong? How can I optimize this code to execute faster?

Any help would be greatly appreciated.

//Query to create virtual table of SimID and MSISDN joined on Cache.MSISDN
var SimID = (from ca_msisdn in Cache.MSISDN
             join db_simobj in ctx.Sims on ca_msisdn equals db_simobj.Msisdn into Holder
             from msisdnresult in Holder.DefaultIfEmpty()
             select new { MSISDN = ca_msisdn, ID = (msisdnresult == null || msisdnresult.SimId == 0 ? 0 : msisdnresult.SimId) });

//Loop through virtual tables and add new data to model
foreach (var ToUpdate in SimID)
{
    if (ToUpdate.ID == 0)
    {
        Console.WriteLine("We have found a new MSISDN: " + ToUpdate.MSISDN + " adding it to the model.");
        ctx.Sims.Add(new Sim { Msisdn = ToUpdate.MSISDN });
    }
}

//My Sim object
public partial class Sim
{
    public Sim()
    {
        this.CDR_Event = new HashSet<CDR_Event>();
    }

    public long SimId { get; set; }
    public long SimStatusId { get; set; }
    public Nullable<long> FitmentCentreId { get; set; }
    public string Serial { get; set; }
    public string Msisdn { get; set; }
    public string Puk { get; set; }
    public string Network { get; set; }
    public Nullable<System.DateTime> SimStatusDate { get; set; }
    public Nullable<System.DateTime> ActivationDate { get; set; }
    public Nullable<System.DateTime> ExpiryDate { get; set; }
    public Nullable<long> APNSimStatusId { get; set; }
    public Nullable<System.DateTime> APNActivated { get; set; }
    public Nullable<System.DateTime> APNConfirmed { get; set; }
    public string Svr { get; set; }

    public virtual ICollection<CDR_Event> CDR_Event { get; set; }
}

Upvotes: 0

Views: 33

Answers (2)

JeremyJAlpha
JeremyJAlpha

Reputation: 53

The answer to this question as IronMan84 alluded to; as documented here is to use .AddRange() and add everything all at once as a list outside the foreach instead of one by one inside the loop.

List<Sim> _SIMS = new List<Sim>();
foreach (var ToUpdate in SimID)
{
    if (ToUpdate.ID == 0)
    {
        Console.WriteLine("We have found a new MSISDN: " + ToUpdate.MSISDN + " adding it to the model.");
        _SIMS.Add(new Sim { Msisdn = ToUpdate.MSISDN} );
    }
}
ctx.Sims.AddRange(_SIMS);

Upvotes: 0

Corey Adler
Corey Adler

Reputation: 16149

Your main problem is that every time that you add to the Sims collection on your context you are adding to what the context's change tracker needs to keep track of, which can become a very expensive proposition memory-wise when you start having larger amounts of entities.

You can solve this in one of two ways:

1) Save the data in batches. That way the change tracker never has to keep track of a large amount of added entities.

2) If you don't want to save it right away, keep it as an in-memory list first, and then do #1 when you do go to save those records.

Upvotes: 1

Related Questions