Delayed syncing issue

[Mark_Taylor]

I am having an issue with virtual columns data pulled from another table taking 10-20 seconds to sync into the display of the current record I am looking at. Basically a virtual column inside a virtual column.

This is occurring at multiple points but let me attempt to simplify it for purposes of this discussion.

Four tables sourced from Google sheets

1. ClientSessions
2. ActiveRecord
3. ObjectivesAssignments
4. ObjectiveData

And we have SessionA and SessionB and Objective1

When I enter SessionA the ActiveRecord table checks to see if there is a active session record for my user. If so it deletes it and adds a new row reflecting that SessionA is my active record.

I have a ClientSession table that has a virtual column (TableView) of all the ObjectivesAssignments for the client of the session I am viewing.

That table view has a column Called data that is a virtual column that looks up a record in ObjectiveData that belongs to this ObjectivesAssignments and session using this expression

Expression

text(any(Select(ObjectiveData[Count],
and([Objective Assignment] = [_thisrow].[ID],
[Session] = LOOKUP(any(Select(ActiveRecord[ID],
and(
[UserEmail] = useremail(),
isnotblank([SessionID])
))),“ActiveRecord”,“ID”,“SessionID”)
))))

For this example let’s say that
(A1) SessionA Objective1 = 5
(B1) SessionB Objective1 = 10

I log in select A1 and see 5. I go back and select B1. I still see a 5. if I wait 10-20 seconds the 5 will turn into a 10. I I select the row during that 10-20 second window I will see the correct value (10). If I click the sync button rather than wait the 10-20 seconds the value in the table will switch to 10.
If then go back and browse to A1 I see the 10 until everything syncs (10-20 seconds) then I see the correct value 5.

I found that this problem goes way if I turn off delayed sync. But then the user must wait for a sync to occur (10-20 seconds) for every little change that is made.

[Steve]

Virtual columns that include queries (FILTER(), LOOKUP(), MAXROW(), MINROW(), or SELECT()) are very compute-intensive. Virtual columns that include queries three-deep are at least three times as intensive. The more data you have, the slower sync is going to get; the more complex your queries, the faster the sync process will get slower. You should probably reconsider your use of virtual columns.

See also:

help.appsheet.com

App Performance: Core Concepts

In order to make apps run faster, you need to first understand some core concepts.

help.appsheet.com

Improve the Speed of Syncs with Data Updates

There are four ways to improve Sync performance when the app makes updates.

help.appsheet.com

Performance Profile

How to get the most out of the performance information for your app.

[Steve]

Here’s your expression, reformatted to my preferences:

text(
  any(
    Select(
      ObjectiveData[Count],
      and(
        [Objective Assignment] = [_thisrow].[ID],
        [Session] = LOOKUP(
          any(
            Select(
              ActiveRecord[ID],
              and(
                [UserEmail] = useremail(),
                isnotblank([SessionID])
              )
            )
          ),
          “ActiveRecord”,
          “ID”,
          “SessionID”
        )
      )
    )
  )
)

Consider this subexpression:

LOOKUP(
  any(
    Select(
      ActiveRecord[ID],
      and(
        [UserEmail] = useremail(),
        isnotblank([SessionID])
      )
    )
  ),
  “ActiveRecord”,
  “ID”,
  “SessionID”
)

You’re querying ActiveRecord to get the ID one specific row (any(Select(...))), then using that ID to query ActiveRecord again to get the SessionID of that very same row. This is inefficient. Instead, you could get SessionID with one query:

any(
  select(
    ActiveRecord[SessionID],
    and(
      [UserEmail] = useremail(),
      isnotblank([SessionID])
    )
  )
)

Here’s your expression with that change:

text(
  any(
    Select(
      ObjectiveData[Count],
      and(
        [Objective Assignment] = [_thisrow].[ID],
        [Session] = any(
          select(
            ActiveRecord[SessionID],
            and(
              [UserEmail] = useremail(),
              isnotblank([SessionID])
            )
          )
        )
      )
    )
  )
)

[Mark_Taylor]

Steve,

Thanks for pointing that out. It has been bugging me that I should find a more elegant solution for that.

I put your suggestion into my code and it still works as before to get the same data. But I am still getting the delay.

Here is another way of saying it (hopefully this makes sense)

That same expression is used to return:
–ObjectiveData[Count] to display in the table
–ObjectiveData[ID] for the action assigned to clicking the row
"LinkToRow(“Expression returning ObjectiveData[ID]”,“ObjectiveData_DetailView”)"

Without pressing the sync button the table takes >20 Secs to display correct value. But clicking the row takes me to the correct record. (even thought he row was showing the old value when I click it).

I disabled all of my formatting rules (recommended by the performance analyzer) to see if that would help but it has not.

[Steve]

Most virtual column values simply will not update until a sync occurs, which is what’s happening during that delay. Virtual column values will update immediately only if their row is otherwise updated (through a form or by an action).

[Mark_Taylor]

Steve,

Thanks for the feedback. My rows have (+/-) line actions and I had notice that if I added/subtracted any row the value immediately updated. I.E. if I hit + and then immediately - I could see the actual value without waiting for the update.

In theory I could deign an action that does for each row but every time I browse to another objective, but that would add some serious numbers to my sync que so I think I will search for another solution in the mean time.

Thanks again