Performance improvement tips

I was searching the archive for information about the “Worksheet bulk read” option for syncing data and came across these tips from Simon Robinson which sound very useful to me. However, I have some questions about them that I’d like to post below:

URL: Hi What is best practice in terms of workshe...

Tip 1 = The key thing to note first is Google Sheets will limit you to 2million cells per spreadsheet.
So if you’re gonna go over that, then definitely look at having more than 1 spreadsheet file.
But if that’s not going to happen then definitely keep all tables in one sheet as Appsheet can read them faster

Tip 2 = Don’t use virtual formulas if you can avoid them.
Especially where they have to be recalculated on every sync for tables with a large number of rows (>500)

This intrigues me. I think I can cut down on some of my virtual formulas by putting formulas in columns in my Google spreadsheet that do the same work. This will make my spreadsheet bigger, but will that make syncing faster? I imagined that virtual formulas would make my app more efficient but I guess I was wrong about that.

Tip 3 = Make use of security filters to reduce the number of rows that have to be synced

I have never used a security filter before so this is completely new to me. If I right-click on a column on Google sheets I see “Protect range.” Is that what I should use? Will doing so with read only columns make a difference? Is there anything to watch out for in using “Protect range” if I will be the only user of my app? For example, if I use a security filter on a prototype app that I will share with students, will the filter cause problems in the copying process?

Tip 4 = Mark any relevant tables as read-only and turn on Server Caching

Tip 5 = Also turn on Worksheet bulk read, Delta sync and Automatic Updates

Currently, if I choose “Worksheet bulk read” it says “The Bulk Read setting is enabled by default for all apps.” Does that mean it doesn’t matter whether or not I select this?

Tip 6 = Use the sync time analyser to check for any hold-ups

By the way, I have recently responded to an old thread that is also related to performance but I don’t think anyone noticed. The thread is about background syncing. “Background syncing” is confusing because it can mean the syncing of data that the app does, while in use, in the background. I’m interested in another kind of background syncing: the continuation of syncing activity by the app when the app itself has be put into the background (the person is using another app on the phone). That strikes me as being very important (people are not forced to sit and wait) but I haven’t been able to get it to work on iOS. It does seem to work on my Android device (a Huawei phone). Any comments?

URL: Background syncing

5 28 3,247
28 REPLIES 28

Lynn
Participant V

Hi @Kirk_Masden With regard to #3 Check out this link

Thanks @Lynn! This is good information for me. I should have know that I should search for “security filter” in this user group. In addition to the page you introduced to me, I found this video in which Santiago provides a clear demonstration from about 40 minutes in:

EugeneB
Participant V

What is the different between using virtual column and also normal column for calculation. it is correct using normal column the formulation is only happened when the column is being edit?

Hi @EugeneB ! I was quoting @1minManager (@Simon_Robinson ) here. I think the advice is good but I don’t feel qualified to explain it.

I can say from my own experience that virtual columns can be very “expensive” if they are used in a way that makes them dependent on other rows in a big sheet. On the other hand, if they only calculate other values that are located within a row, and those values are not dependent on other rows, virtual columns can be very useful. In fact, I use them all of the time.

That’s about all I can say for now. I hope others will share their insights with us.

A normal (non-virtual) column value is only recomputed when its row is modified, as when updated in a form, or by an action. The column value is stored in the data source (e.g., in the spreadsheet).

A virtual column value is recomputed when any non-virtual column of its row is modified, and when the the app data is synced with the data source. The column value is not stored in the data source.

Virtual columns can substantially hurt the performance of your app, so care should be taken to avoid unneeded virtual columns and virtual columns with complex expressions or expressions that process large amounts of data. Virtual columns are the #1 cause of performance problems in AppSheet apps.

@Steve thanks for the detailed explanation.
Can we use action to update list of normal column, tried just now seem that we are only able to add action to one record ( action to make modification for one record at a time) the action button only display in the record, not at the table view.

i have an two complex app formula in a record Help Needed - Two DateTime Duration to exclude non working hours - #8 by EugeneB (due to appsheet doesn’t have function yet, to calc duration of working hours) currently was using Virtual Column but because we are going to have large record in the future. just worry about the performance (currently it is already running at 15-25 seconds) was thinking to put the app formula in Normal column and using action to manual trigger the refresh.

