Performance is a very important issue in Odoo development. It might work fine for very few records, but if there are many records, it will be very slow because of poor database management in the application. Understanding how the Odoo ORM works and when to use SQL can help to optimize the development process.
Understanding Odoo ORM
(Object Relational Mapping) is the mechanism that allows Odoo to talk to the database, using Python instead of SQL.
partners = self.env['res.partner'].search([('customer_rank','>=',1)])ORM benefits include:
- Easier to read code
- Integrated security
- Easy maintenance
- cache/optimization
For all these reasons, use ORM instead of SQL.
Avoiding the N+1 Query problem
Another very common problem for Odoo performance is the N+1 query problem.
Bad approach:
orders = self.env['sale.order'].search([])
for order in orders:
print(order.partner_id.name)
Odoo will likely make more database queries to fetch the partners' details. It will significantly impact the app performance if there are thousands of records.
A better answer would be:
partner_names = orders.mapped('partner_id.name')The mapped() function provides efficient access to related records, avoiding too many database queries.
The Functions: mapped() and filtered()
The mapped() function is useful for getting field values from many records.
Suppose we have employees:
employees = self.env['hr.employee'].search([])
employee_names = employees.mapped('name')
This way, we simplify our code and let Odoo work with its own optimized mechanisms.
The filtered() function is used when you already have a recordset and want to select some records from it.
For example:
employees = self.env['hr.employee'].search([])
Suppose you want only active employees.
active_employees = employees.filtered( lambda emp: emp.active )
But in general cases, better to do the filtering at the database level.
Instead of:
employees = self.env['hr.employee'].search([])
department_employees= employees.filtered( lambda emp: emp.department_id.id == 5 )
Do the following instead:
department_employees = self.env['hr.employee'].search([('department_id', '=', 5)])Aggregation with read_group()
Developers are more likely to use loops to compute totals.
Normal approach:
orders = self.env['sale.order'].search([])
total = 0
for order in orders:
total += order.amount_total
print(total)
This is a good approach, but it does not work well when working with large data sets. You could do the same with read_group():
result = self.env['sale.order'].read_group( [], ['amount_total:sum'], [] )
This way, the query is executed directly from PostgreSQL, and the entire process becomes much more efficient. Recommended for read_group() are the following:
- Reporting
- Dashboards
- Statistics calculation
- Summary views
Prefetching & Caching
Odoo has some built-in optimization features such as prefetching and caching.
Prefetching
If a developer accesses multiple related objects at once, Odoo loads them all at once rather than performing individual queries. This is a way to optimize the number of database calls and increase performance.
cache
If the developer opens some objects in one request several times, Odoo stores the records in memory during the process of the request and uses cached information instead of querying the database again.
When Should You Use Raw SQL?
Even though it is recommended to use ORM in the majority of the cases, there are certain situations where using raw SQL will be more efficient. Some of the common use cases include:
- Large reporting systems
- Aggregation tasks
- Large data set processing
- Performance oriented operations
Example:
self.env.cr.execute(""" SELECT partner_id, SUM(amount_total) FROM sale_order GROUP BY partner_id """)
result = self.env.cr.fetchall()The use of raw SQL can work more efficiently because it is directly executed on PostgreSQL. Still, it does not take advantage of Odoo features like the following:
- Access rights
- Record rules
- Business logic
- ORM optimization
For this reason, raw SQL should only be used when the ORM is not able to perform some operations fast enough.
Optimization of performance is an important feature in developing applications using Odoo. Prevention of N+1 queries, the use of functions such as mapped() and read_group(), as well as the use of Odoo prefetching and caching features, are essential in improving the performance of applications. Most of the time, the ORM is the way to go in terms of performance; however, knowing when to use raw SQL is also crucial.
To read more about How to Optimize ORM Queries in Odoo 19, refer to our blog How to Optimize ORM Queries in Odoo 19.