launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/gAT-opti into lp:launchpad.

Commit message:
Optimise Person.getAdministratedTeams from ~500ms to ~10ms.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/gAT-opti/+merge/263053

This branch rewrites the Person.getAdministratedTeams() query from 500ms to 10ms in a case like me (21997).

The old plan query ended up finding all ADMIN TeamMemberships ever, then filtering them based on TeamParticipation and other madness like that. The new version uses a CTE to precalculate relevant TeamParticipations, forcing the query to go in the direction we want.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/gAT-opti into lp:launchpad.

=== modified file 'lib/lp/registry/model/person.py'
--- lib/lp/registry/model/person.py	2015-04-19 12:56:32 +0000
+++ lib/lp/registry/model/person.py	2015-06-26 02:16:36 +0000
@@ -1750,22 +1750,33 @@
 
     def getAdministratedTeams(self):
         """See `IPerson`."""
-        owner_of_teams = Person.select('''
-            Person.teamowner = TeamParticipation.team
-            AND TeamParticipation.person = %s
-            AND Person.merged IS NULL
-            ''' % sqlvalues(self),
-            clauseTables=['TeamParticipation'])
-        admin_of_teams = Person.select('''
-            Person.id = TeamMembership.team
-            AND TeamMembership.status = %(admin)s
-            AND TeamMembership.person = TeamParticipation.team
-            AND TeamParticipation.person = %(person)s
-            AND Person.merged IS NULL
-            ''' % sqlvalues(person=self, admin=TeamMembershipStatus.ADMIN),
-            clauseTables=['TeamParticipation', 'TeamMembership'])
-        return admin_of_teams.union(
-            owner_of_teams, orderBy=self._sortingColumnsForSetOperations)
+        class RestrictedParticipation:
+            __storm_table__ = 'RestrictedParticipation'
+            teamID = Int(primary=True, name='team')
+
+        restricted_participation_cte = With(
+            'RestrictedParticipation',
+            Select(
+                TeamParticipation.teamID, tables=[TeamParticipation],
+                where=TeamParticipation.person == self))
+        team_select = Select(
+            RestrictedParticipation.teamID, tables=[RestrictedParticipation])
+
+        return Store.of(self).with_(restricted_participation_cte).find(
+            Person,
+            Person.id.is_in(
+                Union(
+                    Select(
+                        Person.id, tables=[Person],
+                        where=Person.teamownerID.is_in(team_select)),
+                    Select(
+                        TeamMembership.teamID, tables=[TeamMembership],
+                        where=And(
+                            TeamMembership.status ==
+                                TeamMembershipStatus.ADMIN,
+                            TeamMembership.personID.is_in(team_select))))),
+            Person.merged == None).order_by(
+                self._sortingColumnsForSetOperations)
 
     def getDirectAdministrators(self):
         """See `IPerson`."""