Sync slow because of "most recent" MAXROW() virtual column

AppSheet AppSheet Q&A
1

I looked at the Corporate Dashboard Performance analyzer and it appears that the reason my app is taking almost a minute to sync is a virtual column where I grab the most recent entry for a particular bid item.
Here is the expression:

MAXROW("DailyBidItems", "created_timestamp", AND([bid_item_fk] = [_THISROW].[bid_item_fk], [daily_bid_item_pk] <> [_THISROW].[daily_bid_item_pk]))

I use this to pre-populate fields with the most recent values for things like “percent complete.” Is there a more efficient way to do this so that the sync time is shorter? I’ve received complaints from app users regarding the speed of sync.
Thank you!

2

If the VC is only used by some but not all rows, consider wrapping MAXROW() in IFS() with a condition that’s only true for the rows the VC is used.

2 Likes
3

it’s only used in new rows. How would that be indicated?
Perhaps something along the lines of, if DATE([created_timestamp]) = TODAY() ?

1 Like
4

Vivian_Nowka-Keane:

Perhaps something along the lines of, if DATE([created_timestamp]) = TODAY() ?

That would very likely improve things, depending on how clustered-together your timestamps are.

If by “new rows” you mean “only as a row is being added”, you can test whether a row is “new and as of yet unsaved” versus preexisting with this:

ISBLANK(
  FILTER(
    "DailyBidItems"
    ([RowKey] = [_THISROW].[RowKey])
  )
)

replacing RowKey with the name of the table’s key column. This is a heavier computation than the expression you proposed, but it guarantees to find the one row being added. Compare the performance of each to find the better for your situation.

Another option would be to use a normal (not virtual) column and populate it using an initial value rather than an app formula. This uses storage, but entirely eliminates the recalculation on sync.

2 Likes
5

Your solution is accurate to what the purpose of the virtual column is, which is for being used as rows are added, but the performance time is much better using the expression I described. With your solution of heavier computation, sync time was about 12 seconds, but by checking the date, it was brought down to about 6 seconds. My gut wants to do what’s accurate, but I’ll stick with the lighter computation as it will suffice for what I’m doing.
Thanks for your help!

1 Like