Performance improvement tips

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

2 Likes

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:

1 Like

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.

1 Like

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.

3 Likes

@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

1 Like

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

2 Likes

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 :slight_smile:

Simon@1minManager.com

6 Likes

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!

1 Like

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?

1 Like

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.

1 Like

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

1 Like

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 :confused: .

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.

5 Likes

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.

1 Like