Why certain virtual columns slow down syncs

AppSheet AppSheet Q&A
1

Hi! I’ve recently gotten a lot of valuable help on how to avoid expensive virtual column expressions (that is, virtual column expressions that slow down syncs):

[How to avoid expensive virtual column expressions](https://community.appsheet.com/t/how-to-avoid-expensive-virtual-column-expressions/30111) Questions

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…

This question is related to that thread but I thought it was different enough to warrant a separate post.

Today I found that the following expression can cause over a second of extra sync time in my app:

concatenate(ifs(MONTH([Began])=1,“January”,MONTH([Began])=2,“February”,MONTH([Began])=3,“March”,MONTH([Began])=4,“April”,MONTH([Began])=5,“May”,MONTH([Began])=6,“June”,MONTH([Began])=7,“July”,MONTH([Began])=8,“August”,MONTH([Began])=9,“September”,MONTH([Began])=10,“October”,MONTH([Began])=11,“November”,MONTH([Began])=12,“December”,)," “,day([Began]),”, “,year([Began]),”, ",TEXT([Began], “H:MM AM/PM”))

The expression is fairly long, and there are many records in the table (over 3000) that it applies to, but all it does is determine the format of a date that will be shown in a detail view. So, it would seem to me that AppSheet could basically ignore it at sync time and have the app handle the display format inside the device when the detail view is shown.

This leads me to another question. Is AppSheet continuing to work on its syncing algorithms? Or, in other words, can we reasonably expect syncing times to improve in the future as technology improves and as the syncing algorithm continues to be tweaked?

Thanks for your consideration.

4 Likes
2

So the context expression helps a lot for this kind of stuff… If context detail, xyz, else "’…
Therefore, it will only calculate complex expressions in the detail view…

Crazy trick for you…
Thanks @MultiTech_Visions

11 Likes
3

Wow! Hadn’t thought of that. I’ll give it a try! Thanks!

1 Like
4

It’s also bonkers for ref lists…
Like you can put a quick edit toggle on the detail view to show hide the REFROWs, which is also tied to the virtual columns… So it only calcs the lists on demand…

I’ve had to get nasty with virtuals some of our data sets can get massive… But seriously, I wouldn’t be outside the box on that without Matt

3 Likes
5

Side note… Putting REFROWs inside any function geeks it up… So, use case by case, and test well

2 Likes
6

Most virtual columns you want in specific views anyway… I’ve also wanted to test it to see if you just went IF(CONTEXT (VIEW) = CONTEXT (VIEW) if it would skip the calculation during sync…

Whatcha think @MultiTech_Visions

1 Like
7

Just to confirm, I would write . . .

if(context(“Type”)=“Detail”, insert original expression here ,“”)

right?

1 Like
8

Context (“ViewType”)

2 Likes
9

Kirk_Masden:

concatenate(ifs(MONTH([Began])=1,“January”,MONTH([Began])=2,“February”,MONTH([Began])=3,“March”,MONTH([Began])=4,“April”,MONTH([Began])=5,“May”,MONTH([Began])=6,“June”,MONTH([Began])=7,“July”,MONTH([Began])=8,“August”,MONTH([Began])=9,“September”,MONTH([Began])=10,“October”,MONTH([Began])=11,“November”,MONTH([Began])=12,“December”,)," “,day([Began]),”, “,year([Began]),”, ",TEXT([Began], “H:MM AM/PM”))

Hi @Kirk_Masden,

Not sure , if this will help with sync time. The above expression could be written as

TEXT([Began], “MMMM DD, YYYY, H:MM AM/PM”)

Just one difference from your expression and the above expression is single digit dates will be preceded with a zero in the short expression.

For example your expression will give 2nd September as

September 2, 2020, 8:16 AM

The short expression gives

September 02, 2020, 8:16 AM

May be worth a try with shorter expression, whether it helps with sync times.

Edit: The TEXT() function seems to be very powerful. The following format will give the date when it is single digit without preceding zero

TEXT([Delivery Date], “MMMM D, YYYY, H:MM AM/PM”)

7 Likes
10

Wait a second! When did we get date formatting?

2 Likes
11

For how often I’m on this thing, it’s fascinating I still miss things… Ugh!

4 Likes
12

Wow! Thanks a lot!

By the way, I think one reason that I got started using a CONCATENATE() expression in which a unit of time is chosen through an IF() expression is my need to produce dates in Japanese. Here’s and example:

concatenate(YEAR([Date]),“年”,MONTH([DATE]),“月”,DAY([Date]),“日（”,> ifs(WEEKDAY([Date])=1,“日”,WEEKDAY([Date])=2,“月”,WEEKDAY([Date])=3,“火”,WEEKDAY([Date])=4,“水”,WEEKDAY([Date])=5,“木”,WEEKDAY([Date])=6,“金”,WEEKDAY([Date])=7,“土”),> “）”)

This produces “2020年8月31日（月）” (Monday, August 31, 2020).

Still, for English, I definitely want to make better use of the TEXT() expression. Thanks again!

3 Likes
13

Thank you @Kirk_Masden. App in multi languages indeed have need for that additional logic. Yes, used only with English language apps, TEXT() seems to be very powerful for handling temporal values.

2 Likes
14

Grant_Stead:

CONTEXT()

Oops! Sorry for that stupid mistake on my part.

When I tried

if(context(“ViewType”)=“Detail”,> TEXT([Began], “MMMM D, YYYY, H:MM AM/PM”),> “”)

nothing showed up in that spot on the detail view. This may be the reason:

Note that View and ViewType may not work as expected if used in a virtual column’s App formula expression.

Trying to name the view (e.g. Context(“View”)= exact name ) didn’t work either.

I wonder, @Grant_Stead, since you have obviously put a lot of work into reducing sync times, if you think an “ignore at sync” option might be appropriate or useful for situations where you want the app to do what computing is necessary on a display by display basis.

This also relates to my original question about whether or not AppSheet is continuing to tweak its syncing algorithm. It would be great if the algorithm were smart enough to figure out something like “this virtual column is only needed in the context of detail views so I don’t need to worry about it now.”

4 Likes
15

Hi @Kirk_Masden,

Kirk_Masden:

Note that View and ViewType may not work as expected if used in a virtual column’s App formula expression.

To circumvent above, I believe you may wish to try the date formatting equation in a separate VC. Say this VC is called [Date for Detail] with the expression in app formula as

TEXT([Began], “MMMM D, YYYY, H:MM AM/PM”)

You could choose to show this VC only in detail view with Show_If constraint of CONTEXT(“ViewType”)=“Detail”

4 Likes
16

Thanks!! That’s what I’ve done!

By the way, the use of TEXT() that you recommended seems to be much, much faster at sync time:

BEFORE:
Compute virtual column 00:00:01.0848519 {“ColumnName”:“V Date began for detail”}
AFTER:
Compute virtual column 00:00:00.0305264 {“ColumnName”:“V Date began for detail”}


3X_6_3_63f4359dc043149bd20070a20213cd5abf9fa4ab.gif

3 Likes
17

That is great great to know @Kirk_Masden. Thank you.

3 Likes
18

Fascinating, I don’t think I’ve noticed that note, or have had any issues with that in the past… I’ll need to double back to some of my apps and make sure they’re still performing. If I have time I’ll report back.

3 Likes
19

To chime in on the whole CONTEXT(“ViewType”) thing.

I’ve been experimenting with this and found it to be ridiculously handy for cutting some corners.

For instance, I’ve just put together a form that has a virtual column that processes over a set of rows to come up with a result (a very heavy handed formula) with a pre-condition included.

IFS(CONTEXT(“ViewType”) = “Form”,
blah blah blah.)

So the virtual column only calculates when inside the form.

As @Grant_Stead mentioned, if you tuck your Ref_Rows inside something like the formula above, but instead include a separate column (of the enum type) where people can toggle the ref-row on/off, you’ll find your sync times will decrease and performance in general will get better.

If you’ve got an app where you can reasonably expect only one person to be working on any given row at a time, you could totally build an app where every ref_rows and other heavy virtual column would be empty until you selected that row.
With the ability to take over the event actions for inline views, you could devise a system where when a user taps on a record it flags that record and all the VC populate (making that record “available” and “workable” in the app).
When you select another, the previous flag would be cleared, maintaining a relatively empty VC structure.

It would be a pain to build, but I think you could get away with crazy complex VCs - with TONS of rows and data - yet have a quick load time.

8 Likes
20

Kirk_Masden:

concatenate(ifs(MONTH([Began])=1,“January”,MONTH([Began])=2,“February”,MONTH([Began])=3,“March”,MONTH([Began])=4,“April”,MONTH([Began])=5,“May”,MONTH([Began])=6,“June”,MONTH([Began])=7,“July”,MONTH([Began])=8,“August”,MONTH([Began])=9,“September”,MONTH([Began])=10,“October”,MONTH([Began])=11,“November”,MONTH([Began])=12,“December”,)," “,day([Began]),”, “,year([Began]),”, ",TEXT([Began], “H:MM AM/PM”))

@Kirk_Masden question for your about your original formula… why was this inside a VC? Seems like something like this could be stored as a static variable (only changing whenever the relevant data changes) inside a physical column.

3 Likes