ERP Tech Talk #8: High-Performance Odoo

In IT Paragon’s ERP Tech Talk #8, it was Indrajaya’s turn to share his experience in monitoring the performance of the ERP System in Paragon.

Figure 1: Performance

Introduction

Performance is a common issue that exists in any application, especially large-scale applications such as ERP. One of the problems that often arise regarding system performance is a system that is running slowly, causing the following problems:

• User complains
• Wasting work time
• Interfere with work concentration
• Doing worthless work

Example Issue

The issue that has occurred in the ERP system in Paragon (we using Odoo) is the need to process large amounts of invoice reconciliation in one click (more than 6000 invoices). Which when using the built-in Odoo feature the user does it all day without success.

Figure 2: I’ll Just Wait Here

Root Cause

After looking for the cause, it was found that the problem occurred due to the use of ORM in Odoo. Yes, it’s true that ORM. Although ORM is very helpful for developers in programming applications without having to mess with SQL syntax in interacting with databases. But there are negative sides to using ORM on Odoo, that’s is:

• Odoo generate a bunch of SQL statement
• Some part lock the table becomes a race condition
• Consume resource a lot for unnecessary operation

Solutions

One solution that can be taken is to convert ORM to SQL, with the following pro and cons :

• Pro: custom query and optimization, and Extremely fast
• Cons: Need highly care when change table structure and relation

How to

There are two steps to convert ORM to SQL, the first is to generate SQL queries that are generated by Odoo’s default ORM statements for analysis, and the second is to reconstruct SQL queries to make it more efficient. To generate SQL query from Odoo’s default ORM statements, it can be done in two ways:

Using Log Postgresql

Previously we have to make settings on Postgresql Configurations as follows:

log_duration = on
logging_collector = on
log_destination = stderr
log_statement = all
log_directory = /tmp
log_filename = postgresql.log

If we using docker-compose, we can add configuration as follows :

version: ‘2’
services:
    db:
        command: postgres -c -c log_duration=on -c logging_collector=on -c log_destination=stderr -c log_statement=all -c log_directory=/tmp -c log_filename=postgresql.log

After that, if we click any button in Odoo like this:

Figure 3: Example Button in Odoo

We can find in the postgresql.log file the SQL query generated from this process. And we can analyze the query which we will reconstruct in the next step.

Figure 4: Content of postgresql.log

Odoo Shell

If the first way, the resulting query can be very large because the process of one-click on the button can trigger several of Odoo’s methods. Then in this second way, we can generate SQL queries based on a certain Odoo’s methods. The first step is that we must enter Odoo Shell, if we use docker, we can using these commands:

docker exec -it <container name> odoo shell -c /path/to/odoo.conf -d <db_name> --log-level=debug_sql --no-http --db_host=<db_host> --db_user=<db_user> --db_password=<db_password>

and after entering the Odoo shell, we can use the command:

>>> env[‘<model.name>’].browse(<id>).<name.of.methode>()

example:

>>> env[‘account.invoice’].browse(1).action_invoice_open()

Figure 5: Result generate SQL queries by methods

Reconstruct Query

Based on the results of generating SQL queries above. We can reconstruct new queries that are more efficient and according to our needs. And we can implement them by modifying the Odoo module associated with the process whose SQL queries we want to optimize.

Conclusion

The use of ORM makes it very easy for us as programmers to develop applications. However, don’t let that make us reluctant to learn basic things like SQL Query, because similar problems can occur at any time and whatever tools we are using.

Reference :

1. IT Paragon’s ERP Tech Talk #8 by Indrajaya