SYNC timeout behavior

AppSheet AppSheet Q&A
, ,
1

Hello,

For the last two months, I’ve focused only on app optimization and fixing performance issues.

The sync time is usually between 4–10 seconds, even though my tables are almost empty. I think that’s acceptable due to size.

However, I’m testing the app with multiple concurrent users to simulate real-world traffic. A few times each day, the sync gets completely stuck. When I open the spreadsheets, one of them (it’s random every time) is also stuck and returns the error shown in the log below.

The first group of users receives a 240-second timeout, the second group 180 seconds, the third 60 seconds, and so on. It looks like there is a one-minute cooldown, which might be related to an API quota or some other Google limitation.

I managed to reduce my data structure from 39 tables (37 in Google Sheets and 2 in AppSheet Database) spread across 20 spreadsheets down to 9 spreadsheets.

There are:

  • no formulas,

  • no empty rows,

  • no complex conditional formatting,

  • no scripts running when the error occurs.

I only have a few scripts running late at night and a few every four hours, each taking only 4–10 seconds to complete.

I’d like to move more tables to AppSheet Database, but even empty tables have a fixed sync overhead of around 3 seconds, compared to about 0.5 seconds with Google Sheets.

I also tried moving the data to Microsoft OneDrive, but I still experienced sync issues, possibly related to authentication tokens or something similar.

Half of my tables use the following security filter:

IF(
  LOOKUP(USERSETTINGS("ID"), "Registry", "ID", "Permission") >= 100,
  TRUE,
  LOOKUP(USERSETTINGS("ID"), "Registry", "ID", "Client") = [Client]
)

At this point, I don’t know whether the issue is related to AppSheet or Google.

I need to deploy the app to my team for further testing, but right now I’m blocked and starting to consider moving away from AppSheet.

Error:
“System.Threading.Tasks.TaskCanceledException: A task was canceled.\n at ServerHandler.SyncRequestHandler.ReadTablesWithParallelForEach(IPerfCollector perfCollector, Context context, IReadOnlyDictionary2 perTableParamsFromClient, String appTemplateName, AppTemplate appTemplate, DataFormat desiredFormat, IList1 appTables, AppErrors appErrors, ConcurrentDictionary2 tableDataOut, CancellationToken cancellationToken) in /src/ControllerHandler/ServerHandler/SyncRequestHandler.cs:line 905\n at ServerHandler.SyncRequestHandler.ParallelReadTables(IPerfCollector perfCollector, Context context, String appTemplateName, AppTemplate appTemplate, AppTemplate smallClone, String tableTimestampsFromClient, Dictionary2 perTableParamsFromClient, DataFormat desiredFormat, Boolean warnClientUpdate, CancellationToken cancellationToken) in /src/ControllerHandler/ServerHandler/SyncRequestHandler.cs:line 661\n at ServerHandler.SyncRequestHandler.ExecuteSyncRequestAsync(IPerfCollector perfCollector, Context context, AppSyncRequest request, Boolean cookiesHasStartWithToken, String requestUrl, Uri referrer, String webViewParameter, String androidParameter, String isCompliantParameter, StartAppToken startAppToken, String appver, CancellationToken cancellationToken) in /src/ControllerHandler/ServerHandler/SyncRequestHandler.cs:line 438”

2

Why? I recommend avoiding AppSheet Database. It has numerous bugs and limitations, and is poorly supported. Google Sheets is a much better platform.

Non-Google-based data source hosting should be avoided, except for an SQL database.

This expression could be simplified to this:

OR(
  LOOKUP(USERSETTINGS("ID"), "Registry", "ID", "Permission") >= 100,
  LOOKUP(USERSETTINGS("ID"), "Registry", "ID", "Client") = [Client]
)

Note that your use of LOOKUP() here is expensive: every row of the Registry table is scanned at least once and more-likely twice for every row of the data table. These security filters are likely the cause of your baseline sync times.

Also, this security filter is unsecure: user settings values are changeable by users. A savvy user could change their ID to elevate their privileges.

Users should be strictly identified by USEREMAIL(). There is literally no other way to securely identify a user. If you aren’t using a license that allows logins, it is impossible to secure your app.

That said, to improve the performance of your app, create a slice named (e.g.) My Registration with a row filter expression of [ID] = USERSETTINGS("ID"). This creates a one-row slice containing only the current user’s registration.

Then replace your existing security filters with this:

OR(
  ANY(My Registration[Permission]) >= 100,
  ANY(My Registration[Client]) = [Client]
)

ANY(...) grabs the first value of the given list, which will be the corresponding column value from the one row of the My Registration slice.

3

Thank u for reply :wink:

Iam still getting (but less) sync stuck on random table (even read only), yesterday only twice.

Complete timeout
Read a single table 00:03:57.7235620 {“TableName”:“issues”,“Timestamp”:“09.39.12.769205”}
[internal] GetTableData 00:03:57.7235590 -
[internal] DataLayer_GetTableData 00:03:57.7235489 {“TableName”:“issues”}
Read table rows 00:03:57.7205806 -
[internal] GDriveStorageProvider_ReadTable 00:03:57.7205093 -
[internal] GoogleSheetData_Read 00:03:57.7199385 -
[internal] GoogleSheetDataV4_ReadSheetRows 00:03:57.7199360 -

Almost
Read a single table 00:00:38.1982171 {“TableName”:“readings”,“Timestamp”:“11.35.07.468341”}
[internal] GetTableData 00:00:38.1981734 -
[internal] DataLayer_GetTableData 00:00:38.1981659 {“TableName”:“readings”}
Read table rows 00:00:38.1962757 {“RowsRead”:“26”,“ColumnsRead”:“13”}
[internal] GDriveStorageProvider_ReadTable 00:00:38.1950003 {“RowsRead”:“27”,“ColumnsRead”:“13”}
[internal] GoogleSheetData_Read 00:00:38.1948992 -
[internal] GoogleSheetDataV4_ReadSheetRows 00:00:38.1944913 -

To reduce the number of Google Sheets files and minimize the chance of a table getting stuck.
Due to limit of 2500 rows per ASDB is useless for usual stuff but for small read only or lookup table should get job done, but fixed sync time is awfull.

OR(
  ANY(My Registration[Permission]) >= 100,
  ANY(My Registration[Client]) = [Client]
)

I had this formula before, but I was afraid to use slice in a security filters because of circular dependencies (cycling formulas).

I was hoping that the IF statement would be short-circuited.