Code review comment for lp:~jcsackett/launchpad/plus-participation-additional-fixes

On Wed, Aug 18, 2010 at 1:01 AM, j.c.sackett
<email address hidden> wrote:
> The findPathToTeam calculations were a problem b/c in the cases causing the timeout, the query gets called hundreds of times, leading to the largest set of time in the process. Because of the iteration through an unknown series of team relations, there's no easy way to predict how this will work.
>
> As an example, in https://lp-oops.canonical.com/oops.py/?oopsid=OOPS-1688ED2377 you can see the query used in findPathToTeam being called 261 times, for a total time of 4610. The other +participation OOPS reports show the same thing.

Ok, so theres basic reason for it to be slow.

Have you considered this algorithm (psuedo code):
# 1 query, we want all the teams he participates in
# (that is, all rows that have the member == user, and the team == anything
# should return a list of tuples [(user, t1), (user, t2)] or something like that
memberships = list(get_memberships(person==user))
teams = [team for _, team in memberships]
# Get all the memberships of those teams themselves; because memberships
# are transitive, this will be a subset of the first result, and lets us infer
# a valid graph cheaply (there can be multiple paths to a team, we only
# need to show one)
team_temberships = get_memberships(person in teams)
graph_edges = list(team_memberships)
graph = dict(graph_edges)
graph.update(memberships)
# now render
...