Workflow rules and sync speed

[Kirk_Masden]

Today I made a workflow rule. The work the rule was designed to accomplish was to write data to one cell in the sheet. The basic condition was “whenever data on a table is changed” but I added a “If this is true” condition (A <> B) that would only lead the rule to be invoked about once a day or so. Still, it seemed to significantly slow the background sync process. By that I mean that when I built up a number of data changes that needed to be written to the spreadsheet, the pace at which those syncs occurred in the background was quite slow. So, I gave up on my workflow rule and embedded actions elsewhere in the app to do the same work. For now my problem is solved and my app is working well.

Here’s my question: Is my experience typical of workflow rules that are invoked whenever a sheet changes? Was I doing something wrong or should we avoid workflow rules when possible because of the danger of slowing our apps down?

[TyAlevizos]

What was the data source type, Kirk? And depending on the exact condition statement, there could be expected slowdown while it gets the result back.

[Kirk_Masden]

Thanks @TyAlevizos! It a Google spreadsheet, in a “book” with quite a few other sheets. The sheet I set to “whenever data on a table is changed” is not huge but the “book” of Google spreadsheets has gotten pretty big.

The condition statement was fairly simple. “Is the date, which has been written to another table, current?”

As I wrote, I’m happy with the work around I’ve put in place (using actions with the exact same condition) but just curious about how workflow rules work.

[Steve]

Phil:

The only exception is if the workflow operation is asynchronous.

For readers who may not know, an “asynchronous” operation is one for which the app doesn’t wait for completion. Email and SMS are good examples: the app doesn’t wait for them to be delivered. A data change operation, though, is not asynchronous: the app waits for all data changes to complete before proceeding.

[Phil]

Hi Kirk,

Normally workflow is reasonably efficient. However, any time spent performing the workflow operations contributes to the elapsed time of the add, update, or delete operation. The only exception is if the workflow operation is asynchronous.

When you have a performance problem, I suggest going to Manage > Monitor > Performance Profile and taking a close look at the Performance Profile results. We capture the time required to perform each step of the add, update, or delete and of the workflow rules they trigger.

I have spent a lot of effort to make the performance measurements accurate. I attempt to capture the elapsed time down to a resolution of one to two milliseconds. Admittedly the detail can be overwhelming, but the benefit of the very fine grained measurements is that thy normally reveal exactly where the time is going.

Often you will find that the time is being consumed computing virtual columns or expressions. This is especially true if these computations require that we read other tables when computing the virtual column or expression. Armed with this information, you can sometimes improve your expressions to make them more efficient.

[MultiTech]

Phil:

I have spent a lot of effort to make the performance measurements accurate. I attempt to capture the elapsed time down to a resolution of one to two milliseconds. Admittedly the detail can be overwhelming, but the benefit of the very fine grained measurements is that thy normally reveal exactly where the time is going.

No amount of thanks can be given for this level of detail. Thank you thank you thank you!!!

You don’t know how many times this has solved a riddle… or saved my ass. Having a paper trail like this allows you to debug with confidence.

[Kirk_Masden]

Phil:

you will find that the time is being consumed computing virtual columns

Yes, @Phil! I did indeed find this. Because I’ve been doing a lot of “seat of the pants” or “trial and error” app tinkering, I think I have quite a few virtual columns that could be culled. The problem I’m having now, though, is that I don’t remember exactly what is connected to what and so I hesitate to just erase virtual columns that I suspect I may not need. In order to get rid of those superfluous virtual columns I’d like to be able to search for columns and actions that may depend upon them. Google sheets allows us to search within formulas. Such a feature would be a great addition to AppSheet and I think it would complement the performance profile function you have done such good work on for us.

Here’s a feature request I made in this regard:

Better search capabilities within the AppSheet editing interface Feature Requests

As AppSheet has grown, it has become possible to develop apps in which columns, actions, and expressions interact in increasingly complex ways. The ability to build more complex apps is great but it can be very difficult to figure out what is connected to what when one returns to an app after some time has passed. We can use our browser’s search function in some contexts to solve such puzzles but the current search function is extremely limited. So, here’s my proposal: Add the capability to …

