Sync problems I'm getting a timeout on my sy...

(Twenty West KC) #1

Sync problems

I’m getting a timeout on my sync each time from one data table. I know that this data table is large (16,000 rows), but it houses all of the data collected from individual deliveries for an entire year, and needs to be called upon for charting in order to see trends and make predictions. I have already eliminated all blank cells from the spreadsheet, and eliminated unnecessary tables. Is there anything

else that I can do in order to keep it from taking forever to sync/timing out every time?

(Praveen Seshadri (AppSheet)) #2

Seems unlikely that you actually need 16000 rows to do the charting on the app.

Can’t you do the aggregation on the backend (in the spreadsheet) and sync back a summary table that has the data pre-aggregated?

(Twenty West KC) #3

I imagine I could do something like that, though it would be very time consuming. The expressions and virtual columns in Appsheets makes it much easier to select and chart the exact data I need from the table

(Twenty West KC) #4

For instance, I’m charting monthly comparisons for 16 different products delivered to over 120 locations, for an entire year. Also the charts are interactive, allowing for user changes to which wells and months are displayed. To aggregate all that data in the spreadsheet would be a bit of a chore, but if that’s the only way to get Appsheets to sync, then I guess I won’t have a choice?

(Steve Langert) #5

@Twenty_West_KC Can you test your app with Excel?

In the spreadsheet world, Excel is significantly faster than GSheets with large datasets.

(Twenty West KC) #6

@Steve_Langert

I will try this instead, thanks

(Praveen Seshadri (AppSheet)) #7

@Steve_Langert Not for what we’re doing here though. @Twenty_West_KC I don’t recommend Excel unless you are (a) putting in the formilas in the sheet, (b) using Office 365.

@Twenty_West_KC, if this 16,000 row table is read-only, then you can do server caching + delta sync — this will cause it to be synced the first time but subsequent syncs won’t involve much overhead.

Also, is there some obvious partitioniing that is possible — eg: does each user need all 16000 rows? Or is this data broken (for example) into 10 counties, and each user only needs to see data from one county at a time?

(Twenty West KC) #8

@praveen

I had already tried switching to server caching + delta sync but it doesn’t seem to be helping. The table is read-only as well. The table initially loaded with updates available and I changed it to read only, would it need to have been imported as read only for the initial data caching to take effect?

And I wish I could set it up as a partition, there are 40 different larger categories that the 120 locations are broken into, but each user would need to be able to select and see the data from any of these sites.

(Twenty West KC) #9

@praveen

I tried deleting the table and reloading it as read-only and it worked with the server caching this time - I think that was the problem, that I had initially loaded that table with updates available. No longer timing out and it is syncing quite fast.

(Steve Langert) #10

Good to hear.

(Praveen Seshadri (AppSheet)) #11

@Twenty_West_KC, you should read this section on Data Scalability. help.appsheet.com - PERFORMANCE, SCALE, RELIABILITY | AppSheet Help Center

I suspect your app would be a good candidate for data partitioning (into 40 sheets, one for each category) The user would choose a category and the corresponding data would be loaded into the app. This will be very much faster of course PERFORMANCE, SCALE, RELIABILITY | AppSheet Help Center help.appsheet.com

(Praveen Seshadri (AppSheet)) #12

Seems unlikely that you actually need 16000 rows to do the charting on the app.

Can’t you do the aggregation on the backend (in the spreadsheet) and sync back a summary table that has the data pre-aggregated?

(Twenty West KC) #13

I imagine I could do something like that, though it would be very time consuming. The expressions and virtual columns in Appsheets makes it much easier to select and chart the exact data I need from the table

(Twenty West KC) #14

For instance, I’m charting monthly comparisons for 16 different products delivered to over 120 locations, for an entire year. Also the charts are interactive, allowing for user changes to which wells and months are displayed. To aggregate all that data in the spreadsheet would be a bit of a chore, but if that’s the only way to get Appsheets to sync, then I guess I won’t have a choice?

(Steve Langert) #15

@Twenty_West_KC Can you test your app with Excel?

In the spreadsheet world, Excel is significantly faster than GSheets with large datasets.

(Twenty West KC) #16

@Steve_Langert

I will try this instead, thanks

(Praveen Seshadri (AppSheet)) #17

@Steve_Langert Not for what we’re doing here though. @Twenty_West_KC I don’t recommend Excel unless you are (a) putting in the formilas in the sheet, (b) using Office 365.

@Twenty_West_KC, if this 16,000 row table is read-only, then you can do server caching + delta sync — this will cause it to be synced the first time but subsequent syncs won’t involve much overhead.

Also, is there some obvious partitioniing that is possible — eg: does each user need all 16000 rows? Or is this data broken (for example) into 10 counties, and each user only needs to see data from one county at a time?

(Twenty West KC) #18

@praveen

I had already tried switching to server caching + delta sync but it doesn’t seem to be helping. The table is read-only as well. The table initially loaded with updates available and I changed it to read only, would it need to have been imported as read only for the initial data caching to take effect?

And I wish I could set it up as a partition, there are 40 different larger categories that the 120 locations are broken into, but each user would need to be able to select and see the data from any of these sites.

(Twenty West KC) #19

@praveen

I tried deleting the table and reloading it as read-only and it worked with the server caching this time - I think that was the problem, that I had initially loaded that table with updates available. No longer timing out and it is syncing quite fast.

(Steve Langert) #20

Good to hear.