Appsheet Apps Speed

sorin_mihai · 11-02-2019 08:38 AM

Hello everyone

I have a few questions about Appsheet Apps Speed, just to know what is normal and if there is something specific to my situation that I can do to speed up the user experience through the app.

So I have an App made now, that uses one Google Spreadsheet that has 15 tabs out of which only 5 have more that 10 rows and 5 columns (they are the only tabs collecting data, the rest are categories, users etc.)
I would say this is a small or at most, medium sized App (let me know if I’m wrong).

The App I made is now used on the Free plan with 7 Users. We’re building it up as a Task Management app for Teams + Overviewing Clients and Managers.

The app itself is quite intricate in the way it uses these tables and it has a bunch of virtual columns added and so forth, but my following observations about it’s speed were true ever since the Spreadsheet was mostly empty, so I don’t think calculation time is really relevant here.

It takes about 5-6 seconds when opening the App on a mobile device until you get to the default view.
It takes about 6-8 seconds on a sync (it takes longer to sync after changing data, but that would be expected)
It takes 2-3 seconds to move from a View to Another on a Mobile Device of any User using the app. This is true both for Android and Iphone Users.
(as an observation here, it does take slightly less navigating the Views in the App Builder on Chrome browser on the PC)

Out of these observations, it’s the Third one that bugs me the most, the others are fine as they are expected. But I didn’t really expect a 2 seconds wait every time you touch a menu, a tab a view or a form on the Mobile Device.

Is this normal? Is everyone else here experiencing the same thing?
Is this about the Plan I’m on? And it wouldn’t have this delay if the App would be Deployed?

Or can I still hope to have more instant movement through the App, within these conditions I’m running the App in right now? Because the App is great and Appsheets functionality is amazing and I can’t imagine being able to do what I’m doing with Appsheet … with mainly anything else out there
But I’m really curious about the cause of these slow interactions with the Apps build in it. This doesn’t make much of a Time Management App if it takes two second to click on anything in the App.

Thank you,
Sorin

TDhers

Hi Sorin,
No 2,3 seconds seems excessive but it could depend on other factor. Mostly how much data you load and in particular whether there are a lot of virtual columns that needs to be computed and if the data is setup such that you need to do a tons of join at data query time.
So if most of your tabs are short list, I suspect these are lookup tables type thing and doing joins on spreadsheet is not as efficient as doing it with relational DB that are optimized for this.
Could I suggest you try two things.
Replace your lookup table REF things with loading your lookup tables as enum column instead and entering these values once in your enum list.
The second thing I would try is to remove virtual columns and reads them one at a time to see if one in particular takes a long time to compute.
That should give us some first indications as to where the data is spent? Data joins or virtual column expression or elsewhere if neither of these suggestions have influence on the perf.
Hope this helps.

Thierry

pravse

Often, the root cause of slow transitions between views is an inefficient Format Rule (a format rule with a SELECT in it can be very inefficient). I’d suggest look there first. The other cause I’ve seen is very large data sets, but that doesn’t seem to be your problem at all.

sorin_mihai

Hy Thierry,
Thanks for writing.

Sorry, what does that mean?

So lets take one example:
There is a Projects Table and a Category of Projects Tabel. The projects Table is one of the Long Sheets that keeps gathering Projects as we make them. The Category of Projects is a short Sheet, with 4 Categories. Besides the Name of the Category, there are other Columns as well to this Sheet. When creating a new Project, there is a Dropdown to select the Category of Projects, which is indeed a Ref column to the Category of Projects Table.
What is the Change you suggest I make in such cases?

Well is there a way to make them inactive? I’m kind of afraid of removing them as it took quite a while to make them …

But you seem to indicate this is a calculation time issue … I don’t know that “too many” means, I doubt I got to that point :)) but then again, to make sure, I guess the easiest way to display the amount of connections is to show you the Spec images:

So this is everything in there:

And this is an explenation of it all:

So for the main part of it, there is as I said, quite a number of connections, as the App has mainly a clear Hierarchy - There are Category of projects, Each heaving Many Projects, each Project can have many Objects, Each Object can have Many Tasks, each Task has Many Times, which are filled in by the Users.
The connections I made so that it’s very easy to navigate from any place in the Hierarchy to any other part of it, within that Project … but then again, it does look like this:

As for Virtual Columns, I use them a lot for displaying purposes, where they concatenate the info in other column, with up to 5-6 IFs. I’m happy to try them out one by one and see if there is one in particular that is using too much computing time, but I can’t remove them

Thanks,
Sorin

sorin_mihai

To test the virtual colimns, I’ll try not displaying them, maybe that will make a difference and then start displaying them one by one and see if there is a change in speed of load of different views…

Steve

This will be a problem if it isn’t now.

Rightly so! Yikes!

I agree with this.

I suspect he’s suggesting you replace Ref columns with Enum columns of base type Ref or Text. Doing so will prevent AppSheet from auto-generating the Related … virtual columns with reverse references (see Reverse References in References Between Tables). Because these virtual columns are recomputed at each sync, the expectation is that they can contribute to slow sync times, especially for complex interrelationships between tables. According to @praveen’s comments in another post, though, reverse references typically don’t contribute significantly to sync time, so it’s unlikely to help.

