Performance impact summing specific rows from other sheets

[Djigi]

Hi,

I think I have a pretty performance impactful script in my AppSheet:

[startweight] -
SUM(SELECT(stock[weight], [idingredient] = [_THISROW].[id])) -
SUM(SELECT(packaging[amount], [idpackaging] = [_THISROW].[id])) -
SUM(SELECT(vans[weight], AND([idproduct] = [_THISROW].[id], NOT([type] = 'lost')))) -
SUM(SELECT(customers[weight], [idproduct] = [_THISROW].[id]))

It works like a charm, but the app is not operational yet. I'm wonder what impact in performance this will have after a year of data. Doe anyone have experience with this?

Also, if anyone has good suggestions on how to make it less slow in the end, I'm very curious on how to do it better, always.

[Rafael_ANEIC-PY · Accepted solution]

Hello @Djigi, all your expressions can be improved with references, as all your select use [_THISROW].[id] I guess for each of them there's a "Related Something", like "Related Stock" or "Related Packaging", so you can do this:

[startweight]-
SUM([Related Stock][weight])-
SUM([Related Packaging][amount])-
SUM(SELECT([Related Vans][weight],NOT([type] = 'lost')))-
SUM([Related Customers][weight])

You could also create a slice of your vans, containing only the not "Lost" ones, and then add a ref_rows linked to it in your table, that way you could get rid of the select for this expression:

SUM([Ref_Row_To_Slice][weight])

And those won't ever give you any problems, they're as efficient as can be.

[Djigi]

Hero! It works like a charm. I wasn't using these ref columns for anything yet. I can see the immense benefits of it! Now I suppose it won't slow down as much as I predicted. Thank you!