search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
690 stars 95 forks source link

Slow cell processing when the text wrapping is presented #3774

Closed strangecamelcaselogin closed 6 months ago

strangecamelcaselogin commented 1 year ago

Provide required information needed to triage your issue

We have a feature that allows users to remove formulas from a sheet upon request. We are aware that cell processing performance is typically slower on the web compared to the desktop app. However, we have also observed that, in some workbooks, this performance is even slower. In these cases, the execution of context.sync after updating cells may take up to 30 seconds, while in other cases, it is completed in 3 to 5 seconds or even faster.

After doing the research, I identified a trigger: enabling the text wrap option for cells slows down the context.sync called after updating them by an average of 3 to 6 times.

Your Environment

Expected behavior

The execution time of context.sync after updating the cells should be comparable, regardless of whether the "wrap text" option is enabled on the cells.

Current behavior

If the cells have the "wrap text" option enabled, context.sync called after updating them takes 3 to 6 times longer to execute.

Steps to reproduce

Run prepared snippet on a new workbook, then:

  1. Press the "Init sheet" button to generate the data
  2. Press the "Remove formulas" button to remove the formulas
  3. Press "Init sheet" button again to regenerate the data
  4. Press the "Apply text wrapping" button
  5. Press the "Remove formulas" button and compare the results

Logs for my result:

Formulas generation completed
All formulas removed. Total time: 1562 ms, long sync: 1341 ms
Formulas generation completed
Text wrapping enabled
All formulas removed. Total time: 6874 ms, long sync: 6654 ms

Link to live example(s)

  1. https://gist.github.com/strangecamelcaselogin/ae31fd68ac1da09b0b8c5407c2ba44a8

Provide additional details

  1. The update of a single cell occurs at line 54
  2. The context.sync that I mentioned above is at line 65
  3. In our production code, we are processing the sheets in batches, and this snippet represents the actions taken in a single batch
  4. We are processing the cells one by one intentionally because, in some cases, we can't use more efficient methods like range.copyFrom(range, 'Values')

Context

I'm trying to improve the performance of the formula's removal that we already have. The removal is conditional: we may leave some formulas in place. Additionally, we should be able to process spill ranges and pivot tables.

donlvMSFT commented 1 year ago

Hi @strangecamelcaselogin ,

Thanks for reported this, we could verify this behavior on Excel online version. But from production perspective, the performance after word wrap is still in the scope of expected behavior, so we won't change the current behavior recently.

Please let us know if this blocks you, that would help us prioritize this issue.

Thanks a lot!

strangecamelcaselogin commented 1 year ago

Hi @donlvMSFT,

This is not blocking us, but it creates an inconvenience for our web users. On large workbooks, the acceptable wait time of 1 to 2 minutes when using the desktop app turns into a wait time of 5 to 10 minutes on the web. This may be perceived by our users as a "freeze". I'm just surprised that word wrap has such a significant impact on the web, but doesn't affect the desktop app.

For now, we'll implement the disclaimer to use the desktop app for the impacted feature, but we look forward to improvements.

Thank you!

donlvMSFT commented 7 months ago

Hi,

Sorry for the very late reply, we're revisiting this issue, want to share something we found. During validation from our machines, we found that the time spent for desktop and online not have much difference, like: online with wrap ≈ win32 with wrap ≈ win32 without wrap, and yeah, online without wrap is much better than these.

We're validating using the snippet you shared before, and want to check with you mentioned that: 1 to 2 minutes when using the desktop app turns into a wait time of 5 to 10 minutes on the web. Seems I couldn't reproduce, desktop shares similar behavior with web version, could you share more detail or any insights on this?

Thanks a lot!

donlvMSFT commented 6 months ago

Thanks for reporting this issue. After careful consideration, our team has decided to not fix this issue in the short term. We recommend you to upvote or submit a new Tech Community New Ideas item, this will give us visibility of the commonality of it and will help with our prioritization. If we end up working on this issue, we will re-activate as needed.