Why certain virtual columns slow down syncs

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):

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.

2 Likes

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

8 Likes

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

1 Like

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

2 Likes

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

2 Likes

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

Just to confirm, I would write . . .

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

right?

1 Like

Context (“ViewType”)

2 Likes

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”)

8 Likes

Wait a second! When did we get date formatting?

2 Likes

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

3 Likes

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

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

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

Hi @Kirk_Masden,

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”

3 Likes

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”}

3 Likes

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

3 Likes

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

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.

6 Likes

@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