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.

4 32 2,317
32 REPLIES 32

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

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

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

I've just stumbled across this old conversation. The discussion caught my eye because I have a Detail view where users go from row to row in slideshow mode updating QuickEdit columns in each along the way, and I would love to find ways to limit the VC calculation to the current row rather than the many hundreds of rows that each user has loaded at any given time--especially given the periodic app syncs as each user is assigned additional rows via automations.

@Grant_Stead & @MultiTech, it sounds like you're saying that it's possible to limit VC calculation to the currently displayed row by conditioning the VC's App formula on the current view context, such as CONTEXT("View") = "MyDetailViewWhereINeedTheVC". However, while that would indeed avoid unnecessarily calculating the column when unrelated views are displayed, I assume that when the cited view is active then the column would still calculate for all the loaded rows--i.e., not just the currently displayed row. Am I missing something?

AFAIK, the only way to limit VC calculation to the currently displayed row (sort of an equivalent of the requested CONTEXT(“ROW”) feature) is to use a Show type column, which I believe is calculated only for the current row--but in which case the column's "value" is not available for reference in other expressions (e.g., in other columns, in actions).

Just to confirm, I would write . . .

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

right?

Context (“ViewType”)

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

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”

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

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

Where do you find the sync time by VC

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

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

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

Wait a second! When did we get date formatting?

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!

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.

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

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.

MultiTech
Participant V

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.

Very useful insights @MultiTech_Visions.

MultiTech
Participant V

@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.

Thanks for the question.

To make a long story short, the answer to the Why question is: Didn’t occur to me to do it another way.

Now that I think of it, I could easily write my date expressions (English and Japanese) to this table. Thanks for the idea.

Currently, the TEXT() expression that @Suvrutt_Gurjar taught me only takes 0.03 seconds per sync so I no longer have a big incentive to make a change. Still, your point is very well taken and I’ll look through my virtual columns in this and other tables to try to find ones that could easily be written more-or-less permanently.

MultiTech
Participant V

To add another point here; just the other day I figured out that a de-reference essentially costs the same as a lookup() or select() during sync.

The process of calculating the dereference is the same (steps wise) as a lookup(); it involves reading the whole table that the de-ref is to.

For instance, if I have a de-ref in a child record that pulls some piece of data from the parent into the child - during the calc, that whole parent table is loaded in order to find the record needed.

Jonathan_S
Participant V

Is is possible to disable or stop the auto generated reverse ref columns from calculating?

Sure can, change the type to an Enum (Base type ref) and remove them! haha

Pointer
Participant V

In order to speed up synchronization in my application, I removed virtual columns and used enum ref instead of ref row. 

This topic says that ref row virtual columns will optionally be displayed with context. So, will synchronization be faster if I use ref row instead of enum ref and apply this method?

I will make corrections and I will be happy if you reply.

@Grant_Stead @Kirk_Masden @MultiTech @Suvrutt_Gurjar 

MultiTech
Participant V

@Pointer wrote:

In order to speed up synchronization in my application, I removed virtual columns and used enum ref instead of ref row.


Changing a reference from hard to soft - from a Ref column type to an Enum (base type ref) - will remove the system generated reverse reference (aka. the [Related Whatever] virtual column on the parent table, that uses a REF_ROWS() formula) from the system.

  • This will, in turn, help reduce the time it takes for your app to sync.

____________________________________________________________________________

It's advisable to do this if you only need the reference in order to dereference data; meaning you don't need to see the list of child records on the parent record.  If that's true, then you can change the Ref to an Enum (base type ref) and still maintain the dereference capability - but without the reverse reference that adds virtualization overhead. 💪

Using enum ref has accelerated synchronization, but it is part of and related tables are not created, which is not useful. My question is, if I use ref row and have it calculated when called with CONTEX(), will I be successful in synchronization speed?

By the way, one strategy I use to avoid virtual columns that might slow down my app is INPUT():

Using INPUT() to update another table using a value from this row
https://www.googlecloudcommunity.com/gc/Tips-Tricks/Using-INPUT-to-update-another-table-using-a-valu...

This is akin to a "just-in-time" strategy in manufacturing.  That is, instead of keeping the information in virtual columns on the off chance that it may be needed someday, you use INPUT() to write it to a table with only one or two records  when you need it.  This way, the calculation is only done when the information is actually needed. Or, if you have many virtual columns in the tiny table to which the key information is written, they don't slow things down because they only need to crunch a very small amount of data.

Pointer
Participant V

My question is actually very simple, I want to use the related items (ref row), but I was not using enum ref because I had the problem, it was not working. How can I use ref order without having synchronization problems? 

Top Labels in this Space