Questions about workflow performance

[Rafael_ANEIC-PY]

Hello friends ! I have recently started getting rid of all the virtual columns i could in my apps, sparing only the ones having ref_row expressions, and replacing the rest with workflows that would recalculate the columns i need to recalculate only when it is needed, that worked great at first, until…

It became slow

Or at least slow enough to take more time than a sync does, so for example if an user made a change to a child row, a workflow would trigger an action that updates the value of a column in the parent row, but if he syncs manually after the change, the value is not updated.

That gives the impression to the user that the app is broken, since he synced a change and he’s still seeing an old value in the parent table, he won’t see the new value until he syncs again, which is problematic.

so here are the questions:

• How can i improve workflow performance?
• Has anyone experienced what i have described above?
• Do i have to apologize to my former virtual columns and beg them to come back to my app?
• Do i have to choose between the virtual columns murdering my sync times or my workflows killing the user experience?

BONUS QUESTION: Would a webhook do better in this use case?

[MultiTech]

Rafael_ANEIC-PY:

• Has anyone experienced what i have described above?

@Rafael_ANEIC-PY you’ve uncovered the major difference between workflows and actions

• Workflows are a sort of “passive” way to update data - it happens in the background (so to speak), only getting the results after a sync happens
• Actions are an “active” way to update data - it happens immediately.

All your other questions are valid and solid, the answer is pretty much the same for all of them:

• You’ll need to re-work your data structure in a more efficient way.

NOT an easy task sometimes

I imagine in those VCs you’ve got SELECT() formulas of some kind? In a well designed data schema, you’ll have very little need to make a hard-pull using SELECT() or LOOKUP() because the data you want, or the subset of records, already exists and it’s just a matter of calling what’s already there.

---

Some key AppSheet-skills that will help

• One of the key AppSheet-skills to learn is how to use Slices in conjunction with Ref_Rows() to create subsets of data.

  • It’s through these that you accomplish efficient versions of your SELECT() formulas.

• Another key skill: know when you can store a value in a physical column vs. when it needs to be a virtual column

  • Many times, the values you’ve got computed inside a VC could actually be stored inside the actual table.
    • If the VC-value only changes when data from the row changes - it can be a physical column

---

Awhile ago I put together a document outlining the key concepts I believe one needs in order to master the AppSheet platform; you can read it here.

---

Unfortunately, AppSheet doesn’t plan on adapting anything to make the results of workflow data changes appear faster inside the app.

You can read more about it here:

How will actions and buttons be rolled into Automation AppSheet Automation

It seems that the new Automation panel is essentially a re-work of the workflow/report panels. I’ve noticed that any processes I create are enacted like a workflow would be; meaning: it’s not until the record is synced with the server that the process fires off (like a workflow). Not like a button: where when I press it, the change is seen immediately. It seems that if you want THAT sort of behavior, you’re still gonna be doing everything with actions.

[Austin]

MultiTech_Visions:

Awhile ago I put together a document outlining the key concepts I believe one needs in order to master the AppSheet platform; you can read it here.

Side question don’t wanna hijack the post, but I see you as a quintessential Community approved expert for Appsheet. When you make these guides and outlines, I assume you use a gsheet based app as the example of your guides? Most of the skills are essential in any datasource but some do depend on what datasource is used for what is optimal. For example I use a MySQL database so I try to make any action I can into triggers since my database is exponentially faster than using actions or workflows when I need lots of changes.

[MultiTech]

Austin_Lambeth:

When you make these guides and outlines, I assume you use a gsheet based app as the example of your guides?

I do; though I have clients that are utilizing SQL servers as their data sources, the majority of people using AppSheet would fit into some classification of Citizen Developer.

• They’re more comfortable creating a spreadsheet formula that integrating a SQL server trigger.

So I generally come from things from a Sheets stand point; but the best practices from the SQL-world still apply to the sheets world - they actually make a world of difference.

---

Austin_Lambeth:

You might have to choose a blended approach and figure out a balance that will always show the most up to date information but be a bit slower than the fastest method.

Good advice! Some items you need to update rapidly: sometimes I’m forced to create a “heavy” formula (some SELECT() formula) in order to make a formatting rule apply faster, instead of waiting for the sync to happen.

