create of account.move is relatively slow because of lots of repeated SQL queries

[Colin Newell]

Creating an account.move appears to perform a lot of repeated queries. A lot
of these appear to be tied to things like validation and actually cause lots of
repeated queries. By taking a look at the call within a profiler there appear
to be 3 areas where a lot of time is taken,

addons/account/account_invoice.py

_get_invoice_from_line

addons/account/account_move_line.py

_get_move_lines
browse <- gets called a lot, especially by the validation in the _constraints.
Each individual constraint calls browse to reload the same data.

By making some tweaks to reduce the number of queries in those 3 areas I was
able to reduce the time spent in the call on a test server running in debug
mode from something like 0.25s to 0.1s on average. This looks significant
enough that it looks like it is worth optimising. This is just looking to
remove repeated queries that bring back identical results. While I have
made changes locally to get these performance numbers, I am not sure whether
my changes are the correct action to take. I have simply cached the results of
certain functions to prove that if the query is only generated the once then
the system runs faster. I haven't attempted to prove that the caching is safe.

The correct fix for these issues may be changes to the code. The _constraints
and the store properties on the relationship fields appear to be particularly problematic
in terms of performance as they generate a lot of repeated reads.

Attached are some debug logs from running with and without those areas tweaked
to prevent repeated queries to demonstrate the difference.

I have specifically been testing this with OpenERP 6.1 but the code for 7.0 does not
look massively different, so it probably does the same thing.

[Olivier Dony (Odoo) (odo-openerp)]

I have reproduced this behavior on the customer's database and will be investigating it soon, but there is no immediate fix, as you can guess. The number of queries stems from the high level OpenERP model, which can of course lead to many suboptimal SQL queries when everything resolves at the low level. Adding a cache is a dangerous workaround (every cache comes with tricky invalidation issues), but there may be other solutions to reduce the number of queries here.

Thanks,