How to avoid expensive virtual column expressions

Kirk_Masden · 08-28-2020 06:17 PM

My app is rather bloated and I think my sync times are slow because of how I’ve used virtual columns. This led me to search for tips regarding how to speed things up. Here’s a good one:

I’m sure the advice is very good but I’m having trouble implementing @praveen’s fourth admonition:

Avoid virtual columns with a SELECT and anything but an equality or IN filter condition

To be honest, merely getting my app to do what I want it to do has been difficult for me. Figuring out how to do that efficiently, etc. has added another level of complexity that has been a bit beyond me for the most part.

I’ve found lots of admonitions about avoiding this or avoiding that but what I usual don’t understand is what I might do instead. For example, let’s say I want to be able to display a running count of how many records remain in a set that needs to be worked on. I do that with a SELECT() expression in a virtual column, but I’m not supposed to. So, I’m wondering if “avoid such and such” means that there are workarounds that are more efficient or if it means that one needs to give up on kinds of functionality.

If there’s a tutorial or something with examples of how expensive virtual column expressions can be avoided without losing functionality, I’d love to study it. Depending on the price, I think I’d be willing to pay for such instruction.

Marc_Dillon

This is certainly another learning curve, on top of an already large learning curve. First you learn how to do it, then you attempt to learn how to master it, right?

The thread you linked is full of very good info. Definitely a must read for anyone interested in performance optimization.

From what I’ve seen, the main alternative to a virtual column is to setup actions that run upon form saves (or on the push of another action button). These actions perform the same calculation as the virtual column, but instead update the value of a real column. This means that this calculation is only running when it needs to (providing you set it up in all the right places), instead of a VC that runs upon every sync, and on every record.

It’s a trade-off of development convenience and ease vs app performance.

For example if you’re looking to hold a count of child records in a parent record, you would setup an action that runs upon form save of a child record, which executes an action in the parent record, which sets the value of a column, using the same expression that you would have put in your VC.

I believe you could say the same thing for a lot of disciplines.

Koichi_Tsuji

Hi @Kirk_Masden

Your frastration should be common across all the app users. Expression such as select with decent amount of “where” type conditions, as well as IN, Contain etc used in virtual column is always getting app heavier as the app table grows time goes by.

One of the tricks I m kicking into my app is quite simple trick. Just replacing VC to physical column. Yes, we lose the functionality of VC, i.e. always run the calcualation to show the result of basis latest associated table/rows are edited. However, this simply slow down the app.

To avoid this, I push expression into physical column either inital value or app formula. Then I implement the mechanism to update the entire row by changing value of trigger column. Run this action to refresh the physical column which use to VC before.

Ref type column and its calcultion is basically return the list of rows in question. Just holding such value within Physical column. List type is only used in VC, so change it onto Enumlist. Now we are able to set Ref relationship within Enum and Enumlist. So on this enumlist field returns the set of row as inline as VC does.

Once related child row is added, edited, or deleted, to run the re-calculation of physical fields, set up the workflow action to fire the update in the trigger column in the associated parent, basically just one row in the parent table.

This will remove the usual VC ref type which tie the child to parent.

Kirk_Masden

Thanks @Marc_Dillon! Thanks @tsuji_koichi!

Both strategies (moving expressions to actions and replacing virtual columns with physical ones) are ideas that I have been looking at and will give more attention to in the future. I really appreciate your detailed and thoughtful comments.

One more thought:

When I’m editing, I frequently encounter the “Note, this expression could significantly impact sync time” warning. In fact, it’s so common as to be almost meaningless. I’ve tried to utilize the performance profile to identify what I might do to speed things up, but that’s hard. The article says to look for

Virtual column expressions that take a long time to read: perhaps they can be written more optimally?

But I’ve never been able to find a reference to a specific virtual column expression that is causing a slow sync. Does this have to do with the fact that I’m not on a corporate plan and don’t have access to all of the performance profile bells and whistles?

Also the performance profile article says that slow sync times are almost always because of “because of many tables and/or large tables.” That leads me to wonder if spending a lot of energy to reconfigure virtual columns might not make that much of a difference for my long sync times.

All in all, though the article I quoted in my original post seems to have some good pointers, I feel pretty much in the dark when it comes to deciding what I need to do first to improve efficiency. I wish there were some analytic tool that could let me know what my most expensive expressions and tables are. I don’t know about such a tool at this writing (other than the performance profile, which, as I wrote above doesn’t give me that much detail). If there’s something I’m missing please let me know.

