On the first access to debit, we read all the eager fields (which is to put it simply the local fields to the model) for a whole batch of records. Which means that this sum does 2 SQL queries when looping over 1609 rows (IIRC the batch size is 1000 records).
I don’t know what move.select_cache does but presumably one SQL query per move.
So it’s very surprising that using it is 2x faster than the sum call. It might be an error in the measuring process but if it isn’t then we would be very eager to understand what does select_cache do .
def select_cache(self, list_names, table):
if self.id:
cur = Transaction().connection.cursor()
field_str = ",".join(list_names)
cur.execute("SELECT "+ field_str+" FROM "+table+" where id="+str(self.id)+" ;")
result = cur.fetchone()
if result:
ix = 0
for name in list_names:
if self._local_cache.get(self.id,-1)==-1:
self._local_cache[self.id] = {}
self._local_cache[self.id][name] = result[ix]
ix+=1
Indeed, that is what tryton does, I have checked it.
you guys are geniuses.
I have continued looking at how to make the code faster:
sum = sum(l.debit - l.credit for l in move_lines) # noqa
I clarify that this is version 6.4, and I do it to improve an old version.
I was able to add a selection of preferred fields to the 1000 rows process, giving better results.
It turns out that being eager, in addition to choosing more rows, also chooses fields that have to be taken to an instance and access verified: many2one fields, etc. so that consumes more process.
the test:
MoveLine = Pool().get('account.move.line') # noqa
move_lines = MoveLine.search([])
move_lines[0].set_preferred_fields(["debit","credit"])
suma = sum(l.debit - l.credit for l in move_lines) # noqa
without the preferred_fields it was: 0.428 seconds.
I also got good results on a function field(especially in the old version that I need), but it looks ugly:
def get_party_name(self, name):
if self.id:
self.set_preferred_fields(["create_date","number","reference","description","invoice_date"])
te = str(self.create_date)+str(self.number)+str(self.reference)+str(self.description)+str(self.invoice_date)
if self.party:
self.party.set_preferred_fields(["name"])
te += self.party.name
self.party.set_preferred_fields([])
self.set_preferred_fields([])
return te
return ""
I clarify that it is only useful when there are many records.
The problem with a method such as set_preferred_fields even if it was properly implemented (that is, it changed all fields to lazy except the ones provided in the argument) is that when the code continues after the code you’re profiling the list of fields set as eager may not be enough and hurt performance.
If you need to optimize this specific code, I’d suggest simply using read()which is a lower level method but allows you to specify the list of fields to be loaded and is part of the API and with no need for specific SQL expressions.
hi!
yes i been wearing read and search_read. but i wanted have more control in the instances and property.
i believe that this also It will be useful in reports. or bypass check accesses of many2one fields when there are many rules mixed.
In any case, I share the test code in modelstorage(class ModelStorage).
def set_preferred_fields(self, field_names):
if self._cache:
self._cache["preferred_fields"] = field_names
inside __getattr__ before :
# Order data read to update cache in the same order
index = {i: n for n, i in enumerate(ids)}
read_data = self.read(list(index.keys()), list(ffields.keys()))
add
if field.loading == "eager" and self._cache.get("preferred_fields", -1)!= -1:
if self._cache["preferred_fields"] and type(self._cache["preferred_fields"]) == list:
new_ff = {}
for fn, fi in ffields.items():
if fn == name:
new_ff[fn] = fi
if name in fn :
new_ff[fn] = fi
if fn in self._cache["preferred_fields"]:
new_ff[fn] = fi
ffields = new_ff
.