Seeking Strategies to Enhance Sync Efficiency for App with Unique Value Validation

[shivaannshhu]

Hello AppSheet Community,

I am seeking advice on improving sync performance for an AppSheet app connected to a Google Sheet that is experiencing slower sync times due to increased data volume. The app captures a 'Scan Code' for each new entry and verifies its uniqueness against all existing entries.

Current Approach:

• New 'Scan Codes' are entered into the Google Sheet via the AppSheet app.
• Additional columns are automatically populated using expressions in the 'Initial Value' settings within AppSheet.
• 
• The 'IsUniqueScanCode' column, in particular, uses an expression to check the uniqueness of each new 'Scan Code' against the entire dataset, which is growing and currently contains over 10,000 entries.

Issue:

• A recent sync error has occurred , and the sync times are lengthening as more data is added.
• The 'IsUniqueScanCode' check seems to be contributing to the bottleneck.
  
  

  Expressions Used:

  • The 'IsUniqueScanCode' column uses the following AppSheet expression:
  """IF( COUNT( FILTER( "Central Kitchen", AND( [Scan Code] = [_THISROW].[Scan Code], [_ROWNUMBER] < [_THISROW].[_ROWNUMBER] ) ) ) = 0, TRUE, FALSE )  """"

  Questions and Potential Solutions:

  1. How can I optimize the 'IsUniqueScanCode' verification? Would restricting the check to the last 500 entries effectively reduce sync time without significantly impacting data integrity?
  2. Could other initial value expressions for 'Product Code' and 'Production Date' also affect sync performance? They are currently simple string manipulations.
  3. Would shifting the computations from AppSheet initial value expressions to Google Sheets array formulas improve sync efficiency? Although it might move the computation burden to the Google Sheets side, will it allow AppSheet to record new scan codes faster?
  4. What are the best practices for maintaining swift sync times in AppSheet, especially when real-time updates for dashboards are necessary?

  I am considering all viable options to achieve a balance between real-time data updates for dashboard purposes and efficient app performance during data entry.

  Thank you for your insights and suggestions.

[WillowMobileSys]

First, in the Sync error dialog box, you need to tap on the "View Error Details" button to see what messages you are getting.

When it comes to Sync performance issues, assuming connectivity is good, there are really two main things that contribute to slowness:

1)  Amount of data in the app
2)  Process intensive Virtual Columns - i.e.  expressions that scan tables - especially multiple table - and select Lists of data.

Your Initial Value expressions are run only when a new row is added and DO NOT contribute to Sync time.

Suggestions to improve Sync Times

Since #1 above also contributes to #2, the quickest, easiest way to speed up the performance is to reduce the amount of data that needs to be downloaded into the app.  And the easiest way to do that is to remove older, no longer needed rows. 

Many times an app needs data for only a day or two and then can be physically deleted/purged.  This can be done manually or you can create an automated process to purge the data on some schedule - in the app or with something like Google Scripts.

If you can't purge data, then maybe you can filter it.  AppSheet uses Security Filters to limit, by table, the data downloaded to a device.  Only the rows meeting the filtering logic are downloaded.

Keeping the downloaded data size small is essential to maximizing the performance of Syncs. 

You also want to avoid any lengthy Virtual Column calculations.  If you don't know,  Virtual Column are re-calculated on each Sync.   Don't mis-interpret this.  It does NOT mean to avoid Virtual Columns.  They are necessary and you will likely need Virtual Columns as you build out your app.  What you don't want are lengthy Virtual Column calculations that are performed on every Sync when the data doesn't change or at least doesn't change often.

Question

Where does the Scan Code come from and how is it used?

[shivaannshhu]

Scan Code comes from a form in the app and it takes input from a barcode scanner.

 
I have a doubt. Isn't the data only downloaded when the app is loaded/opened? The sync issue I faced was in saving the form view's entry. Even now, every barcode entry takes 2 seconds to sync to Google Sheets. In the beginning, it used to happen almost instantly.