Retrieving data from a record involves executing SQL queries. When
multiple datasets are involved, each requiring distinct queries, it
can lead to slower performance and increased processing time. This
guide explores how prefetching can address this challenge. By
implementing a prefetching strategy, you can minimize redundant
queries, improve data access efficiency, and boost the overall speed
of the framework.
Analyze the provided code, which demonstrates a representative
processing technique. In this case, 'self' represents a recordset
with multiple records. Prefetching is automatically triggered when
the recordset is accessed directly, ensuring efficient data
handling.
def compute_method(self):
for record in self:
print(record.name)
However, prefetching becomes more complex, especially when using a
browse based approach to access records. In the example model, the
for loop iterates over each record individually, which prevents
effective use of prefetching and results in more queries being
executed than expected.
Incorrect Prefetching Example (Using res.partner):
def process_customer_names(self):
self.env.cr.execute("SELECT id FROM res_partner WHERE customer = true")
for rec in self.env.cr.fetchall():
partner = self.env['res.partner'].browse(rec[0])
print(partner.name)
Rather than passing individual IDs directly into the browse method,
store the IDs in a variable and pass that variable instead. This
approach enables working with the entire recordset efficiently,
supports prefetching, and results in fewer and more optimized SQL
queries.
Correct Prefetching Example (Using res.partner):
def process_customer_names(self):
self.env.cr.execute("SELECT id FROM res_partner WHERE customer = true")
partner_ids = [rec[0] for rec in self.env.cr.fetchall()]
partners = self.env['res.partner'].browse(partner_ids)
for partner in partners:
print(partner.name)
Prefetching plays a key role in reducing the number of SQL queries
when working with varied datasets by efficiently retrieving required
information in advance. While it usually works efficiently in Odoo,
there are cases, such as splitting the dataset, as shown in the
provided model, where its performance can be affected.
records = [record for record in record_ids if record.id not in [101, 102, 103, 104]]
Prefetching is not utilized in the provided code because the
recordset is fragmented into multiple sections.Efficient use of
prefetching plays a vital role in optimizing object-relational
mapping (ORM) performance. In Odoo, when iterating over a recordset
using a for loop, accessing a field during the first iteration
triggers prefetching, which loads the corresponding field values for
all records in the set—not just the current one. This design
anticipates that similar data will be needed in subsequent
iterations. As a result, the fetched data is stored in memory, and
further access within the loop retrieves it from the cache instead
of executing additional SQL queries. This significantly improves
performance by reducing the number of queries from O(n) to O(1).
Consider a case where the recordset includes ten records. When the
name field is accessed during the first iteration, Odoo prefetches
the name field for all ten records in the set. This applies to any
other fields accessed as well. For the remaining iterations, data is
read directly from memory, avoiding additional database queries.
This optimization reduces the total number of queries from 10 to
just 1, improving overall performance.
def process_sale_orders(self):
self.env.cr.execute("SELECT id FROM sale_order WHERE state = 'sale' LIMIT 10")
sale_order_ids = [row[0] for row in self.env.cr.fetchall()]
sale_orders = self.env['sale.order'].browse(sale_order_ids)
for order in sale_orders:
print(order.name) # Prefetched for all records
print(order.date_order) # Also prefetched efficiently
Prefetching ensures that all fields (excluding *2many fields) are
loaded into memory, regardless of whether they are accessed in the
current loop. This approach is significantly more efficient than
triggering separate queries for each field, as loading extra fields
through prefetching has minimal effect on performance.
In certain cases, automatic field prefetching can cause unexpected
behavior during execution. To prevent this, you can disable
prefetching by using with_context(prefetch_fields=False) on the
recordset. Prefetching relies on the environment cache to manage
field values—once a record is read from the database, all future
access to its fields is handled through this cache. The cache can be
accessed via env.cache, and if needed, it can be reset using the
invalidate_cache() method to ensure data consistency.
When a recordset is divided, the Odoo ORM creates a new recordset
instance with its own prefetch settings. By default, any operation
on this new subset will prefetch data only for the records it
contains. However, to maintain shared prefetch behaviour across the
subsets, you can use the with_prefetch() method and pass the
appropriate record ID. In the example model, the recordset is split
into two parts, both of which receive the usual prefetch setup. As a
result, accessing fields in one group may cause the ORM to load and
cache data for the other, enhancing performance through temporary
shared caching.
def process_partners(self):
self.env.cr.execute("SELECT id FROM res_partner LIMIT 10")
partner_ids = [rec[0] for rec in self.env.cr.fetchall()]
partner_recordset = self.env['res.partner'].browse(partner_ids)
# Split into two parts
recordset1 = partner_recordset[:5]
recordset2 = partner_recordset[5:].with_prefetch(partner_recordset._ids)
# Access fields in first half, triggering prefetch of name and email for first 5
for partner in recordset1:
print(partner.name)
print(partner.email)
# Access fields in second half, triggering prefetch for all 10 using with_prefetch
for partner in recordset2:
print(partner.name)
print(partner.email)
In the given example, recordset1 limits its prefetching to just the
first five records. On the other hand, recordset2, by using the
with_prefetch() method, shares the full prefetch context of the
original recordset, enabling it to access all records more
efficiently. This illustrates that prefetching in Odoo is not
confined to the boundaries of sliced recordsets. The with_prefetch()
method can be applied to allow multiple recordsets to share a
unified prefetch scope. As a result, accessing a field on any one of
the records leads to the prefetching of that field for all
associated records, reducing the number of database hits and
improving overall performance.