Performance improvement on bank statement validation

Hello Frederic,

I suggest you network with Noviat, because I believe they already addresses
some of the slowness issues for eBanking in
their account_bank_statement_extensions module:
http://bazaar.launchpad.net/~noviat/openobject-addons/extra-6.1/files/head:/account_bank_statement_extensions/

Also, I remind you that this module is a dependency of the most advanced
cashflow framework around that we are also extending and putting in
production here, so in any case, that would be a great task to brainstorm
toward a minimal common denominator for bank statements. I'm available if
you want to discuss that. But may be part of what they did regarding
performance and edition of large bank statements (per line states) can be
integrated in the core or else promoted as a common OCB module.

Best regards.

On Mon, Sep 2, 2013 at 12:30 PM, Frederic Clementi - Camptocamp.com <
<email address hidden>> wrote:

> Public bug reported:
>
> Hello,
>
> Our customer need to validate bank statement with 15'000 lignes on
> average . Today they slice the bank statement into 1000 lignes and, even
> though, It takes hours to validate it.
>
> Same probleme to cancel a bank statement.
>
> This point becomes more and more critical for us especially in
> e-commerce business.
>
> Can you propose improvement please ?
>
>
> Frederic Clementi
> Camptocamp
>
> ** Affects: openobject-addons
> Importance: Undecided
> Status: New
>
>
> ** Tags: maintenance
>
> ** Tags added: maintenance
>
> --
> You received this bug notification because you are subscribed to OpenERP
> Addons.
> https://bugs.launchpad.net/bugs/1219881
>
> Title:
> Performance improvement on bank statement validation
>
> Status in OpenERP Addons (modules):
> New
>
> Bug description:
> Hello,
>
> Our customer need to validate bank statement with 15'000 lignes on
> average . Today they slice the bank statement into 1000 lignes and,
> even though, It takes hours to validate it.
>
> Same probleme to cancel a bank statement.
>
> This point becomes more and more critical for us especially in
> e-commerce business.
>
> Can you propose improvement please ?
>
>
> Frederic Clementi
> Camptocamp
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/openobject-addons/+bug/1219881/+subscriptions
>

I think many performance problems are based on o2m inserts/updates (create/write)

a create/write off a line causes function fields in the "one" record to be recalculated multiple times

example

             moves = []
             for line in cr.dictfetchall():
                    line['name'] = 'neutral-'+ move['date'],
                    #self.create_move(cr, uid, line, vals, context ) #<---- very time consuming
                    moves.append(self.create_move(cr, uid, line, vals, context ))
             move_obj.write(cr, uid, [move_id], {'line_id': moves}, c ) #<-- much faster

may be other approaches are better

Dear Raphaël,

I think Novita doesn't handle anything regarding performance as the problem comes from the account.move validation, not the bank statement object. By the way (only had a look 2 minutes, I may be wrong) I think they override the confirm bank statement line by using the write "confirm" directly rather than calling the method => This will probably break the compatibility with banking addons (both ours and Stefan one).

So we'll not solve that one this way. We must:

1) Optimize the account.move confirmation in the core of OpenERP (touching probably both bank.statement and account.move.line object)

2) Converge together (with you, Stefan and us) in a base banking module common to everyone.

For #2 there is already some work in progress with Stephane Bidoul If I remember well..

Regards,

Joël

A potential source for this is the use of properties. AFAIR, there are a number of property access in a bank statement confirmation, and these are dreadfully slow.

@Joel,

Well Luc de Meyer told me Noviat had customers with 2000+ bank statement
lines (it's for this reason they have a per line state), so eventually the
performance boost isn't in this module, but I think they probably faced a
fixed a few issues and it could be worth asking them. Indeed in that code,
the performance thing for them was to bypass the normal write under certain
circumstances, and this is hacky for sure.

@Alexandre,

generally speaking, in the future, I would like to see properties to be all
handled by SQL without roundtrips between Python and Postgres. What do you
think? Syleam made some code at least for the reading part:
https://github.com/syleam/openerp-pdi-connector/tree/master/pdi_connector/sql
May
be worth repackaging or even including some of that in the core?

@all

as for m2o insertion/write triggering slow re-computation of the parent
container object (the bank statement here), whil I don't know if that's the
bottleneck here specifically, I confirm what Ferdinand says that this is
one one of the topmost PITA of the Openobject framework today. We have a
patch taking advantage of the awkward batch write API that trick orm.py
using a context key to ensure you can write multiple o2m records (using one
API call) and that function fields will only be computed once at the end.
Not sure this the thing that slows the most here. But for instance using
that patch, recomputing a 200 lines sale order after applying a new
pricelist went from 20+ minutes to 15 seconds. I'll try to post the the
patch somewhere later in the day. Could be useful in any case.

Good luck on that one.

On Tue, Sep 3, 2013 at 8:52 AM, Alexandre Fayolle - camptocamp <
<email address hidden>> wrote:

> A potential source for this is the use of properties. AFAIR, there are a
> number of property access in a bank statement confirmation, and these
> are dreadfully slow.
>
> --
> You received this bug notification because you are subscribed to OpenERP
> Addons.
> https://bugs.launchpad.net/bugs/1219881
>
> Title:
> Performance improvement on bank statement validation
>
> Status in OpenERP Addons (modules):
> New
>
> Bug description:
> Hello,
>
> Our customer need to validate bank statement with 15'000 lignes on
> average . Today they slice the bank statement into 1000 lignes and,
> even though, It takes hours to validate it.
>
> Same probleme to cancel a bank statement.
>
> This point becomes more and more critical for us especially in
> e-commerce business.
>
> Can you propose improvement please ?
>
>
> Frederic Clementi
> Camptocamp
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/openobject-addons/+bug/1219881/+subscriptions
>