Prefetch Patterns
When you access information from a record, the record is queried. If you have a data set with various data sets, specifying different SQL queries can slow down the framework when fetching the data sets. In this recipe, we'll look at how to solve this problem using a prefetching example. You can reduce the number of queries required by following a prefetch design. This makes the execution more streamlined and the framework faster.
Examine the accompanying code. It is a representative processing technology. In this strategy, the self is a record with many records. Prefetching works perfectly at the point of highlighting the recordset directly.
# Correct prefetching
def compute_method(self):
for rec in self:
print(rec.name)
However, prefetching can be more complex, for example, when retrieving information using a browse strategy. In the accompanying model, the for loop searches through the records one by one. This doesn't use prefetching effectively and runs more queries than expected.
# 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)
Instead of passing individual IDs to the Browse method, you can get the IDs into a variable and pass them right away. Then you can perform operations on that recordset. With that in mind, you don't lose the prefetch element, and the information is captured 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 reduces the amount of SQL queries when working with different data sets. This is done by fetching each piece of information without delay. Prefetching generally works consistently in Odoo, but under certain conditions, this component is lost, for example, when splitting the dataset as shown in the attached model.
records = [rec for rec in record_ids if rec.id not in [101, 102, 103, 104]]
The above code does not take advantage of prefetching as it splits the recordset into parts.
Accurate use of prefetching can contribute to the overall representation of object-relational mapping (ORM). You should look into how prefetching works in your engine. When you highlight a record in a for loop and access field values in the first iteration, prefetching retrieves information for the entire record instead of bringing information from the current record into the iteration. This is because once you reach a field within the for circle, you are likely to carry that information with you to the next record iteration. The main emphasis of the for loop, prefetching, fetches, and caches information for all records. The next iteration of the for loop will serve information from this cache instead of executing another SQL query. This reduces the number of queries from O(n) to O(1).
We should assume that the recordset contains ten records. Information for each of the ten records is retrieved when the name field of the record is accessed in the first loop. This is not only true for name fields. It also shows each field of these ten records. The next iteration provides information from memory. This reduces the number of questions from 10 to 1.
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 name of all 10 records in the first loop
print(rec.attention) # Prefetch attention of all 10 records in the first loop
Note that prefetching preserves the values of all fields (except *2many fields) regardless of whether those fields are used in this for loop idea. This is because, unlike the additional questions in each segment, the additional sections have a minor impact on performance.
In some cases, prefetched fields can interfere with execution. In such cases, prefetching can be disabled by passing False to the prefetch_fields setting, such as recordset.with_context(prefetch_fields=False). The prefetch component uses the environment cache to save and restore record values. This means that once a record has been retrieved from the information base, all subsequent calls to fields from the environment cache will be processed. You can access the environment cache using the env.cache property. To invalidate the cache, you can use the invalidate_cache() strategy in your environment.
When splitting a recordset, the ORM creates a new recordset with new prefetch settings. Executing a procedure on such a recordset prefetches information for individual records only. Assuming you need to prefetch each record after prefetching, you can do this by passing the prefetch record id to the with_prefetch() technique. In the companion model, it divides the recordset into two sections. Here, we passed typical prefetch settings for both datasets. So when you get information from one, the ORM will get information from the other and cache the information for some time.
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 recordset1:
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 code above, Recordset1 prefetches only five records in the recordset, but Recordset2 prefetches all the records in the recordset. Prefetch settings are not limited to recordset detachment. You can also use the with_prefetch() strategy to have typical prefetch settings across different recordsets. Which means, when information is fetched from one record, information is also fetched for all remaining recordsets.