Performance - Planning Ahead

[Fraxinus]

Hi there,

Could someone point me to a thread on (or respond to) the below topic?

I am in the process of creating one of my first apps, and I am trying to make conscious performance choices NOW. For simple expressions/formulae that can all be calculated using the three methods (below), all other things being equal, which should I use?

• Spreadsheet expression
• Column expression
• Virtual column expression

USE CASE

I am retrieving a stock price (say Apple) in Google Sheets, because the expression is not supported by AppSheet. Once I have the price (in Sheets), I want to calculate the value of my shares by multiplying the share price with the number of units. I'd like to understand the rank order of most efficient to least efficient solution:

• Google Sheets multiplies the units by the price (100% Google Sheets calculation)
• AppSheet standard column expression multiplies the units by the price retrieved by Google Sheets
• AppSheet virtual column expression multiplies the units by the price retrieved by Google Sheets

CONSIDERATIONS

I am trying to understand which is more beneficial for performance, assuming my app will grow to tens (potentially hundreds) of thousands of rows of data:

• Keeping the spreadsheets as small as possible and offloading more of the work to AppSheet, potentially having multiples column types spanning multiple tables, or
• Keep the workload on AppSheet as light as possible, but get GoogleSheets to do more of the calculations, while AppSheet completes other tasks

I'd welcome some thoughts/feedback from the community. Thanks!

[Aurelien · Accepted solution]

Hi @Fraxinus 

Depending on the amount of data, virtual columns should be preferred. More generally, you will need to consider Google Sheets as a static database. Even more if you plan to have hundreds of thousand rows of data, even Google Sheets will starting being overloaded.

Then, you can:

- use security filters for unnecessary data on devices

- use physical columns when that would make sense, for example for calculations that need to be performed very rarely.

I suggest you read this documentation:

App performance: Core concepts - AppSheet Help

Improve the speed of Sync with database updates - AppSheet Help

[Fraxinus]

That's super helpful - thank-you!!

Just to play back what I read in those articles, the ranked order of most efficient operations is:

1. AppSheet virtual column expression multiplies the units by the price retrieved by Google Sheets
2. AppSheet standard column expression multiplies the units by the price retrieved by Google Sheets
3. Google Sheets multiplies the units by the price (100% Google Sheets calculation)

The reasons (and please sense check me - I am paraphrasing to see if I understand everything) are:

1. Spreadsheets requires the most re-computation on an update, because all spreadsheet formulas have to be recalculated on the back-end, before the updated data can be returned to the AppSheet API.
2. Standard columns require modest re-computation because the computations sit at the API level, closure to the end users.
3. Virtual columns require the least re-computation because they reduce the amount of data transferred during a sync.

I say the above with the understanding that bloated computation and data transfer are are to be avoided with best practices, like slices and security filters.

Can I get a head nod/shake if I got that right/wrong?

[Aurelien]

Hi @Fraxinus 

I would add: if you can, prefer avoiding any AppSheet calculation on physical columns, as they are calculated on edition only, not on the fly.

Virtual columns are calculated on the fly.

I would say too:

- AppSheet virtual columns give the most accurate result, but may require a good engine on the device 😉

This means, if a price should evolve ==> virtual columns computation will stay true, while real columns computation will turn wrong, or more exactly: not up to date.

And you are correct, least data transferred locally if you prefer AppSheet's virtual column computation over "Google Sheets computation".

Not sure I answered to all of your questions, but there are additional insights though 🙂

In the end, it's a compromise between: data that need to be up to date, amount of data transferred, and computation capabilities.

[Fraxinus]

Sure seems like you helped me out! I am walking away with a MUCH more clear understanding of how to prioritize which type of method to use. I welcome others to opine if there is more to add, and if they agree, just like the solution! 🙂

@Aurelien - Thank-you so much!!