Prefetch patterns
When retrieving information from a record, the record undergoes a query process. Handling diverse datasets with distinct SQL queries can potentially slow down the framework during data retrieval. In this guide, we explore a solution to this issue through a prefetching example. By adopting a prefetch design, you can optimize execution, streamlining the process and enhancing the overall speed of the framework.
Review the provided code, which serves as a representative processing technique. In this approach, 'self' represents a record containing multiple records. Prefetching seamlessly operates when interacting with the recordset directly.
# Correct prefetching
def compute_method(self):
for rec in self:
print(rec.name)
Yet, prefetching becomes more intricate, particularly when fetching information using a browse strategy. In the given model, the for loop traverses the records individually, not leveraging prefetching effectively and consequently executing more queries than anticipated.
# Incorrect prefetching
def some_action(self):
record_ids = []
self.env.cr.execute("some query to fetch record id")
for rec in self.env.cr.fetchall():
record = self.env['purchase.order'].browse(rec[0])
print(record.name)
Rather than supplying individual IDs to the Browse method, consolidate the IDs into a variable and pass them directly. This enables operations to be performed on the recordset, preserving the prefetch element and capturing the information in individual SQL queries.
# Correct prefetching
def some_action(self):
record_ids = []
self.env.cr.execute("some query to fetch record id")
record_ids = [rec[0] for rec in self.env.cr.fetchall()]
recordset = self.env['purchase.order'].browse(record_ids)
for record in recordset:
print(record.name)
Prefetching is instrumental in minimizing the number of SQL queries when managing diverse datasets. It achieves this by promptly fetching each piece of information. While prefetching typically operates seamlessly in Odoo, there are specific scenarios, such as when dividing the dataset as illustrated in the provided model, where this functionality may be compromised.
records = [rec for rec in record_ids if rec.id not in [101, 102, 103, 104]]
The provided code lacks the utilization of prefetching due to the segmentation of the recordset into parts.
Effectively leveraging prefetching can significantly enhance the overall effectiveness of object-relational mapping (ORM). Understanding how prefetching operates in your engine is crucial. When a record is highlighted in a for loop, and field values are accessed in the initial iteration, prefetching fetches information for the entire record rather than fetching information specific to the current record within the iteration. This is because once a field is reached within the for loop, it is likely that this information is carried forward to the subsequent record iterations. The primary focus of the for loop, prefetching, retrieves, and caches information for all records. Subsequent iterations of the for loop retrieve information from this cache rather than executing additional SQL queries. This optimization reduces the number of queries from O(n) to O(1).
Consider a scenario where the recordset contains ten records. Accessing the name field of the record in the first loop triggers the retrieval of information for each of the ten records. This holds true not only for name fields but also for every field of these ten records. Subsequent iterations retrieve information from memory, effectively reducing the number of queries from 10 to 1.
def some_function(self):
self.env.cr.execute("select id from purchase_order limit 10")
record = self.env['purchase.order'].browse(record_ids)
for rec in record:
print(rec.name) # Prefetch the name of all 10 records in the first loop
print(rec.attention) # Prefetch attention of all 10 records in the first loop
It's important to note that prefetching retains the values of all fields (excluding *2many fields) irrespective of whether those fields are utilized in the current for loop context. Unlike the additional queries in each segment, prefetching additional fields has a minimal impact on performance.
In certain scenarios, prefetched fields may interfere with execution. To address this, prefetching can be disabled by setting prefetch_fields to False, such as recordset.with_context(prefetch_fields=False). The prefetch mechanism utilizes the environment cache to store and retrieve record values. This implies that once a record is fetched from the database, all subsequent calls to fields are processed from the environment cache. Accessing the environment cache is possible using the env.cache property, and to invalidate the cache, the invalidate_cache() method can be employed in the environment.
When a recordset is split, the ORM generates a new recordset with fresh prefetch settings. Executing a procedure on such a recordset prefetches information for individual records exclusively. If the goal is to prefetch each record after the initial prefetch, this can be achieved by passing the prefetch record id to the with_prefetch() method. In the accompanying model, the recordset is divided into two sections, with typical prefetch settings passed for both datasets. Consequently, fetching information from one dataset leads the ORM to retrieve information from the other and cache it for a certain duration.
def some_function(self):
self.env.cr.execute("select id from purchase_order limit 10")
record_ids = [rec[0] for rec in self.env.cr.fetchall()]
recordset = self.env['purchase.order'].browse(record_ids)
recordset1 = recordset[:5]
for rec in recordset1:
print(rec.name) # Prefetch name of all 5 records in the first loop
print(rec.attention) # Prefetch attention of all 5 records in the first loop
recordset2 = recordset[5:].with_prefetch(recordset._ids)
for rec in recordset2:
print(rec.name) # Prefetch name of all 10 records in the first loop
print(rec.attention) # Prefetch attention of all 10 records in the first loop
In the provided code, Recordset1 prefetches only five records within the recordset, whereas Recordset2 prefetches all the records in the recordset. It's crucial to note that prefetch settings are not confined to recordset segregation. The with_prefetch() method can also be employed to apply common prefetch settings across diverse recordsets. This implies that when information is fetched from one record in a recordset, information is concurrently fetched for all remaining recordsets.