Another questions is that, will the virtual column still compute even we didn’t display it in the view? was thinking to use a slice to only display the virtual column on a smaller set of data.

we are currently thinking the way to optimized/speed up the performance of the app, currently we have the user feedback the app is taking long to sync/load. (15-25 sec) if we not able to improve that, the user experience will be bad, and it might be not practical to use the app or we have to drop to use the app.

Yes it does. This is because even if a virtual column is hidden, it can still be used in workflow/automation template files and referred to in other formulas

Hahaha, the worst one I have is around 90secs! Once you get much past that the app defininition won’t always work. But it has multiple tables with > 6000 rows

Yes you can, and doing this with the most time consuming formula shaved off about 25secs per sync on my App

So in my example the parent table was a timesheet clock in / clock out type data. The daughter table would record the individual items the user had fitted that day. Each of these items had a price and the sum of those prices was what the App user would be paid that day. The SUM formula was the Parent Timesheet table.

To start with this SUM(Select([Related Items][Price],TRUE)) was just a virtual column. But the timesheet table was 5000 rows and adding 30ish rows per day. The item table was nearing 8000 rows and adding 100-200 per day.

So what I did was:

  • Change it from a virtual to a normal column with the same name so it didn’t mess up any formulas or templates
  • Put the formula to be in the Initial Value section of this new column
  • Turn on reset on edit. So now the formula will update whenever anyone changes something in the timesheet record
  • Because we are now using an initial value, this leaves the issue that a user might be able to modify this column. So either make it hidden. Or if you need to let them see it, set the the editable formula to be UserEmail()="Your_Email_Address"
  • But we now have another issue. The user could create a timesheet entry, then create an item in the daughter Item table. But unless they go back into the timesheet record the formula will not update.
  • So what you need to setup is a automation bot that triggers when a daughter table Item is added or edited. This will then call an action that updates the column we created above.

Yes its a major task, but there isn’t any other way that I know of. Hoep this helps

Simon@1minManager.com

Thank you very much for these informative comments, @1minManager .

I wonder if I could impose upon you to request a little advice regarding my own project. I wrote it up here today:

I put it in the form of a tip but it includes a basic question. The tip is that there are two ways to make inline link actions: 1) automatic via a virtual column; and 2) manual with a hand-made action that is hung on a real column. The question is Can I expect the second approach to be significantly better if I have a large number of such hyperlinks?

I have lots the first type of virtual column hyperlinks in an app (about 25 in one table) and it’s slow on my rather clunky Android device. Sync is not the problem. The issue is that using column headings on a table to reorder takes forever. I assume that cutting down on virtual columns should help but I don’t really understand the inner workings. Do you think that there is likely to be a significant performance differences between hyperlinks in virtual columns vs. real columns and actions?

If it’s not too much trouble, @Steve, I would love to know what you think about this matter too. Thanks!

So am I correct in thinking you are starting with a table of 8000 rows? Or are you adding them one by one as you go along?

Starting with 8000 rows is correct. But the user can add words to the collection.

So I’d suggest a 2 fold approach

1 - Create a table of 8000 rows with your english works. Have a column for each of the URL’s you want to generate. Put your formulas in the Initial Value section of each of these columns. So this way when you add a new word, the URLs will be created actually in the form.

2 - But what to do about the existing 8000 words. Well all you have to do is create a Automation bot that performs an action on multiple rows. In my apps I’d normally schedule this, but if you don’t have the $10 license you may need to trigger it with an action. All this bot need to be told to do is go into each row and update one column, may a DateTime. Doing this will also rerun any Initial Value formulas.

An issue with no2 is that I think each bot is only allowed 300secs to run. So you may have to have it run repeatedly to get them all to update.

I think doing it this way and having hard coded URLs will give you the best sync time.

Thanks a lot! My app is still in development so making changes like this is still relatively easy.

One difficulty associated with working out all of theURLs in advance is that some of the records have alternate spellings (British vs. American, etc.). So, I thought putting the URL generating expression in the actions would avoid the virtual column, be more efficient, yet still be able to accommodate different spellings. Is the solution I’m imaging not likely to make a positive difference?

