TicketSystem.eventually_restrict_owner executes 10,000 identical SQL queries

[dsd@…]

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?

[Remy Blank]

Yes, we know (#4245). Unfortunately, nobody has stepped up to do the work up to now.

Your best bet at the moment is to set [ticket] restrict_owner = false.