I’ve got a performance problem with a particular calculated column on a table. It now takes at least two seconds to add a row and it’s getting worse. It’s fairly obvious why it’s slow but I wondered if anyone had any ideas of a more efficient way of getting the same result? Or if there are back-end improvements that could be done?
The general idea is to be able to allocate assets to jobs and know if there are enough assets to cover all the jobs that are out at once.
The referenced tables (summarised with most of the columns removed)
# This is a table of general types of assets, eg `MacBook Pro`, `Mouse`, `IEC Power Cable`
# ~150 rows
@grist.UserTable
class Asset_Types:
Description_Formal = grist.Text()
# This is how many of this asset type we own, calculated by counting Assets
def Have(rec, table):
return len(Assets.lookupRecords(Type=rec.id,Disposed=None))
# This is a table of instances of Asset Types, one row for each physical instance
# ~500 rows
@grist.UserTable
class Assets:
Disposed = grist.Date()
Type = grist.Reference('Asset_Types')
# This table describes load lists. A load list is a list of asset types and quantities to be taken to a job.
# The load lists have a out and in date and multiple load lists might overlap
# ~15 rows (and growing)
@grist.UserTable
class Load_Lists:
Out_Date = grist.Date()
In_Date = grist.Date()
# This is the set of load list item rows that make up this list
def Items(rec, table):
return Load_Items.lookupRecords(List=rec.id)
# This is the set of lists that intersect the same date range as this one (including this one)
def Overlapping(rec, table):
return list(r for r in Load_Lists.all if ((min(rec.In_Date - r.Out_Date, r.In_Date - rec.Out_Date).days + 1) > 0))
This is the problem table (Most of the columns removed) - Stock
is the problem column
# These are the items for the load lists. One row describes an asset type and quantity
# ~650 rows and growing quickly
@grist.UserTable
class Load_Items:
List = grist.Reference('Load_Lists')
Asset = grist.Reference('Asset_Types')
Quantity = grist.Int()
# This compares the number of assets of this type that we have (the count of matching items in the Asset table)
# to the number of assets of that type in load lists which have the same date range as this one, including this one.
def Stock(rec, table):
if(not rec.Asset):
return None
return rec.Asset.Have - sum(r.Quantity for r in Load_Items.all if(r.Asset == rec.Asset and rec.List in r.List.Overlapping))
Obviously adding a Load_Items
row invalidates Stock
for all other rows because it references Load_Items.all
.
It’s worth mentioning that I view the Load_Items
table with a SELECT BY
on a Load_Lists
card. The performance was acceptable when there was only one or two load lists in the system (ie about 100 item rows) but now it’s slow even when I’m viewing a list with only a few items. I suppose it’s recalculating all the rows in the table but it would be quicker if I could make it only recalculate the ones in view.
I also though about hiding the Stock
column then just checking it at the end but that doesn’t make it faster, only deleting the Stock column makes it fast again.
Any suggestions?
Thanks,
Mark Daniel