Horrible performance with thousands of nodes

We ran into a massive performance wall causing timeouts and otherwise long page loads. Our site has thousands of nodes in groups. When loading all user groups using og_subgroup_user_groups() in places such as the entityreference selection plugin and the "user groups and subgroups" view handler, the returned array of GIDs was massive. The reason is that they aren't GIDs at all, but rather all inherited child entity IDs the user has access to via groups.

Our group structure is two level. About 30 main level groups and each have 10 to 40 subgroups.

I debugged og_subgroups_children_load_multiple() which does most of the work for og_subgroup_user_groups(). I'm not sure if og_subgroups_children_load_multiple() even promises to only return groups or if it's supposed to return all inherited child entities, but it doesn't make sense for it to return all child entities to og_subgroup_user_groups().

In detail: og_subgroups_children_load_multiple() calls og_subgroups_get_associated_entities() and that's the main problem. It makes sense if we're talking about returning all child entities, but in other use cases such as when called from og_subgroup_user_groups(), we should only get groups. I mended this locally by writing another function called og_subgroups_get_associated_groups(). When calling that instead, og_subgroups_children_load_multiple() becomes very sensible and we are experiencing okay performance.

og_subgroups_get_associated_groups() looks like this:

/**
 * Get entities serving as groups that are associated with a group.
 *
 * @param $entity_type
 *   The entity type.
 * @param $entity
 *   The entity object or entity ID.
 *
 * @return
 *  An array with the entities' entity type as the key, and array - keyed by
 *  the OG membership ID and the Entity ID as the value. If nothing found,
 *  then an empty array.
 */
function og_subgroups_get_associated_groups($entity_type, $entity, $fields = array()) {
  $cache = &drupal_static(__FUNCTION__, array());
  if (is_object($entity)) {
    // Get the entity ID.
    list($id) = entity_extract_ids($entity_type, $entity);
  }
  else {
    $id = is_numeric($entity) ? $entity : 0;
  }
  if (isset($cache[$entity_type][$id])) {
    // Return cached values.
    return $cache[$entity_type][$id];
  }

  // Get all group bundles of this entity type in an array.
  $group_all_bundles = og_get_all_group_bundle();
  $group_bundles = isset($group_all_bundles[$entity_type]) ? array_keys($group_all_bundles[$entity_type]) : array();

  // Gather scheme information from the entity to be used in the query.
  $entity_info = entity_get_info($entity_type);
  $entity_table = $entity_info['base table'];
  $entity_key_id = $entity_info['entity keys']['id'];
  $entity_key_bundle = $entity_info['entity keys']['bundle'];

  $cache[$entity_type][$id] = array();
  $query = db_select('og_membership');
  $query->join($entity_table, 'entity', "etid = entity.{$entity_key_id}");
  $query->condition('entity_type', 'user', '!=')
  ->condition('group_type', $entity_type, '=')
  ->condition('gid', $id, '=')
  ->condition("entity.{$entity_key_bundle}", $group_bundles, 'IN');
  if ($fields || ($fields !== FALSE && $fields = variable_get('og_subgroups_default_fields_' . $entity_type, array()))) {
    $query->condition('field_name', $fields, 'IN');
  }
  $query->fields('og_membership', array('entity_type', 'id', 'etid', 'gid'));
  $query->fields('entity', array($entity_key_bundle));
  $result = $query->execute();
  if ($result) {
    // Get the group ID from the group membership.
    foreach ($result as $og_membership) {
      $cache[$entity_type][$id][$og_membership->entity_type][$og_membership->id] = $og_membership->etid;
    }
  }

  return $cache[$entity_type][$id];
}

Should we change og_subgroups_children_load_multiple() to accept a parameter such as $groups_only and call the right associated content helper function depending on its value, or can we outright change what og_subgroups_children_load_multiple() returns? I think it's too late to do the latter since some custom implementations - if not this module itself - may depend on all child entities rather than only child groups being returned. However, og_subgroup_user_groups() should definitely only return GIDs and not (possibly, for the admin at least) thousands of entity IDs.