For me, a simple text search that covers all of the text in the expressions throughout the app (and the column names they invoke) would be a big improvement. In an ideal world, it would be great for AppSheet to have a “deadweight” search function – a function in the performance profile that suggests virtual columns that can be deleted – but I realize that that would be harder to put in place.

[Fabian_Weller]

Searching the whole App can be done with the App Documentation.

Searching the whole App Tips & Tricks 💡

If you like to know where in the App you are using a column, you can use the App Documentation. [image] In this you can search for text. For example. You have created a Virtual column and you think you could delete it. But … perhaps you have used it in a workflow rule? Or in a format rule? Simply search for the column in the App Documentation to find out where it is used.

[Phil]

Great clarification Steve!
Thanks for making it.

[MultiTech]

@Kirk_Masden I too have run into this problem, but it was typically with an app that was coming close, or spilling over (^_^), from data bloat.

My solution was the same, move the data edits “app-side” and just have them mixed in with action stacks, form saves, etc.

I’ve had to explain things like this to my more keen-eyed users; they’ll notice that they save a form (what they’re thinking should be one sync cycle) but they see three down on the little Sync button.

In general I think of things like this:

• When I save a form, that’s one thing that needs to be done.
• If I add other actions in there, depending on the action, that could add several steps for calculation and additional edit save.

So for every 1 thing, now we have at least 2 (probably more like 4 or 5), and the time it takes for everything to happen… grows.

If you have a lot of edits, with a lot of data for those calculations to pour over… there’s a lot more things happening - and the device doesn’t have more capacity than it did before - so things take longer.

---

It’s a balancing act; I’ve got one app where edits are thrown into form saves, along with other that run on timers once a day (just before work).

I lean towards Action edits, for what it’s worth.

[Kirk_Masden]

Thanks to @Phil, @Steve, and @MultiTech_Visions for your very, very helpful explanations. I’ve been around for quite a while so I should know better but I haven’t been in the habit of looking carefully at the Performance Profile. I’ll start doing that and look for ways to make my app more efficient, though it seems to be running reasonably well at the moment.

If I may, I’d like to ask for confirmation on one more point. In my flashcard app I use a couple of sheets that are not tables in my app to slice, dice, and then summarize moderately large quantities of data that the app collects. Recently, I found that a now() formula, which was referenced by many cells was causing the Google sheet to show its “wait” bar fairly often. Here’s the point I’d like to confirm in that connection:

I suspect that AppSheet syncs wind up waiting for G sheet recalculations and that an inefficient or bloated G sheet will slow down the sync process. Am I right about that?

By the way, since I only needed the now() formula to tell me the day and not the exact time, I made an AppSheet action to write it to the sheet, which avoids constant recalculation. That was the action that was originally triggered by the workflow rule but is now embedded elsewhere in the app.

Thanks again!

[MultiTech]

Kirk_Masden:

I suspect that AppSheet syncs wind up waiting for G sheet recalculations and that an inefficient or bloated G sheet will slow down the sync process. Am I right about that?

Yup. So after each update-sync, appsheet… wait for it… wait for it… wait for it… only sends the next update after the spreadsheet formulas have recalculated themselves.

Your solution of using an action to write that value into a table (essentially “storing” the variable) is exactly what I would have done.

[Suvrutt_Gurjar]

@Phil, @Steve, @MultiTech_Visions, @Kirk_Masden,

This entire discussion thread is very informative. Thank you all for useful insights.

I believe such discussion threads are apt being reclassified under tips and tricks.

There are many useful insights into synchronous/asynchronous concept, performance monitoring, virtual columns usage, tips on managing edits.

Thank you all.

[Kirk_Masden]

Thanks for the encouragement, @Suvrutt_Gurjar! Here’s a tip I wrote based on this discussion:

Sync slow? Your GSheet might be the problem! Tips & Tricks 💡

If your Google spreadsheet simply holds your data and you can do all of the calculations you need to do without difficult via AppSheet, then you can skip this tip. If, one the other hand, you use your app to collect fairly large amounts of data, then use extra Google sheets to do some back-end number crunching, keep reading. [loading_bar_2] This gif of Google sheets recalculating is taken from the following page by Ben Collins: I recently had a problem of this type and my solution what to …

It only summarizes one idea, not all of the good ideas that have been shared here.