Not sure what you mean

Sorry for not explaining properly.

I think your approach is to write complete URLs into the spreadsheet – the hard coded URLs you mentioned. Currently, that’s not what I’m doing. I’m constructing the URLs in virtual columns, which I suspect is inefficient.

I am interested in the idea of writing complete URLs into the spreadsheet but I think there are two issues:

  1. The data is arranged such that the main category for the word in the app is either a single word, such as “anywhere,” or a something link “apologize/apologise” where two spellings are represented. Where there is an alternative spelling, the app lets the user chose one of the spellings. Because I’m currently using virtual columns, the URL are immediately adjusted to the spelling that was chosen. If I write URLs to the sheet, I need to figure out how to handle that issue.
  2. This second issue is less important but I can imagine situations where a user would change an entry after typing it in to the app. This would require an associated change in the URL. With 25 URLs that would be a lot of rewriting that would have to be done.

I got a response from @Steve that makes my think I may need to write the URLs to the sheet, as I think you are indicating. Since the URL can change depending on which spelling of a word that the user chooses, I’m thinking of always writing the URL to the sheet as part of a group action before that URL is used to open a web page. I wonder if you think that would be an appropriate strategy.

Yes otherwise if you use virtual columns, for say 3 different URL formulas per word. Then each time you sync the App is going to have to recalculate 3 x 8000 = 24,000 formulas

Could you make this work so that each spellling is its own row?

With my method this wouldn’t be an issue as editing a row would caused the formulas to be recalculated

Yes, that would best. Have the URL in the spreadsheet and then either display it in the App or call that column from a Show type column

Thank you very, very much for your help. I’ve already begun to implement your suggestions. It will take me a while to finish, though, because I have so many such links. Thanks again!

P.S. I was a little surprised that putting the URL expression directly in a hyperlink action was deemed by @Steve to be a bad idea. I guess that means that hyperlink actions behave more-or-less like virtual columns do. It’s interesting to me that an action that writes a URL to a sheet using the very same expression would be better. I guess that’s because writing actions are only invoked when the writing actually needs to be done whereas hyperlink actions always need to be ready and waiting . . . if I’ve got that right .

Not are direct topic in terms of hyperlink and action to open URL externally, how to improve the performance.

My suggestion in general to improve app performance is to review the REF column if you have. When we set the row to REF, then naturally we have reverse reference column in parent table, which is slowing down the app performance. This will generate the inline view on Parent table, detail view which is no doubt useful, but on some case, we dont need such reverse refereren.
On the growing app, we may have various master tables which are connected to other table. We will see loads of parent/child relationship, which is createing reverse reference list type row here and there naturally, which is running bunch of calculation on sync.

We recently review the app, and change such a REF column over to ENUM based type ref instead of pure ref type column. At the end, we will not have reverse reference VC on parent table, which is fine, but at the same time, it will be reducing sync time as three is no VC to recalculate on each sync.

To see the possibility to improve your app performance in general, I would suggest you review your app, just find REF column. If you dont need reverse reference, then change them over to Enum base type ref. This will definitely help your app improve the performance based on our experience.

This particular app of mine (the one I’m working on now) doesn’t have ref columns but I can see how this could be an important issue.

Sure.

Hi, I tried this out in a test app and it was successful in removing unnecessary related fields.

However, I am receiving this warning in the app now. Is there any major impact from it or is there a way to get rid of the warning altogether?

You need to generate a list in either app formula or suggested value . You are lacking in the list of values for enum after change it over from ref

I’m bringing the list of values in through the Valid If
3X_f_9_f9756c7c711b884b1d37c8ca6379f5edb10407f3.png

3X_d_2_d2e74bce85ff105c4540034ed71edbe42f188d01.png

The dropdown is populating correctly, but I’m still receiving the warning

Read my previous advice

SkrOYC
Participant V

This topic is great. I would love to read more info related to performance improvement also related to the expressions we use (like, why select() is so hated) and things like that.
Anyone have a good resource of this?

The only thing I know to do is search this community. I hope someone else can point us to a good resource.

Top Labels in this Space