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

Bug #1223243 reported by Frederic Clementi - Camptocamp
16
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Odoo Server (MOVED TO GITHUB)
Fix Released
Low
OpenERP Publisher's Warranty Team
OpenERP Community Backports (Addons)
New
Undecided
Unassigned
6.1
Fix Committed
Undecided
Pedro Manuel Baeza

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

Tags: maintenance

Related branches

Revision history for this message
Frederic Clementi - Camptocamp (frederic-clementi) wrote :
tags: added: maintenance
Revision history for this message
Frederic Clementi - Camptocamp (frederic-clementi) wrote :
Revision history for this message
Guewen Baconnier @ Camptocamp (gbaconnier-c2c) wrote :

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)

Revision history for this message
Frederic Clementi - Camptocamp (frederic-clementi) wrote :

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.

Revision history for this message
Guewen Baconnier @ Camptocamp (gbaconnier-c2c) wrote :

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.

Revision history for this message
Xavier (Open ERP) (xmo-deactivatedaccount) wrote :

> 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.

Revision history for this message
Guewen Baconnier @ Camptocamp (gbaconnier-c2c) wrote : Re: [Bug 1223243] Re: [7.0] Impossible to import a csv file with 30000 account move lines

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
>

Changed in openobject-server:
assignee: nobody → OpenERP Publisher's Warranty Team (openerp-opw)
Amit Parik (amit-parik)
Changed in openobject-server:
assignee: OpenERP Publisher's Warranty Team (openerp-opw) → OpenERP's Framework R&D (openerp-dev-framework)
importance: Undecided → Low
status: New → Confirmed
assignee: OpenERP's Framework R&D (openerp-dev-framework) → OpenERP Publisher's Warranty Team (openerp-opw)
importance: Low → Undecided
status: Confirmed → New
Revision history for this message
Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount) wrote :

Please find attached graph analysis of a move importation.

Regards

Nicolas

Revision history for this message
Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount) wrote :

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.

Revision history for this message
Nicolas Bessi - Camptocamp (nbessi-c2c-deactivatedaccount) wrote :

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

Revision history for this message
Martin Trigaux (OpenERP) (mat-openerp) wrote :

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 :)

Revision history for this message
Frederic Clementi - Camptocamp (frederic-clementi) wrote :

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

Revision history for this message
Martin Trigaux (OpenERP) (mat-openerp) wrote :

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

Changed in openobject-server:
status: New → Fix Released
importance: Undecided → Low
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.