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
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]
Those could explain it. Though it’s easy to test if you disable them temporarely.
1 Like