"Least expensive" method to define the relationship of items and item images (folder as a table)?

[amikorgaleria]

I have a consistently increasing inventory of about 5000 items, each with its own gallery of images (at least two, but could even be 10). Each item has an sku column (eg: 5372) and the images are named so:

5372(1).jpg, 5372(2).jpg, 5372(3).jpg

These images are then uploaded to a dedicated folder on Google Drive, which is the folder that is defined as a table in my app. 

I needed to define a ref column in the item_images table, for the items to each have an image gallery in the app. This is the formula I used:

#1 ANY(SELECT(item[ID], [sku]=[_THISROW].[ref_item_ID], TRUE)) 

ref_item_ID (I extracted the ske from the image file name to get the sku as follows:)

#2 INDEX(
EXTRACTNUMBERS(TEXT([File])),1
)

Now that the images folder has over 10k files and each has a row in the item_images table with #1 as one virtual column, the sync times are getting so high (80+sec), that the app start times out half the times I start it.

My question: what is a much more effective method to establish the relationship between items (normal table) and item_images (folder as a table)?

[1minManager]

Refer to my previous answer in https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Unable-to-fetch-app-definition-Error-Data-table... and come back with exactly where the app is spending most sync time.

[amikorgaleria]

For the item table, the most expensive operation is "Read table rows" from the related item_images table  / 51sec

For the item_images folder as a table, the most expensive operation is "Read rows from data source" / 53sec

[amikorgaleria]

These above are the operations that take the most time, nothing else even comes close to 5sec, even though I'm using loads of "expensive" virtual columns across a dozen tables.

[1minManager]

50secs sounds unusally long for one table.  Check out one of mine with a 61,000 row x 40 column table.  Just 12secs to read the table data and 7.8secs for the subtable link. 

You using Google Sheets or something else?

[amikorgaleria]

This is a folder as a table, ie: 10k images in a Google Drive folder (a feature of Appsheet). If you don't mind me asking, what platform do you use for your data tables? I've been using Google Sheets without any problems so far.

[1minManager]

Google Sheets for all my apps.  But i've never done a google drive folder as a table.  Maybe thats why its a bit inefficent

[Suvrutt_Gurjar]

@amikorgaleria 

Would you please be able to reconfirm and share if possible the sync time screenshot of the following VC from performance monitor?

#1 ANY(SELECT(item[ID], [sku]=[_THISROW].[ref_item_ID], TRUE)) 

In general, this VC is performing lookup across two large tables and it appears to be a good candidate for sync time contribution.

Just in case this VC is indeed significantly contributing to sync time,  there could be possibly ways to reduce the sync time of the VC.