I would expect hiding them would have no significant effect, as virtual columns are computed at sync time, not on display. Format rules, however, are computed at display time (see here), hence @praveen’s suggestion.

sorin_mihai

Well, also they are mostly concatenate and if formulas, not Select, as praveen says they are the ones that might take up the time … so it sounds like those wouldn’t be the problem.

How do you mean?

Steve

Each virtual column is a linear multiplier to your sync time: the time to compute one row’s virtual column values times the number of rows. The more rows, the greater the effect on sync time. I generally prefer to keep as much data in normal columns as possible to avoid contributing to sync time.

sorin_mihai

Aham, so you suggest that if I can actually make what the virtual column does, be done in a column with a formula in the spreadsheet and then display that column in appsheet, that would make things run faster right?

Because I could do that for the vast majority of my virtual columns…just didn’t know that would be part of best practices logics with appsheet…is it?

pravse

@sorin_mihai I am 99% certain your perf issue (delay in transitions between views) isn’t due to virtual columns or refs. it is most likely due to format rules. Virtual columns are computed at sync time. They are not computed during view transitions. However, format rules do have to be computed during view rendering and scrolling.

It sounds like you do have format rules. Could you try to disable all of them and see if your performance improves?

sorin_mihai

Hy,

Well, I do have about 24 conditional formatting rules. Disabling them definitely has a positive impact on time. Now it takes just a bit under a second. It’s not instant but it’s faster.

However, taking all of them out, takes me from this:

to this:

Is this a choice I have to make? Speed vs. Ease of read?
Would you say there are certain Format Rules Guidelines to follow so things don’t go so out of hand? Or simply I just have too many?

Also, as an observation, on the Chrome Browser now, after disabling the formatting rules, the app reacts instantly to change of views. So there is some dependency on the computing power, if the mobile app is still a little slower then the Chrome Browser on a PC.
What would you suggest is still making that difference?

Thank you,
Sorin

Steve

Of course this is true.

I would suggest doing as little computation in the format rule as possible; try to do the computation in columns. You might also consider whether you need all of your rules. For instance, you appear to have two indicators that a row is done: a green checkmark and strike-through text. One or the other is probably sufficient. But if you need both, and both are computed by the same expression, find a way to compute the expression just once and reuse its result.

WillowMobileSys

Also, in case its not totally apparent, you can combine multiple formatting “actions” into one rule.

For example, It seems that your example screen view above can be accomplished with 3 format rules

Bold and underline - top row
Circle icon, green highlight, Bold text and underline - for second row.
Check mark icon, green highlight, text strikethrough - for remaining rows.

Then its just identifying the criteria to apply each rule.

As @Steve and @praveen have suggested above, it is likely the complexity in the expressions for these rules that are slowing things down.

And as @Steve mentions, the more “pre-processing” you can do to eliminate that complexity before the format rules are applied, the better for your entire app. This may mean having columns on each row that serve no other purpose than calculated indicators for that row that you can later test for “IS” or “ISNOT” conditions. A few split-second calcs sprinkled through the app are a LOT more acceptable to end users than the a 2 second delay to switch screens.

sorin_mihai

That’s true, it makes a lot of sense.
Some of those conditional formatting rules are paired as you suggest, but now I see I can do even more to take the calculations out of the Formatting Rule and into the spreadsheet.

I’ll definitely do that ASAP.

WillowMobileSys

Be careful with adding a lot of formulas into the sheet. That can elevate wait times in the back end. When formulas are firing in the sheets, AppSheet cannot know if the results of those formulas effect data they are trying to retrieve, so they wait until formulas have completed. I’m sure this is over-simplified but the point is the more formulas in the sheet the more wait time AppSheet might have to endure.

If at all possible I would perform these calculations in the app as data changes.

sorin_mihai

Ok, so best case scenario:

no formulas in the spreadsheet,
only IF formulas in conditional formatting, and the fewer, the better.
real columns in the Spreadsheet, with formulas in Appsheet to calculate their values,
and conditional formatting based on the value of these columns (no calculation necessary, just if statements)

Does that sound correct?

WillowMobileSys

In my opinion yes, that is best case scenario.

But, of course, if we are creating apps of any complexity, the chance of achieving 100% of the best case scenarios is slim to none!!

sorin_mihai

Hy,

So I have a question/observation.

Is it true that:

Normal column formulas, don’t update their calculations and their results based on updates other then through the Form of their Table.
Virtual column do.

What I mean is this:

I have a Table called Objects, a table called Tasks and a Table called Times. Users add Times to Tasks so Tasks are Referenced in the Times Table. And to create Objects you complete Tasks. So Objects have many Tasks, Tasks have many Times.

I have a column in the Tasks Table - Task Progress - that can be Not started - when there are no Times Related to that Task, Started, when there are Times Related to it and Completed, when a User gives it that status via an Action Button.

I want to have a column - Object Progress in the Object Table that does this:

when an Object is just created, meaning, it has no Tasks Related to it yet - it says “Incomplete”;
when Tasks have Times, so they are Started, but not all Tasks are Completed - then it says still “Incomplete”,
when an Object has all of it’s Related Tasks Completed, than it becomes as well "Completed"