Kirk_Masden

Sorry to reply to my own post but I’ve been trying to answer my own question without much success. This post from @praveen is quite relevant:

I’m particularly intrigued by the following:

But now here’s the tricky part … what happens with virtual columns? The virtual columns for table 1 are computed right after table 1 is fetched. But let’s say one of them requires table 8. So that has to be fetched too. In the performance logs, this will count towards the compute time for the virtual column. But in reality, table 8 had to be fetched anyway at some point during the sync. In fact, you’ll see this recorded if you drill down into the virtual column computation in the profiler.

I think I understand the basic gist of this advice but I don’t understand what drilling “down into the virtual column computation in the profiler” refers to. I’ve tried to look at both the Audit History and the Performance Profile but couldn’t find that kind of information. Perhaps I need a corporate plan for this.

Aleksi

Business plan is not needed with the Performance Analyzer or it’s details. Click the “Focus on slow steps” and then when you go deeper with the details you will see virtual columns if they need more time than it’s expected.

Kirk_Masden

Wow! Sorry to be so dense. I’m not sure how I missed it before but I finally understand how you to get detailed information about which virtual columns are expensive. I should have been able to figure it out on my own but, somehow, it eluded me. Thanks! Now, I finally feel like it know what I need to do!!

Kirk_Masden

After taking another look at the performance analyzer results, I guess I was confused because I looked at individual adjustments in data like the following:

[internal] UpdateRow 00:00:03.2546041 {“TableName”:“Kankaku”}

With this one, I can’t drill down any farther. So, does that me that individual updates like this one are not affected by the virtual columns?

GreenFlux

Another approach is moving certain virtual columns to workflow/report templates only.

If the VC is summary data, like a total [AmountDue] on all unpaid accounts, you could move that expression to an email template and run it on demand or on a report schedule to see the value.

Instead of constantly calculating those totals in every client app, the user would just click a button to receive the report with the summary data. Just think about why/when/where users need to see the data, and in some cases you can remove the formula from the app entirely, other than in an email template.

Aleksi

Or… you could create a Summary table that has all total calculations that you need in your app and then you trigger those calculations with one action button.

Kirk_Masden

Right! I’m looking at something like that now. The help I’ve gotten here has already sped up my sync time a little. I’ll keep working on this.

Kirk_Masden

Thanks to help from @Marc_Dillon, @tsuji_koichi, and @Aleksi, I was able locate which virtual columns were slowing down the sync and then make some adjustments. The strategies I’m using are:

Writing data (so that I don’t need to rely on v columns) via actions
Making the Google spreadsheet do some work that v columns had done
Simplifying v columns when possible

In regard to the first strategy, there’s a situation where I would like to use an action to write data but I need to be able to trigger the action so that it is done with the opening of a bottom menu. Unfortunately, that’s not possible now so I haven’t been able to fix that v column:

Another thing I noticed is that the performance analyzer has some “performance” issues of its own. After I finally learned how to use the analyzer to identify virtual columns causing problems, I tried to use it soon after having made a change in my app. Unfortunately, however, there seems to be quite a lag between the app itself and what one can see in the performance analyzer. I wish it were more like the expression editor in the editing interface, so that we could get immediate feedback.

Thanks again everyone for your help!!

Koichi_Tsuji

Why dont you think about introducing an intermediate table/view to trigger the action?
Currently, both primary and menu view is just open the target view, not possible to trigger action by hitting those icon.

What I mean is to generate simple independent table, and add one row. Read this from Appsheet and generate detail view. And whatever type of you feel confortable, you attach action into it. (I prefer inline action for those use case)

Upon hitting menu button/icon, then detail view come up and hit action icon. Need to tap twice to fire action, but it could be acceptable?

Aleksi

Yeap, I proposed the same but with a form view

Koichi_Tsuji

One of my apps, I implemented this tricks, but action not to change the values, but jump to form view “with sync” while the app is set to delayed sync.

Kirk_Masden

Thanks @Aleksi! Thanks @tsuji_koichi!

These are intriguing ideas that I had not thought of. I hesitate, however, because I don’t want to change the user experience. Unless I have misunderstood, the user experience would be changed, wouldn’t it?

