Slow speed when using nested loops when comparing large data sets

Question

I'm retrieving two lists of datasets using Linq to entities. They're both in the same Database, but I need to get one table converted into my Tasks Table because it's integrated into my Calendar. Not worth going into extreme detail here I'm sure but I would love to speed up the process of matching id's and Creating new Task objects. This is a Once and done snippet so even at a slow speed I can simply leave the program running overnight. However, for future reference I'd like some suggestions on increasing the efficiency.

var accounts = data.Accounts.ToList().OrderBy(a => a.ID);
Incidents[] Incidents = data.Incidents.ToArray();

        for (int i=0;i<Incidents.Length;i++)
        {
            foreach (var a in accounts)
            {
                if (a.Acct_CID == Incidents[i].CustomerID)
                {
                    Tasks t = new Tasks();
                    t.creator_id = a.ID;
                    t.start_date = Incidents[i].DateOpened;
                    t.end_date = Incidents[i].DateCLosed;
                    t.product_code = Incidents[i].ProductCode;
                    t.install_type = Incidents[i].InstallType;
                    t.os = Incidents[i].OSType;
                    t.details = Incidents[i].Description;
                    t.solution = Incidents[i].Solution;
                    t.creator_name = Incidents[i].TechID;
                    t.category = Incidents[i].Title;
                    t.text = "Ticket for" + " " + Incidents[i].Name;
                    if (t.end_date == DateTime.MinValue || t.end_date == null)
                        t.status_id = 6;
                    else t.status_id = 7;
                    data.Tasks.Add(t);
                    break;
                }
            }
        }
        data.SaveChanges();

Answer 1

Why not to join tables and create tasks on the fly?

var tasks = from i in data.Incidents
            join a in data.Accounts on i.CustomerID equals a.Acct_CID
            select new Tasks()
            {
                creator_id = a.ID,
                start_date = i.DateOpened,
                end_date = i.DateCLosed
                // ...
            };

BTW I don't think ordering makes sense here thus it should not matter in which order you are adding created tasks to database.

// Query will not be executed until here
foreach(var task in tasks)
   data.Tasks.Add(task);
data.SaveChanges();

Answer 2

I would Join the result on DB

var joinedResult = data.Accounts.Join(data.Incidents, 
                                      a => a.Acct_CID, 
                                      i => i.CustomerID, 
                                      (a, i) => new { Account = a, Incident = i });

foreach (var item in joinedResult)
{
    Tasks t = new Tasks();
    t.creator_id = item.Account.ID;
    t.start_date = item.Incident.DateOpened;
    ........

}

Answer 3

Replace this line

var accounts = data.Accounts.ToList().OrderBy(a => a.ID);

with this

var accounts = data.Accounts.OrderBy(a => a.ID).ToList();

That will let the database do the sort, then cache the sorted results. What you have now pulls in everything, then sorts them each time you reach the foreach loop (accounts gets enumerated all over again).

I can't say it'll make a huge improvement, but if your data set is large enough, re-sorting a large list many times will certainly slow you down.

---

On second glance, you're not only sorting accounts every time, but you seem to be looking for only a small subset of the records, yet you're iterating over the whole array. Consider replacing

    foreach (var a in accounts)
        {
            if (a.Acct_CID == Incidents[i].CustomerID)
            {

with

      foreach (var a in accounts.Where(acct => acct.Acct_CID == Incidents[i].CustomerID))
      {

Answer 4

Create a lookup of the accounts

var accountsLookup = data.Accounts.ToLookup(a => a.Acct_CID);
foreach (var incident in data.Incidents)
{
    foreach (var a in accountsLookup[incident.CustomerID])
    {
        Tasks t = new Tasks();
        t.creator_id = a.ID;
        ...
    }
}
data.SaveChanges();

---

If the accounts are unique you could also create a dictionary

var accountsDict = data.Accounts.ToDictionary(a => a.Acct_CID);
foreach (var incident in data.Incidents)
{
    Account a;
    if (accountsDict.TryGetValue(incident.CustomerID, out a)
    {
        Tasks t = new Tasks();
        t.creator_id = a.ID;
        ...
    }
}
data.SaveChanges();

This would be faster than the first variant. Note that dictionaries have a constant lookup time that does not depend on its size. Therefore you basically get an O(n) execution time for the loop. Your original implementation has an O(n^2) execution time.

Answer 5

    var tasks = (from i in data.Incidents
                     join a in data.Accounts on i.CustomerID equals a.Acct_CID
                     select new
                     {
                         creator_id = a.ID,
                         start_date = i.DateOpened,
                         end_date = i.DateCLosed,
                         product_code = i.ProductCode,
                         install_type = i.InstallType,
                         os = i.OSType,
                         details = i.Description,
                         solution = i.Solution,
                         creator_name = i.TechID,
                         category = i.Title,
                         text = "Ticket for" + " " + i.Name,
                         status_id = 7
                     }).AsEnumerable().Select(x => new
                         {
                             x.creator_id,
                             x.start_date,
                             x.end_date,
                             x.product_code,
                             x.os,
                             x.details,
                             x.solution,
                             x.creator_name,
                             x.category,
                             x.text,
                             x.install_type,
                             x.status_id
                         });


        foreach (var item in tasks)
        {
            Tasks t = new Tasks();
            t.os = item.os;
            t.id = item.creator_id;
            t.install_type = item.install_type;
            t.start_date = item.start_date;
            t.end_date = item.end_date;
            t.solution = item.solution;
            t.details = item.details;
            t.creator_name = item.creator_name;
            t.category = item.category;
            t.text = item.text;
            t.product_code = item.product_code;
             if (t.end_date == DateTime.MinValue || t.end_date == null)
                 t.status_id = 6;
             else t.status_id = 7;
             data.Tasks.Add(t);
        }
        data.SaveChanges();