LINQ groupby with custom IEqualityComparer + combined properties - performance problems

Question

I have a List of data that's combined from a entity framework database query with another IEnumerable of the same type with in memory data from other sources. For some of our clients this list amounts to about 200000 entries (about half from the db), which makes the grouping operating take extremely long (up to 30 minutes on our cheap virtual Windows server).

The grouping operation turns the list down to about 10000 objects (so about 20:1).

The data class of the List is basically just a big row of Strings and Ints and a few other basic types:

public class ExportData
{
  public string FirstProperty;
  public string StringProperty;
  public string String1;
  ...
  public string String27;
  public int Int1;
  ...
  public int Int15;
  public decimal Mass;
  ...
}

The grouping is done through a custom IEqualityComparer that basically amounts to this:

1. If items are allowed to be grouped by the custom logic, that means about half of the properties of both objects are equal, and those are the only properties we care about from this point on, besides ID, Mass and a special StringProperty which can still be different even if the items are allowed to be grouped.
2. Each new grouped object should have the relevant properties (that were the same in step 1), plus the combined IDs from the grouped items as a string and the Sum of all the Mass (decimal) properties of the grouped items, and the special StringProperty should be set depending on if a special string occurs in any of the grouped items or not.

List<ExportData> exportData; // in memory list of combined data from database + memory data

exportData = exportData.GroupBy(w => w, new ExportCompare(data)).Select(g =>
{
  ExportData group = g.Key;
  group.Mass = g.Sum(s => s.Mass);

  if (g.Count() > 1)
  {
    group.CombinedIds = string.Join("-", g.Select(a => a.Id.ToString()));
  }

  if (g.Any(s => s.StringProperty.Equals("AB"))) 
  {
    group.StringProperty= "AB";
  }
  else if (g.Any(s => s.StringProperty.Equals("CD"))) 
  {
    group.StringProperty= "CD";
  }
  else
  {
    group.StringProperty= "EF";
  }

  return group;
}).ToList();

And the custom comparer for completeness:

public class ExportComparer : IequalityComparer<ExportData>
{
  private CompareData data;

  public ExportComparer()
  {
  }
  public ExportComparer(CompareData comparedata)
  {
    // Additional data needed for comparison logic
    // prefetched from another database
    data = comparedata;
  }
  public bool Equals(ExportData x, ExportData y)
  {
    if (ReferenceEquals(x, y)) return true;

    if (ReferenceEquals(x, null) || ReferenceEquals(y, null)) return false;

    (...) // Rest of the unit-tested and already optimized very long comparison logic
    return equality; // result from the custom comparison
  }

  public int GetHashCode(ExportData obj)
  {
    if (ReferenceEquals(obj, null)) return 0;

    int hash = 17;

    hash = hash * 23 + obj.FirstProperty.GetHashCode();
    (...) // repeated for each property used in the comparison logic
    return hash;

What can I do to make this groupby run faster?

Answer 1

It's hard to suggest optimization for comparer, since its code is not shown, but there is an optimization for Select clause.

Right now you are using Sum, Count, Select, Any (2 times) inside that select. That means elements in each group are evaluated 5 times (from them at least 3 times completely). Instead you can use a foreach loop, once, and evaluate your conditions yourself:

exportData.GroupBy(w => w, new ExportCompare(data)).Select(g =>
{                
    ExportData group = g.Key;
    decimal mass = 0m;
    var ids = new List<int>();
    bool anyAb = false;
    bool anyCd = false;
    // only one loop
    foreach (var item in g) {
        mass += item.Mass;
        ids.Add(item.Id);
        anyAb = anyAb || item.StringProperty.Equals("AB");
        anyCd = anyCd || item.StringProperty.Equals("CD");
    }
    group.Mass = mass;
    if (ids.Count > 1) {
        group.CombinedIds = string.Join("-", ids);
    }
    if (anyAb)
        group.StringProperty = "AB";
    else if (anyCd)
        group.StringProperty = "CD";
    else
        group.StringProperty = "EF";

    return group;
}).ToList();

Now we loop over grouping just once, which should be more efficient that doing that 5 times.

Answer 2

g.Count() > 1 could be optimized to g.Any() because you don't really care for the count, you only care if there is at least one element.

Your Any/Any calls for AB or CD could be handled in one loop instead of two.

You might want to try to make a List or Array from your g but that's more of a guess, depending on what happens internally, and how your groups are built, that might do good or harm. You will need to test and profile.

However, I strongly suspect something else is fishy here, either your RAM is exhausted or there is need to optimize code you haven't shown. 30 minutes to do some memory work is insane.