[Austin]

Rafael_ANEIC-PY:

Would a webhook do better in this use case?

I assume Appsheet apps work via webhooks to their own API so I would guess the answer is no.(Don’t quote me on this answer)

Rafael_ANEIC-PY:

Has anyone experienced what i have described above?

I could’ve sworn that Appsheet would self delay if you had a workflow running when a user syncs. We had a massive database trigger that was so poorly made it took 10 seconds to run and it was just added to the sync time of our app and wouldn’t finish syncing until the trigger had finished.

Rafael_ANEIC-PY:

Do i have to choose between the virtual columns murdering my sync times or my workflows killing the user experience?

You might have to choose a blended approach and figure out a balance that will always show the most up to date information but be a bit slower than the fastest method. Some things you can also do to avoid this is making smart VC formula that return nothing essentially unless you are in a place where they are needed.

[MultiTech]

I should note: there is a place and use for both workflow and action-based data updates.

Action-Based Data-Changes

• If you need the value immediately inside the app, the users need to see it and use it, then it should be accomplished through action updates.
• The “downside” to this update method is that every update is made locally: it uses the devices resources (RAM, processor, etc.) to make the calculations, so you’re limited by it’s performance
  • Running complicated SELECT() formulas, with lots of stuff to consider and parse through, will result in a hiccup in your app (it will freeze while it’s doing calculations).
• It produces an “update record” (so to speak) for each update made, that then needs to be synced with the data source one at a time.

Workflow-Based Data-Changes

• If you don’t need to see the update immediately, or if you’re making a large number of changes (such as updating a group of records, deleting child records from a parent delete, things where there are a bunch of updates to be made), then these are typically better placed inside a workflow.
• The calculations are accomplished through the server, so device resources aren’t an issue
• There is only 1 “update record” to push to the system
  • however, that one update will take much longer than a normal update

---

[Austin]

Another probably even more important factor is how expensive is the SELECT() and what constitutes a murdered sync time? I put some thought into what formulas I use for things but I generally don’t put any thought into SELECT() or LOOKUP()s. I don’t know if I just don’t the same level of data or if we just have enough of an expectation of it being slow to not worry. For our large more system level apps we are fine with 10-30 seconds of sync depending on how much manual syncing our users will need to do. Smaller lighter apps we try for 5-10 seconds cause they’re less of a I use this for 8hrs a day 40hrs a week and more I use this once a day.

Might sound counter intuitive that we want small apps to be faster at syncing but its the time spent in the app vs sync time that really seems to matter for our users.

[Rafael_ANEIC-PY]

Thanks for all the interesting answers guys, i’ll answer everything one by one

---

MultiTech_Visions:

I imagine in those VCs you’ve got SELECT() formulas of some kind? In a well designed data schema, you’ll have very little need to make a hard-pull using SELECT() or LOOKUP() because the data you want, or the subset of records, already exists and it’s just a matter of calling what’s already there.

Actually no, i’ve managed to dump all my SELECT() formulas into my workflow template, the exact expression im trying to optimize is:

+SUM([Related items certificados][MontoCert])
+SUM([Related Anticipos][Anticipo])
-SUM([Related Anticipos][Devolucion])
-(SUM([Related Descuentos][Total gravado])/1.1)
-SUM([Related Descuentos][Total exento])
-SUM([Related Retenciones][Retencion])
+SUM([Related Retenciones][Devolucion])

there aren’t that many child records involved, but every parent row has several children tables, that’s what makes me so paranoid about just using it in a VC instead.

---

Austin_Lambeth:

You might have to choose a blended approach and figure out a balance that will always show the most up to date information but be a bit slower than the fastest method. Some things you can also do to avoid this is making smart VC formula that return nothing essentially unless you are in a place where they are needed.

Got any links where i can learn more about those smart VC formulas? hehe

---

MultiTech_Visions:

I should note: there is a place and use for both workflow and action-based data updates.

Yes, and that’s where my mistake is right now, i tried action based data changes but since i didn’t like having double updates i tried the workflow based data change approach thinking i could get almost the same result, my bad.

---

Austin_Lambeth:

