base_tier_validation_formula: Performance issue when changing python expression

[tylersampson]

Is your feature request related to a problem?

We encountered the following issue on a "large" database with about 30,000 invoices (account_move) with 2 tier reviews per invoice:

After trying to update the "Review expression" on a tier definition, the page would remain loading until finally failing with Odoo error "cpu time reached".

Describe the solution you'd like

Looking at the tier.review model, there is an api.depends on definition_id.review_expression for _compute_python_reviewer_ids which results in looping through all related tier.review (past and current) to update the reviewer ids.

I think this room for improvement form scalability perspective, but perhaps business logic bug? We shouldn't update already completed reviews.

Only update in progress tier.reviews by adding additional check:

@api.depends("definition_id.reviewer_expression", "review_type", "model", "res_id")
    def _compute_python_reviewer_ids(self):
        for rec in self:
            if rec.status in ['rejected','approved']: # To stop unnecessary calculation
                continue
            if rec.review_type != "expression":
                rec.python_reviewer_ids = self.env["res.users"].browse()
                continue

Describe alternatives you've considered

Of course, one can update the server config to increase the limits but I don't think this is a future-proof solution.

For now, we modified the tier definition directly in the database. This makes it so it only applies to future reviews created / updated.

Additional context I'm happy to contribute to this if it makes sense.