Formula Length affecting sync time

tingtingandrea · 07-06-2019 06:00 PM

I’ve been trying to find the answer to this. Does the length of an app formula in a virtual column affect the sync time? Should I stick to 1 long formula or multiple short ones. Currently my app is taking way too long to sync.

Aleksi

The physical length in generally is not a reason… but the content is normally doing that if it needs to read values from another table. What kind of formula are you using?

tingtingandrea

Mostly select() formulas, I have 3 tables that read from each other. 1 for the item, 1 for measure, and 1 to log. My sync time takes between 50sec-120 which is way too much. I’ve never had a sync time this bad before. It’s normally 30 max with this many tables and views.

Aleksi

What are your exact app fomulas in virtual column(s)?

tingtingandrea

if(text(select(Foods[food/recipe],[barcode]=[_thisrow].[Food/Drink]))=“Food”,
sum(select(Foods[Calories],[barcode]=[_thisrow].[Food/Drink]))*

if([Unit]=“UhmXBoR0”,decimal([Amount]),

decimal([Amount])*sum(select(unit converter[to grams],[_thisrow].[unit]=[key]))
/sum(select(unit converter[#],[_thisrow].[unit]=[key]))

)
/sum(select(Foods[Serving Size],[barcode]=[_thisrow].[food/drink]))

,

sum(select(Foods[Calories r/m],[barcode]=[_thisrow].[Food/Drink]))*
decimal([Amount])
)

Steve

This is the first formula you posted, reformatted for clarity:

if(
  (
    text(
      select(
        Foods[food/recipe],
        ([barcode] = [_thisrow].[Food/Drink])
      )
    )
    = “Food”
  ),
  (
    sum(
      select(
        Foods[Calories],
        ([barcode] = [_thisrow].[Food/Drink])
      )
    )
    * if(
      ([Unit] = “UhmXBoR0”),
      decimal([Amount]),
      (
        decimal([Amount])
        * sum(
          select(
            unit converter[to grams],
            ([_thisrow].[unit] = [key])
          )
        )
        / sum(
          select(
            unit converter[#],
            ([_thisrow].[unit] = [key])
          )
        )
      )
    )
    / sum(
      select(
        Foods[Serving Size],
        ([barcode] = [_thisrow].[food/drink])
      )
    )
  ),
  (
    sum(
      select(
        Foods[Calories r/m],
        ([barcode] = [_thisrow].[Food/Drink])
      )
    )
    * decimal([Amount])
  )
)

As @Aleksi noted, you’re doing a lot of table lookups, which get slower and slower as the tables grow.

If ([barcode] = [_thisrow].[Food/Drink]) will only ever match one row in the Foods table, you would see better performance if you use a separate virtual column to lookup that matching row, then dereference that virtual column to get the row’s column values in your expression. For example, suppose you added a virtual column called ThisFood with this app formula:

any(
  filter(
    "Foods",
    ([barcode] = [_thisrow].[Food/Drink])
  )
)

You could then simplify your original expression:

if(
  ([ThisFood].[food/recipe] = “Food”),
  (
    [ThisFood].[Calories]
    * if(
      ([Unit] = “UhmXBoR0”),
      decimal([Amount]),
      (
        decimal([Amount])
        * sum(
          select(
            unit converter[to grams],
            ([_thisrow].[unit] = [key])
          )
        )
        / sum(
          select(
            unit converter[#],
            ([_thisrow].[unit] = [key])
          )
        )
      )
    )
    / [ThisFood].[Serving Size]
  ),
  ([ThisFood].[Calories r/m] * decimal([Amount]))
)

You could similarly simplify your conversions by adding a ThisConversions virtual column (assuming ([_thisrow].[unit] = [key]) will only ever match a single row in the unit converter table):

any(
  filter(
    "unit converter",
    ([_thisrow].[unit] = [key])
  )
)

and further simplify your original expression:

if(
  ([ThisFood].[food/recipe] = “Food”),
  (
    [ThisFood].[Calories]
    * if(
      ([Unit] = “UhmXBoR0”),
      decimal([Amount]),
      (
        decimal([Amount])
        * [ThisConversions].[to grams]
        / [ThisConversions].[#]
      )
    )
    / [ThisFood].[Serving Size]
  ),
  ([ThisFood].[Calories r/m] * decimal([Amount]))
)

I would also encourage you to find a way to eliminate the conditional handling of ([Unit] = “UhmXBoR0”). Someone who isn’t intimately familiar with your data (such as someone trying to help you, or a future maintainer of you app) is going to find that confusing. I would encourage you to add that unit to the unit converter table with to grams and # values each of 1. Then you could still further simplify your expression:

if(
  ([ThisFood].[food/recipe] = “Food”),
  (
    [ThisFood].[Calories]
    * decimal([Amount])
    * [ThisConversions].[to grams]
    / [ThisConversions].[#]
    / [ThisFood].[Serving Size]
  ),
  ([ThisFood].[Calories r/m] * decimal([Amount]))
)

Also as Aleksi suggested, using normal (non-virtual) columns can greatly increase performance (at the cost of more storage used). Values that don’t change once initially calculated are ideal candidates for normal columns. All of the expressions I’ve discussed here would seem to me to be good candidates for normal columns.

tingtingandrea

Thank you, I just tried it out and it helped a lot. Could you take a look at the second formula perchance?
I’m having a lot of issues when the column is blank. The first 2 filters work perfectly, it’s the last one. In() works fine on it’s own but I need all 5 columns from unit converter to match 4 columns in foods if they aren’t blank.

filter(unit converter,isblank([barcode]&[product]&[type]&[brand]&[description included]&[description excluded]))

filter(unit converter,[barcode]=[_thisrow].[barcode])

filter(unit converter,
and(
isblank([barcode]),
if(isblank([product]),isblank([product]),in([_thisrow].[product],[product])),
if(isblank([type]),isblank([type]),in([_thisrow].[type],[type])),
if(isblank([brand]),isblank([brand]),in([_thisrow].[brand],[brand])),
if(isblank([description included]),isblank([description included]),in([_thisrow].[description],[description included])),
if(isblank([description excluded]),isblank([description excluded]),not(in([_thisrow].[description],[description excluded])))
))

Steve

Here’s your expression reformatted for clarity:

filter(
  "unit converter",
  isblank(
    [barcode]
    & [product]
    & [type]
    & [brand]
    & [description included]
    & [description excluded]
  )
)
* filter(
  "unit converter",
  ([barcode] = [_thisrow].[barcode])
)
* filter(
  "unit converter",
  and(
    isblank([barcode]),
    if(
      isblank([product]),
      isblank([product]),
      in([_thisrow].[product], [product])
    ),
    if(
      isblank([type]),
      isblank([type]),
      in([_thisrow].[type], [type])
    ),
    if(
      isblank([brand]),
      isblank([brand]),
      in([_thisrow].[brand], [brand])
    ),
    if(
      isblank([description included]),
      isblank([description included]),
      in([_thisrow].[description], [description included])
    ),
    if(
      isblank([description excluded]),
      isblank([description excluded]),
      not(in([_thisrow].[description], [description excluded]))
    )
  )
)

I’d expect that expression to produce no result: you’re attempting to multiply lists, (filter(...) * filter(...) * filter(...)), which isn’t something AppSheet can do. If your intention is to use the first/only result of each FILTER() expression in the computation, you’ll need to wrap each FILTER() expression with ANY() or INDEX() to extract only a single item from the list FILTER() produces:

any(
  filter(
    "unit converter",
    isblank(
      [barcode]
      & [product]
      & [type]
      & [brand]
      & [description included]
      & [description excluded]
    )
  )
)
* any(
  filter(
    "unit converter",
    ([barcode] = [_thisrow].[barcode])
  )
)
* any(
  filter(
    "unit converter",
    and(
      isblank([barcode]),
      if(
        isblank([product]),
        isblank([product]),
        in([_thisrow].[product], [product])
      ),
      if(
        isblank([type]),
        isblank([type]),
        in([_thisrow].[type], [type])
      ),
      if(
        isblank([brand]),
        isblank([brand]),
        in([_thisrow].[brand], [brand])
      ),
      if(
        isblank([description included]),
        isblank([description included]),
        in([_thisrow].[description], [description included])
      ),
      if(
        isblank([description excluded]),
        isblank([description excluded]),
        not(in([_thisrow].[description], [description excluded]))
      )
    )
  )
)

IF() expressions like this:

if(
  isblank([product]),
  isblank([product]),
  in([_thisrow].[product], [product])
)

can be simplified and made more efficient by writing them instead as an OR() expression:

or(
  isblank([product]),
  in([_thisrow].[product], [product])
)

which gives you:

any(
  filter(
    "unit converter",
    isblank(
      [barcode]
      & [product]
      & [type]
      & [brand]
      & [description included]
      & [description excluded]
    )
  )
)
* any(
  filter(
    "unit converter",
    ([barcode] = [_thisrow].[barcode])
  )
)
* any(
  filter(
    "unit converter",
    and(
      isblank([barcode]),
      or(
        isblank([product]),
        in([_thisrow].[product], [product])
      ),
      or(
        isblank([type]),
        in([_thisrow].[type], [type])
      ),
      or(
        isblank([brand]),
        in([_thisrow].[brand], [brand])
      ),
      or(
        isblank([description included]),
        in([_thisrow].[description], [description included])
      ),
      or(
        isblank([description excluded]),
        not(in([_thisrow].[description], [description excluded]))
      )
    )
  )
)

Consider this FILTER() expression:

filter(
  "unit converter",
  ([barcode] = [_thisrow].[barcode])
)

AppSheet has an unusual way of performing an is-equal-to operation like the above: if the left operand ([barcode]) is blank, the operation will be TRUE regardless the value of the right operand ([_thisrow].[barcode]). This means that this FILTER() expression will include every row in the unit converter table that has a blank barcode column value. I suspect this probably isn’t what you expect or want.

Finally, consider what will happen if a FILTER() expression produces no results: it’ll be treated as a value of zero (0) in your math expression. Any number multiplied by zero is zero. So, if any of the three FILTER() expressions produce no results, the entire expression will evaluate to zero.

tingtingandrea

You’re a life saver. I just tried it out and it works perfectly without using any. Thanks so much I’ve been staring at this formula for days.

Aleksi

Well… your formula is quite complex when reading your Foods and Unit converter tables more than once for one record. If you now have lot of records in both tables the calculatation will take a lot time for the result when syncing the app. Have you checked from the Performance profile’s details how much they need for that calculation?

tingtingandrea

It takes around 30 secs. I’m not sure if there’s a better way to write the formulas. This is usually what I use and it didnt take this much time before. Also I’ve just tried changing my virtual columns into physical columns with app formulas and that didn’t seem to help sync time much.

Aleksi

When you have changed the app formula from virtual column to normal column, it should have an affect. When you sync the app, your normal column’s appformula is not recalculated. Then the reason needs to be somewhere else than exactly that one virtual column. You should check all virtual columns so you would find out what would be the main reason for your sync time.

tingtingandrea

I just converted all my long formulas into normal columns and it brought the sync time from 120 to 40sec whewww!

I have 1 virtual column I cannot get rid of as it’s constantly changing and I really need help with this formula. I can’t seem to find any better way to write it. This column taking about 35secs.

(

filter(unit converter,and(isblank([barcode]),isblank([product]),isblank([type]),isblank([brand]),isblank([description included]),isblank([description excluded])))

filter(unit converter,[barcode]=[_thisrow].[barcode])

filter(unit converter,
and(
contains([product],[_thisrow].[product]),
isblank([type]),
isblank([brand]),
isblank([description included]),
isblank([description excluded])
))

filter(unit converter,
and(
isblank([product]),
contains([type],[_thisrow].[type]),
isblank([brand]),
isblank([description included]),
isblank([description excluded])
))

filter(unit converter,
and(
contains([product],[_thisrow].[product]),

contains([brand],[_thisrow].[brand])

))

filter(unit converter,
and(
if(isblank([product]),isnotblank([key]),contains([product],[_thisrow].[product])),

if(isblank([type]),isnotblank([key]),contains([type],[_thisrow].[type])),

if(isblank([brand]),isnotblank([key]),contains([brand],[_thisrow].[brand])),

if(isblank([description included]),isnotblank([key]),contains([description included],[_thisrow].[description])),

if(isblank([description excluded]),isnotblank([key]),not(contains([description excluded],[_thisrow].[description])))
))

)