Another probably even more important factor is how expensive is the SELECT() and what constitutes a murdered sync time? I put some thought into what formulas I use for things but I generally don’t put any thought into SELECT() or LOOKUP()s. I don’t know if I just don’t the same level of data or if we just have enough of an expectation of it being slow to not worry. For our large more system level apps we are fine with 10-30 seconds of sync depending on how much manual syncing our users will need to do. Smaller lighter apps we try for 5-10 seconds cause they’re less of a I use this for 8hrs a day 40hrs a week and more I use this once a day.

Well, at this point i feel like a sync maniac, my app has a pretty smol database as i’m still testing it yet, but i’m already thinking about how it would hurt to have a couple of virtual columns once i start hitting a few thousand rows.

---

pd:

Austin_Lambeth:

(Don’t quote me on this answer)

I just did

[MultiTech]

Rafael_ANEIC-PY:

+SUM([Related items certificados][MontoCert])
+SUM([Related Anticipos][Anticipo])
-SUM([Related Anticipos][Devolucion])
-(SUM([Related Descuentos][Total gravado])/1.1)
-SUM([Related Descuentos][Total exento])
-SUM([Related Retenciones][Retencion])
+SUM([Related Retenciones][Devolucion])

This is an efficient way to do it.

Rafael_ANEIC-PY:

my bad.

No bad here…

---

Austin_Lambeth:

its the time spent in the app vs sync time that really seems to matter for our users.

This has been the same I’ve found as well, which has really caused me to overlook a lot of possibilities that can be had with workflows. But my users need their updates NOW, they don’t have time to wait for the app to sync - they just want to be able to use it - so I’ve focused on build methodologies centered around in-app updates.

(All this technical stuff… it’s like static noise to them, all they hear is KSHSHSHSHHSHSHSH)

[Rafael_ANEIC-PY]

MultiTech_Visions:

This is an efficient way to do it.

Well then, to conclude our debate about virtual columns…

---

MultiTech_Visions:

This has been the same I’ve found as well, which has really caused me to overlook a lot of possibilities that can be had with workflows. But my users need their updates NOW, they don’t have time to wait for the app to sync - they just want to be able to use it - so I’ve focused on build methodologies centered around in-app updates.

I can see myself going that path as well haha.

[Austin]

Rafael_ANEIC-PY:

+SUM([Related items certificados][MontoCert])
+SUM([Related Anticipos][Anticipo])
-SUM([Related Anticipos][Devolucion])
-(SUM([Related Descuentos][Total gravado])/1.1)
-SUM([Related Descuentos][Total exento])
-SUM([Related Retenciones][Retencion])
+SUM([Related Retenciones][Devolucion])

OH ya I refuse to use a SUM or COUNT formula at any possible step in appsheet if I can because they suck SOOOOO bad for sync time.

[Rafael_ANEIC-PY]

Austin_Lambeth:

OH ya I refuse to use a SUM or COUNT formula at any possible step in appsheet if I can because they suck SOOOOO bad for sync time.

Is there an alternative?

[Austin]

Database views or calculating them when they change by assigning them to a real column.

[Rafael_ANEIC-PY]

Austin_Lambeth:

Database views

My database knowledge level is potato right now

Austin_Lambeth:

or calculating them when they change by assigning them to a real column.

Hmmm, calculating them as they change…

How would you do that? as these are several different lists of child rows

[Austin]

Would basically be a workflow that triggers on add delete or update for any of the child rows and it would do an action on the parent to recalculate.
There might be a way to do it in gsheets formulas but we have never used gsheets.

[MultiTech]

Austin_Lambeth:

calculating them when they change by assigning them to a real column

This would be the action-based data-changes I was referring to.

Basically… you need to consider every scenario that would update that value and program some way to account for it - using parent-child ref updates and such - and make the appropriate updates when appropriate. lol

This helps

Quick Update System - How to update records in your app with a push of a button Tips & Tricks 💡

“How can I update a parent record when a child record is changed?” “How can I mark all the line items in an invoice as paid when I mark the invoice as paid?” “If I have App Formulas inside a physical column, how can I get those to update like a virtual column?” “How can I recalculate formulas with a push of a button?” All of these situations can be solved by implementing a Quick Update System inside your app. What is the Quick Update System? A way to update app formulas and recalculate v…