Now I have done so in a Virtual Column and it works perfectly, Using this formula:

IF(
OR(
COUNT([Related Tasks])=0,
OR(
IN(“Not Started”,[Related Tasks][Task - Progress]),
IN(“Started”,[Related Tasks][Task - Progress])
)
),
“Incomplete”,
“Completed”
)

But curiously, if this formula is in a Virtual Column, if I make a new Object (it is now Incomplete), then add a Task to it and then add Times to that Task (still incomplete) and then mark that Task done, the Column, upon sync updates to Completed.
This is not the case for a normal Column. After it takes it’s first value at the creation of an Object - (incomplete) it keeps it, no matter what I do with it’s related Tasks and it Related Times.

So if this is true, then the conclusion is that, for column that need to update based on updates in other Tables then their own (i’m or sure I’m getting this right, please correct me), you will need to work either with a Virtual Column, or a formula in the Spreadsheet Column.
And coming back to our previous discussion, both were to be avoided in a best case scenario.

Also, if it were to be done with a formula in the Spreadsheet, how would you translate into Spreadsheet Formula this part: IN(“Not Started”,[Related Tasks][Task - Progress])?

So are there any other more elegant ways of solving the above example?

(I’ll make a new post with this question as well)

Thank you,
Sorin

WillowMobileSys

You are correct, the App Formula in normal columns do not fire unless activated by “touching” the record in a Form.

I had this problem with Sum amounts on a Parent record when a child was edited directly - not through the Parent. I fixed that problem by forcing navigation to the Parent Form upon saving of the child.

In your case, It sounds to me like you are attempting to keep the “status” of your Objects record updated. I would recommend using an Action to keep the status updated and then call that Action from anywhere changes are made that could affect the status.

I have a similar need for status of Work Orders. I want to track progress through the workflow from New to Pending to Technician On Way to Working to Done, maybe even Cancelled.

I created a single Action that inspects the Work Order state and sets the status of the Work Order. This Action is called from anywhere a change can be made that could change the status - even Workflows.

This does mean you would need to add custom Actions on Form Saved Behaviors as well as make Grouped Action sets anywhere that Actions can impact the status.

sorin_mihai

That’s an interesting ideea as there are not so many places to place that action.
Thanks for the insight

pravse

My general feedback is to go easy on format rules, both for a good user experience and also for performance. Expressions in format rules should be simple — avoid SELECT or its derivatives (like FILTER and MAXROW)

I’d be curious to see your app and figure out if there are obvious ways we could improve its performance. Do you mind sending the details to support@appsheet.com. Thanks

sorin_mihai

Hy @praveen

Thank you, that would be amazing to get some feedback from you.
I have sent the email to support already

I have two warnings though, and please let me know how I can help:

The whole app is in Romanian …
This is my first Appsheet app … and only one so far. I was as clean as I could be but it’s still a work in progress and a learn by testing App as well :))

I don’t know if I crossed the Frankenstein app line so far, but some of the parts of the app, as I showed in the Specs, are in the making, and about to get connected to the main app.

Thank you and can’t wait for your feedback.

Rifad

@pravse wrote:

go easy on format rules, both for a good user experience and also for performance

Just confused. By mentioning 'Performance' does it mean an expensive formula effect Server side sync performance or User side UI performance ? I have read your post somewhere mentioning format rules are calculated on the Context of a view or is it calculated during a sync and it effect sync time ? @pravse

@Steve any inputs on this will be helpful.

Format Rules: The Essentials - AppSheet Help

Here it mentions "computation each time a screen is scrolled can affect the app's interactivity and performance" but not clear about which performance it mentioned.

Steve

Format rules are client-side only and do not affect sync time at all.

Rifad

Then why is AppSheet support mentioning this kind of things when we approach them with actual problems? They are misguiding us just to close a ticket each time.

I contacted them because of slow sync speed and I had a format rule. They told it’s because of that app sync is slow. I asked them how and they mentioned that is what shown in audit log.

The kind of issues we face and the replies they give is confusing to a user. As an experienced person they got me confused.

Lynn

Hi @sorin_mihai Maybe you could use slices to show the done work instead of formatting.

sorin_mihai

Hy,

I do use slices. The one in the example is a slice of Times added to Tasks and they represent All Tasks in that list.
But I have slices for My Not Finished Tasks for example, and the conditional formatting rules are for - tasks started/not started, tasks for today, urgent tasks. These could not go into smaller slices, it’s practical to have everything that is in progress display in the same list, with highlights to their particularities.

I will try to reduce them as much as I can now that I know maybe I got a little over-enthusiastic about learning Appsheet and conditional formatting rules … I do that usually in order to learn something in more depth, but … now I just learned something new so I’ll adjust.

Thank you,
Sorin

Steve

@Rifad wrote:

Then why is AppSheet support mentioning this kind of things when we approach them with actual problems?

I know you already know the answer: because Support agents are woefully unqualified.

Rifad

There is no solution to actual problem instead they give us additional confusion to recheck myself if I’m wrong.