Sum rows in expression takes longer than aggregate sum in table view

[guasi]

Not sure how to ask this. Bare with me.

• TableA has columns 'date', 'category' and 'amount' (20K rows)
• TableB has 7 rows of unique categories (these tables are not relational)
• I have a dynamic dashboard filter that updates a slice of TableA given the categories in TableB and a date range.

Scenario A

• I have a table view that groups by 'category' and aggregates the sum of amount.
• The aggregated sum updates very fast upon change in dashboard filter.

Scenario B

• TableB has a virtual column 'total' that calculates the sum of 'amount' for the category in each row using the following expression 

SUM(
  SELECT(
    BudgetReady[Amount+], 
    [Category] = [_THISROW].[Category]
  )
)

• The 'total' column updates very very slow upon change in dashboard filter.

Any ideas why? At first I thought it was slow because the table has 20K rows and the slice was taking a while to update. However, using the 'Group by' and 'Group aggregate' native features on the updated slice, I found speed has nothing to do with the amount of rows. Both scenarios give same answer, just Scenario B takes a lot longer.

[Suvrutt_Gurjar]

Your observation is correct. I believe AppSheet uses some internal calculations in scenario A as it has already group by information available.

In scenario B you are performing the calculation through an expression built by you each time the app syncs. There are many informative posts/discussions  available on how VCs with multi row expressions (SELECT(), FILTER()) degrade sync performance. Please search the community by keywords such as  "performance" or "virtual column performance".

It may be a good idea to use AppSheet provided group by totals /counts/sums wherever possible.

[guasi]

Thank you! I read some threads regarding this issue as you suggested and found some suggest to use what they call the "quick update" method. I followed this suggestion to make a normal column to keep the 'total' and to update that column with the "quick update" method.  However, the update took just as long. 

The actions to update did work, they're just not as fast the system generated sum. 

I'll keep playing around with it, but I'd love to hear how people have solved this problem. Seems like a common problem to run into, so I figure lots of people have come up with other solutions.

Thanks!