Sync delays

[Chris_Bean1]

Hi everyone

I have developed a farm management/sales app for a client, which has been working fine for 9 moths, but has run into some major delays. It runs off an excel spreadsheet inside a sharepoint instance, hosted by the client on their own microsoft login. 

Users are reporting delays in excess of a minute for syncing. The app is a sales tool, so trying to keep up small talk whilst chatting with a farmer for that long is impractical. I've consulted this forum and read the articles, and tried the more basic things to improve sync, but the spreadsheet just isn't that big - 14 active tables with a max of 8000 rows. Other client apps with google sheets with more tabs and rows work fine. 

I've contacted appsheet upport to review the app, highlighting that they should contact me on a different email address from the one associated to the apps, as the app email address only has sharepoint access, and email has not been configured for it.

Some of the client's sales agents have stopped using the app as they find it impractical, so I'm really concerned about losing the client. 

They are due to pay the four-figure annual fee for Pro licenses now, and I'm concerned they will use these issues to refrain from paying, thus losing business for me and for Appsheet. 

I'm at a point where I'm willing to pay an expert to go through the whole thing to improve performance. Please give me any suggestions. 

Thanks

Chris

[Marc_Dillon]

14 tables and 8k rows isn't that big. 1 minute sync times is really bad for that for sure.

Are you using many Virtual Columns? Perhaps with nested select()s?

Would you be willing to transfer your data source to a GSheet, it would probably work a little bit better.

I'm willing to pay an expert to go through the whole thing to improve performance.

---

You can find myself, as well as others, on freelance platforms like Upwork.

[Chris_Bean1]

Thanks Marc

There are only the virtual columns that are created by 'ref' column types. No others. There are some excel formulas in the sheet, but not enough to cause that level of delay. 

The users are on the browser versions, on microsoft Surface Pros. They often are offline due to being on farms, but even on wifi, syncing is taking up to a minute. 

We are unfortunately not able to move to google sheets, as the client uses MS365 exclusively. 

Thanks for Upwork recommendation. 

If anyone else has any ideas, please help... 

Thanks

Chris

[Marc_Dillon]

Did you check the performance analyzer?

[Chris_Bean1]

Hi Marc

Thanks for your reply. I found the performance analyser here, it shows the virtual columns are chewing up the time, upwards of 20 seconds. I've made the image below as big as possible, let me know if you can see it. 

I checked another app and it's more like 0.5 seconds per virtual column. This would appear to be an appsheet thing rather than an Excel thing, is there any way to speed these up? 

Cheers

Chris

[Chris_Bean1]

Having read further about VCs, the VCs in the perf analyser appear to just be the REF_ROW VCs, which according to the post below aren't actually the problem???

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Improving-performance-by-getting-rid-of-unneces...

I've got maybe 2 spreadsheet formulas in there, nothing else, so I'm really struggling to find a reason for the slow sync... Note I've got various other apps all of which run sub-5 seconds, and they're more complicated! 

Any further help appreciated.

Chris

[Marc_Dillon]

Right. Ref_rows is calculated in parallel with the loading of the related table, so it adds very minimal amounts of sync time. It's representation on the chart just shows you how long those tables are taking to load.

There's more to the perf analyzer, scroll down to see individual syncs, and you can drill-down, from the binoculars icon, to see exactly how long each part of the sync takes. 

Your sync time appears to be only from loading lots of records. But I thought you said you didn't have very many.

[Chris_Bean1]

Thanks Marc - as per the initial post, the max is around 8000 rows, which as you commented yourself, isn't that much. I've turned on Delta Sync and server caching, neither of which have made a big difference. I'm not sure about turning on quick sync as it appears to still be experimental. 

In terms of the individual syncs, I didn't have enough to look at last week due to the 7 day limit and the payment issues, but as of this morning I have a few, screenshots below. The average in the main chart is still sitting at 50 seconds, too long, with binoculars showing as follows:

The hungry ones are farms and orders, which makes sense as they have the most related rows in another tab.

Dtail of table reads here

Any further thoughts appreciated

Cheers

Chris

[Marc_Dillon]

I would copy the app to GSheets and see if the sync time is the same.

[Chris_Bean1]

Done - same data, but in google sheets, on a free version rather than core. It's faster, but not by much, and it takes 40 seconds to fetch app then sync in the app editor, having timed it. 

So it's better, but probably not enough to convince customer to switch to Google sheets (get IT on board, train team of 4 admins to use google sheets, etc etc). 

Meanwhile I have another app on Google sheets (Core subscription) with 12 tables, of which 4 have 4000 rows, and it shows thus:

Much, much better - this would suggest there is something about the app above (the slow one) that is inherently slowing it down, rather than a difference between Excel and Sheets.

Again, any help appreciated

Chris