Hi - I recently moved my company App from google sheets / Drive location to an excel workbook on the company's SharePoint. In doing so I noticed the sync times tripled or more and looking deeper in the Performance Analyzer I noticed it appears to read the same table 3 times...would appreciate if someone could give me a heads up on what is happening here and how i might fix my terrible sync issues? Many Thanks
Any chance that you have multiple virtual columns reading values from this same table more than once?
I do have a lot of virtual columns and maybe i need to just rebuild this (built it years ago fumbling my way through for a few months and its just cruised through great no troubles for the last 4 years - though slow to sync)...is it the Select formulas? i see there are 3 instances in the analyzer but i think i only have one select formula?? And is the virtual columns only a drain on sync time....are they also an issue on writing back to the spreadsheet?
And in my head I thought if it is writing to the spreadsheet(adding a row on the save action) it would only access the one table that holds all the records? and not some of the other ref tables...hhmmm what am i missing here?
Every time when the app is syncing, it recalculates all virtual columns. If you are reading values a lot from different tables, it affects your sync time. It doesn't matter if it's a SELECT(), MAXROW(), LOOKUP() etc.
Ok thanks - I am understanding the sync side...but not understanding the 'Operation' of "Add Row" where you save the new row back to the sheet and how I have 3 instances of 'read table rows' pop up - is it possibly a bot/action? Looking deeper - I do have 2 actions that clears data out of 2 columns if the user changes one of the other column inputs (that turns off other columns in the row) - not sure if there is a better way to clear data if the user goes back and changes key columns? Thanks for the feedback
Those could explain it. Though it's easy to test if you disable them temporarely.
Ok I think I am getting it...I turned the actions off to clear the columns and that pulls me down to only one read table row...woo hoo - just knocked out 30 seconds of sync time...thanks heaps....now just need to work on the other 38 seconds - a more detailed view tells me i have 16 seconds related to MicrosoftProvider_WriteTable (not sure what i can do about that??) and one virtual column running 15 sec which is trying to get the last time entry for a previous log so as to default it as the next log's start time - is there a better way to do this:
I use a virtual column "lastrow" = maxrow(Tamping Logsheet, _RowNumber,[_ThisRow].[Shift Key1]=[Shift Key1])
then set the initial value for [Start Time] =[lastrow].[FINISH TIME]
User | Count |
---|---|
32 | |
31 | |
30 | |
18 | |
17 |