[7.0] Impossible to import a csv file with 30000 account move lines

[Frederic Clementi - Camptocamp]

Hello,

I need to import accounting journal entries in mass (about 170 000 mvl over 10 months).

I want to use the standard OpenERP CSV import functionnalty but I face a error with only 30 000 mvl .

To reproduce :
- connect a runbot (build 19601 in my case)
- create a bas DB with only accounting and basic Chart of account installed
- In the settings, check t the box to allow csv import
- Go to Accounting / Journal Entries / Journal Entries and import the file attached to this bug report

-> Result : OpenERP is not even able to display the 10 first lines as usual. If you open the Chrome web browser console, you see the error 'Request entity too large'). See attached my printscreen.

I consider such a low capacity of import a bug since for it is not rare, especially for new implementation to have to import past accounting entries... and 30000 move lines is not much.

What is you position on this point please ?

Frederic Clementi
Camptocamp SA

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

The error that Frederic's got is actually the configuration of the nginx server which blocks upload of big files on runbot.

This is not the real issue. If I try to import the same file on a local OpenERP server, it takes a long time . (still running after 40 minutes)

[Frederic Clementi - Camptocamp (frederic-clementi)]

Guewen stopped the import after 1h20 min for 30 000 move lines.
So it is enough to prove the lack of performance I think and confirm the bug report.

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

I put a timer around the creation of the records (only the call to ir_model_data._update(), not taking into account the import stuff around. That means, in openerp.osv.orm at line 1373

                 with Timer() as t:
                     ids.append(ModelData._update(cr, uid, self._name,
                         current_module, record, mode=mode, xml_id=xid,
                         noupdate=noupdate, res_id=id, context=context))
                 _logger.debug('Create of 1 line took %.03f sec.' % t.interval)

Here is an excerpt of the debug lines I get:

2013-09-10 12:45:10,263 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.250 sec.
2013-09-10 12:45:10,704 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.210 sec.
2013-09-10 12:45:11,057 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.200 sec.
2013-09-10 12:45:11,491 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.210 sec.
2013-09-10 12:45:11,969 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.250 sec.
2013-09-10 12:45:12,458 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.250 sec.
2013-09-10 12:45:12,782 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.160 sec.
2013-09-10 12:45:13,187 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.200 sec.
2013-09-10 12:45:13,651 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.240 sec.
2013-09-10 12:45:14,131 9518 DEBUG openerp_70 openerp.osv.orm: Create of 1 line took 0.250 sec.

For an average of 0.220 seconds per line, it will take nearly 2 hours to complete the import of 30'000 lines.

The creation of move lines is very slow.

[Xavier (Open ERP) (xmo-deactivatedaccount)]

> only the call to ir_model_data._update(), not taking into account the import stuff around

The import munging and analysis takes very little time, it's always the creation of complex objects in openerp which takes CPU time.

There's a test case in the server which imports ~1000 contacts (calling Model.load directly, not going through the import parsing and munging) and takes 25s on my machine, 24.6s being in res.partner's create or one of its callees. The problem is of course that openerp's object creation can be arbitrarily complex, and for some objects it does a lot of work checking stuff around or creating related and sub-objects, the import process itself can't do much about that.

[Guewen Baconnier @ Camptocamp (gbaconnier-c2c)]

Yes, that's why I specifically measured the ORM part and ignored the import
part.

The problem here is of course in the creation / write of move lines.
Le 10 sept. 2013 15:30, "Xavier (Open ERP)" <email address hidden> a
écrit :

> > only the call to ir_model_data._update(), not taking into account the
> import stuff around
>
> The import munging and analysis takes very little time, it's always the
> creation of complex objects in openerp which takes CPU time.
>
> There's a test case in the server which imports ~1000 contacts (calling
> Model.load directly, not going through the import parsing and munging)
> and takes 25s on my machine, 24.6s being in res.partner's create or one
> of its callees. The problem is of course that openerp's object creation
> can be arbitrarily complex, and for some objects it does a lot of work
> checking stuff around or creating related and sub-objects, the import
> process itself can't do much about that.
>
> --
> You received this bug notification because you are subscribed to OpenERP
> Project Group.
> https://bugs.launchpad.net/bugs/1223243
>
> Title:
> [7.0] Impossible to import a csv file with 30000 account move lines
>
> Status in OpenERP Server:
> New
>
> Bug description:
> Hello,
>
> I need to import accounting journal entries in mass (about 170 000 mvl
> over 10 months).
>
> I want to use the standard OpenERP CSV import functionnalty but I face
> a error with only 30 000 mvl .
>
> To reproduce :
> - connect a runbot (build 19601 in my case)
> - create a bas DB with only accounting and basic Chart of account
> installed
> - In the settings, check t the box to allow csv import
> - Go to Accounting / Journal Entries / Journal Entries and import the
> file attached to this bug report
>
> -> Result : OpenERP is not even able to display the 10 first lines as
> usual. If you open the Chrome web browser console, you see the error
> 'Request entity too large'). See attached my printscreen.
>
> I consider such a low capacity of import a bug since for it is not
> rare, especially for new implementation to have to import past
> accounting entries... and 30000 move lines is not much.
>
> What is you position on this point please ?
>
> Frederic Clementi
> Camptocamp SA
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/openobject-server/+bug/1223243/+subscriptions
>

[Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount)]

Please find attached graph analysis of a move importation.

Regards

Nicolas

[Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount)]

Hello,

One of the main point of improvement will be to fix the number of call of account_move post and validate functions.
They can be called many times when you create a move or a move line. If entry posted is set on journal it is a lot worse.

[Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount)]

Hello,

It appears that validated is called a lot of time but finally it should be called only once.
When the move is posted.

Actually account move validate function is called each time a move line is created even in draft. If entry_posted is allowed on journal if will be called for each move lines.... This make a lot of repetition. Things can be a lot worst if analytic/project module are installed as validate create and unlink analytic lines related to the move. This will launch all the triggers that depends on analytic line and literally redo a huge amount of work for nothing. If your analytic setup has a lot of data this can lead to have more than a second to create a move...

Regards

Nicolas

[Martin Trigaux (OpenERP) (mat-openerp)]

Hello,

The validation process has been reduced according to Nicolas's comment (revision 9458). This will impact most likely moves with a high number of lines. The example file has 3 moves per lines so not a big gain here (but way more if you have about 200 lines).
We may need to do some refactoring in trunk to rethink the validation process, I think it can be improved.

Importing a file with 3000 lines took me 3m30, so we can expect ~35min for a 30.000 lines file. 0.07s per line seems reasonable seeing the complexity of the task. We may be able to grab a few seconds there and there but I don't think we will really do a huge break on the complexity...

Suggestions are most welcome :)

[Frederic Clementi - Camptocamp (frederic-clementi)]

Martin, problem is that our customer do import move lines (mvl) with analytic account.
In this case, OpenERP, if I understand well validate the mvl in order to generate the analytical entries and do a sort of rollback... this is a perf killer don't you think.

Frederic

[Martin Trigaux (OpenERP) (mat-openerp)]

Hello,

We have merged two fix on account speeding up the performances to a more acceptable level

- revision 9458: reduce the number of time validate() function is called on account move (worst case was one per line, now should be one per move)
- revision 9679: reduce the number of time analytic lines are created and dropped as mentioned Frederic (only end of validation, unbalanced move creates no analytic lines)

Another possible fix (but not merged in 7.0) is grouping all the constraints on account.move.line to make only one browse instead of 8 per move.

If you have other suggestion of improvement, let us know