Keep getting error Unable to fetch app definition

[Fusion_Incense]

Keep getting error Unable to fetch app definition. Error:Error: Data table ‘MessageBoardImage’ is not accessible due to: Timed out waiting for a resource. The reason is usually a very large/slow loading table or a cross-table cycle in AppSheet formulas…/n
When I check my Performance details it gets time out from “Read rows from data source” which from 2 tables of ‘MessageBoard’ read 11262 rows and ‘MessageBoardImage’ read 356 rows
My question: are tables to large? if so what can I do to speed up the reading process?

[Steve]

You likely have virtual columns with complex or inefficient app formulas.

[Fusion_Incense]

@Steve Hello Steve,
Meaning 11xxx rows of data size should not be the issue right? I’ll check my formulas to see if I can improve it, Thanks

[Steve]

I don’t think 11,000 rows is inherently a problem. I depends on how many columns the rows have.

[Fusion_Incense]

@Steve My data size now is 11,xxx rows and 10 columns and still growing everyday, I have modify the formula a bit, the app only run a bit faster, still getting error

[Steve]

What do your virtual column app formulas look like?

[Fusion_Incense]

for Ex.
MessageBoard Table

Column Name | App formula
Email | USEREMAIL()
Area | [CompanyID].[Area]
Date | [TimeStamp]
MessageWhom | any(select(SalesRep[SalesRep],[Email] = [_thisrow].[Email]))

I’ve regular 11 Columns including 1 _RowNumber and 7 Virtual Columns using only those simple formula, total of 11,xxx rows

MessageBoardImage Table
only one Column has App formula which is

Column Name | App formula
CompanyID | [MBID].[CompanyID]
which has around 365 rows

[Steve]

So this expression:

any(select(SalesRep[SalesRep],[Email] = [_thisrow].[Email]))

is being evaluated 11,000+ times at each sync. That expression scans every row in the SalesRep table. You didn’t mention how many rows that table has, but that number will multiply 11,000+. This is a very expensive expression.

[Fusion_Incense]

@Steve
I See, so using this expression become expensive when data grows? I have 12 Sales in SalesRep table, meaning on every sync it will read 132K times? no wonder is slow. So what would be my best approach? make that Virtual Column is permanent Column? What about any other expression I can use to get that data from other table which is not ref so I can’t use [Email].[SalesRep] right?

[Steve]

A virtual column app formula is evaluated once for each row of the table during a sync. The more rows, the more times the the app formula is evaluated. So, yes, it gets more expensive as the data grows.

An app formula for a normal (non-virtual) column is only evaluated when another non-virtual column in the row is updated (as through a form view, or with an action). Using a non-virtual column would increase speed, but may not give you up-to-date values.

[Steve]

What are all of the column names in the MessageBoard table? That might give me enough information to offer some suggestions.

[Fusion_Incense]

@Steve
For MessageBoard
Regular Column Names are
_RowNumber, TimeStamp, MBID, MBCategory, City, Town, CompanyID, Content, GPSCoordinates, Email, Area
Virtual Column Names are
Date, Time, MessageWhom, SalesAbbr, MessageImage, SalesBelong, CompanyTwon

Only CompanyID is a Ref

[Steve]

I would suspect all of your virtual columns could be made non-virtual. Virtual column values that may change in the future because data they make use may change outside the row itself should remain, but VCs that simply convert other column values may be considered for non-virtual columns. Date and Time, definitely, and MessageWhom.

Actually, I would suggest removing Email and MessageWhom altogether and replacing them with a single non-virtual column, SalesRep, with an initial value expression of:

ANY(
  FILTER(
    "SalesRep",
    AND(
      ISNOTBLANK([Email]),
      ([Email] = USEREMAIL())
    )
  )
)

and set its EDITABLE? expression (Editable_If) to FALSE. Then the SalesRep column would be a Ref to the email’s SalesRep row, and could be readily dereferenced to get its column values (e.g., [SalesRep].[Email]). As an initial value, this expression would only be evaluated when a row is added; the resulting value would then get stored in the non-virtual column for later use without reevaluation. This would be a huge performance win if it would otherwise meet your needs. There would be a challenge of migrating to this new approach, though.

[Fusion_Incense]

@Steve Thank you for the great help!
Maybe I’ll try to remove those expensive expression and see if it works out.