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?
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.
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.
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.
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.
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.
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:
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.
Searching the whole App can be done with the App Documentation.
Great clarification Steve!
Thanks for making it.
@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:
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.
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!
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.
@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.
Thanks for the encouragement, @Suvrutt_Gurjar! Hereโs a tip I wrote based on this discussion:
It only summarizes one idea, not all of the good ideas that have been shared here.
User | Count |
---|---|
43 | |
26 | |
24 | |
14 | |
12 |