TicketSystem.eventually_restrict_owner executes 10,000 identical SQL queries
|Reported by:||Owned by:|
We are running trac at dev.laptop.org with a database that goes back several years, with over 10,000 tickets.
Opening a ticket page takes 19 seconds. Its an ancient trac install (we'll upgrade soon) but this is reproducible when loading the same database into trac-0.12.3 with no plugins.
I have profiled the page load operation and found that the entirity of this delay is spent in TicketSystem.eventually_restrict_owner because it fires thousands upon thousands of SQL queries. I guess it is building the list of owners that a ticket could potentially be reassigned to.
First, this line:
for user in PermissionSystem(self.env) \ .get_users_with_permission('TICKET_MODIFY'):
This leads to DefaultPermissionStore.get_users_with_permission() which generates a list of *all* known users, calling get_user_permissions() on each one. get_user_permissions() runs the same SQL query on each user. We have 5480 users returned by env.get_known_users(). So it spends quite a few seconds executing an identical SQL query 5480 times.
Then we arrive at:
if not ticket or \ 'TICKET_MODIFY' in PermissionCache(self.env, user, ticket.resource):
So we go back into perm.py has_permission → check_permission → get_user_permissions and execute several thousand more identical SQL queries.
I'm sure our user list can be cleaned up, but this somewhat-extreme data set exposes a significant inefficiency here.
Could this code be restructured/rethought with these points in mind?