Caching is a silent hero in any ORM, and in Odoo, that hero is called prefetching. Introduced years ago, prefetching keeps gaining refinements, and Odoo 18 pushes the idea even further with clearer patterns, guard-rails, and measurable speed-ups. If you routinely write custom modules or large-scale data-processing scripts, understanding prefetch patterns is the difference between code that merely works and code that scales.
What is Prefetching?
Prefetching is a technique where related data is loaded in advance, anticipating future needs. In Odoo's ORM (Object-Relational Mapping) context, prefetching helps minimize the number of database queries by fetching related records in bulk rather than one at a time.
How Prefetch Works in Odoo
Odoo's ORM automatically manages prefetching to some extent, but developers can optimize it further:
# Basic record access without explicit prefetch
orders = self.env['sale.order'].search([('state', '=', 'confirmed')])
for order in orders:
print(order.partner_id.name) # Potential N+1 query problem
This code fetches confirmed sale orders and then accesses each order’s partner name in a loop without using explicit prefetching, which can cause an N+1 query problem by triggering a separate database query for each partner, leading to inefficient performance.
Custom Prefetch Patterns
Developers can implement custom prefetch patterns for optimal performance.
# Explicit prefetch example
orders = self.env['sale.order'].with_prefetch(
prefetch_ids=confirmed_order_ids
).search([('state', '=', 'confirmed')])
# Prefetch related models
orders = self.env['sale.order'].search([('state', '=', 'confirmed')])
orders.mapped('partner_id') # Prefetches all partners
This example shows explicit prefetching in Odoo. It first uses with_prefetch to preload specific sale orders by their IDs before searching confirmed orders. Then, it fetches confirmed orders and uses mapped('partner_id') to prefetch all related partners, minimizing database queries and speeding up access to related data.
Best Practices for Prefetching
* Analyze query patterns: Use Odoo's logging or database monitoring tools to identify N+1 query problems
* Prefetch in bulk operations: Always implement prefetching when processing large recordsets
* Leverage mapped() and filtered(): These methods automatically handle prefetching efficiently
* Consider field attributes: Use related and compute attributes properly to optimize data access
* Test with realistic data: Prefetch optimizations should be validated with production-like data volumes
Advanced Techniques
The following illustrates multi-level prefetching in Odoo, where confirmed sale orders are first fetched, followed by their partners, and then their countries, loading related records efficiently. It also sets a custom prefetch context to specify which fields to preload for sale.order and res.partner, optimizing database queries and improving performance.
# Multi-level prefetching
orders = self.env['sale.order'].search([('state', '=', 'confirmed')])
partners = orders.mapped('partner_id')
countries = partners.mapped('country_id')
# Custom prefetch context
self.env.context = dict(
self.env.context,
prefetch_fields={
'sale.order': ['partner_id', 'date_order'],
'res.partner': ['name', 'email']
}
)
Proper implementation of prefetch patterns in Odoo 18 can significantly improve application performance, especially for complex operations and large datasets. By understanding Odoo's prefetch mechanisms and applying the techniques outlined above, developers can create more efficient and scalable Odoo applications.
Remember that prefetch optimization should be balanced with memory usage considerations, as excessive prefetching can lead to higher memory consumption. Always profile your changes to ensure they provide the intended performance benefits.
To read more about What is Prefetch Patterns in Odoo 16, refer to our blog What is Prefetch Patterns in Odoo 16.