read-only data: columns or separate workbook (file)?

[Charlie_Wells]

I have several read-only columns in my primary table, sprinkled among the columns that can be updated by the user. My app is very slow to sync, and the problem is not virtual columns, per the performance analyzer. Rather, it just seems to be slow reading in the data.  

So, questions:

a) Is it better to keep them in a separate workbook (Google sheet) rather than in the same spreadsheet as the data that the user can update?

b) When the app syncs to the server, does it read all the data every time it saves each row? (I hope I'm phrasing that correctly). Some time ago, when we hadn't been able to sync for days, it would spend about three minutes syncing up each row. 

Thanks.

[Charlie_Wells]

I meant to post this on Q&A, unable to delete it from here (Delete does nothing). Sorry.

[Steve]

@Michelle ^^

[Michelle]

@Charlie_Wells @Steve,

This has been moved. 

[Steve]

Perhaps post a screenshot of the performance analyzer output around a sync?

---

@Charlie_Wells wrote:

Is it better to keep them in a separate workbook (Google sheet) rather than in the same spreadsheet as the data that the user can update?

---

I'd expect the best performance by keeping everything in as few workbooks as possible.

---

@Charlie_Wells wrote:

When the app syncs to the server, does it read all the data every time it saves each row? (I hope I'm phrasing that correctly). Some time ago, when we hadn't been able to sync for days, it would spend about three minutes syncing up each row.

---

I can't say for sure, but but may potentially.

How big is your data set?

[Charlie_Wells]

Thanks @Steve. Here is what the performance analyzer shows happens when I sync the table when starting the app. I have a security filter that reads in only today's data. When the crew starts the app in the morning there is no data for the day yet (of course there's a pile of data from previous days [four years' worth] on the server that the security filter rejects), so the only data it reads are the non-timestamped columns that are read-only (lists of member names, links to images etc).

The google sheet is about 3.5MB. The data that needs to be written out daily is about 40-50 rows (of 88 columns, about 6 of which are read-only, as I mentioned).

Virtual columns seem to not take a significant time when syncing at startup; fetching the data from the source seems to take the bulk of the time. (1:55 compared to 00:00:02 for the virtual columns)

As Bill Gates famously asked us at a developer meeting years ago when Windows was taking an eternity to shut down, "What is it doing?"

[Steve]

I'm not really sure what to look at here. It does not appear to be virtual columns, as you noted. But... Does the table have any virtual columns? If so, what do their App formula expressions look like? Let's just do our due diligence there.

In the past, there have been performance issues during load with file type conversions, but those affected data loaded from databases, not from Google Sheets.

What does the security filer expression look like for this table?

[Charlie_Wells]

The security filter is simply [Date]=TODAY().

I've attached three screen shots from the analyzer, I have too many tables and they don't all fit in the analyzer window when expanded (and the window can't be resized...)

This is from the log when I  synced when starting the app at my end just now. The crew has already entered a few rows of data today, and they have synced it up. So there were rows to read.

All three tables have virtual columns. The only ones that appear to be computed are Refs to other (read-only) tables. I do have about 40 virtual columns out of a total of 88 in the HMC Log Data table that I use as temp variables to store values entered by the crew for the current passenger, that I then use to do some internal math to compute charges (we have a complicated decision tree to determine ferry charges) and also to concatenate a string to print for the crew on their summary view and also to include in the daily report.

Sync took 57 seconds. 

I'm not sure how to interpret any of this, and I don't want to waste your time. To minimize how long the crew has to wait, should I instruct them to just sync once a day? Or at the end of each run (ten runs a day)? Will delta sync help?

Thank you.