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
Yes, some of the generated queries in the tested module are very long and complex, maybe too much, but for example I can also see, in the same data set, that about half of the time spent in tryton’s ir.Model.get_access is really spent in the same sql.Query methods.
Do you think a (partial) C implementation of python-sql could be useful ?
Well I’m not because Tryton is basically a tool to make SQL query and sql.Query.__iter__ is the main function to build SQL query.
I do not think because it will just have to do the same thing because we want to keep flexibility and introspection.
But I think the current design of building the query and parameter could be improved by:
looping only once to build both
use str.join only at the end and use a list as intermediary value.
use f-string when list is not possible
use a single parameter and tuple for array values (and use array more often in Tryton)
.