Actually, I’ve been thinking of another approach. The expensive virtual column I’m trying to avoid is needed to trigger a format rule. I could just put the condition in the format rule, but that would cause a warning and perhaps cause some latency (?). Another approach I’m considering is to make a different view that replaces the current view based on the condition that is now in the virtual column. In the view, I could make the red color that I want for the warning standard, so that no additional computation would be needed.

What I am talking about would be this kind of solution. I hope it would not cause a long sync time or any other problem:

Aleksi

That warning with the format rule… if you have a complex formula, that could cause some slowness when scrolling the view. But that has nothing to do with the sync because it’s happening on the client side.

Koichi_Tsuji

Not sure what is your really problem here, as the subject is starting to deviate from your original topics and subject posted here. …

For this, that s only you as app creator would decide. I m not sure what is the exact user experience you have on the app currently, so not able to put my comment.

Kirk_Masden

Thanks for your help. I’ve gotten lots of good ideas here and am working on fixing my app now. I’ll try to report back later.

Koichi_Tsuji

「三人寄れば文殊の知恵」

Glad to hear you got inspired.

Aleksi

@Kirk_Masden About this… “action to write data but I need to be able to trigger the action so that it is done with the opening of a bottom menu”

Have you thought to use a form view with the option “Row key” so that the form view will be opened from the bottom menu. Think it that as a confiirmation message. And if you set the option “Auto save” as ON, it will close the form view right away and action is triggered as the Event action.

Kirk_Masden

Thanks to @Aleksi, @tsuji_koichi, @Marc_Dillon, @Grant_Stead, @Steve, @MultiTech_Visions, @GreenFlux and @Suvrutt_Gurjar! (I hope I’m not forgetting someone )

Your advice helped me speed up my sync time with almost no decline in user experience:

I say “almost no decline” because I got rid of a couple of very minor features that were not important but were difficult to implement without expensive virtual columns.

Actually, the sync times in the image above are a bit faster, I think, than what I can expect most of the time – I’ve noticed that sync times vary with conditions. Still, I think my app is better now and appreciate the valuable help.

By the way, here are some related threads where I also received help with my little project of speeding up my sync times:

Actually, I haven’t gotten an answer to this one yet. Any thoughts?

Grant_Stead

Wow, nice work!
It’s a big step, and I remember when I did this to an app for the first time. Solid work going from 20 seconds to sub 10!

Rafael_ANEIC-PY

Hey everyone, sorry for hijacking this wonderful thread, but i have some questions about this:

1-Does that mean i can go ham on the reports with the select() formulas?
2-In the case of email workflows, does the use of many formulas affect the time it takes for the user to receive the email?

I have been trying to balance my virtual columns inside my apps while trying to minimize the amount of computation done in the reports, but if that isn’t necesary at all then im gonna get back a few seconds off my sync times.

Marc_Dillon

Yes.

An expression always has to be processed. A single expression on a single record doesn’t take very long, but that duration starts to build up when you have hundreds or thousands of records.

The point here is that in a VC, that expression will need to be processed for EVERY record, upon EVERY sync. In a workflow, it will only be processed for one single record at a time, and only when the workflow fires. Which in 99% of cases, that amount of time should be wayyyy lower.

Rafael_ANEIC-PY

I see, thanks for the answer @Marc_Dillon. I can think of 5 virtual columns that will meet their demise very soon just from the top of my head, this is game changing for me !

Gustavo_Eduardo

I don't know if it's the same or not, I have found (at least in my caveman way) an alternative solution to "select" when trying to find records. Select() is a way of selecting with a filter. Appsheet always creates "related" fields that are nothing more than selects. I use column dereferencing. So if I have to add a column of related articles, instead of doing

SUM(

SELECT(

items[Row ID],

AND( ..... )

)

Do

SUM([Related Articles][Desired Column],TRUE)

and I get the same result.

Aclaro yo soy un cavernícola en esto.

dbaum

@Gustavo_Eduardo wrote:

SUM([Related Articles][Desired Column],TRUE)

What is the role of "TRUE" in your expression? I don't see any second argument in the SUM function documentation.

Gustavo_Eduardo

Tienes toda la razón, no va el true! Muchas gracias por avisar...Es

SUM([Artículos relacionados][Columna deseada])