Office.js Performance: How much should I put into one Excel.run function?

Question

I'm working with some large spreadsheets (~30,000 rows) and running into some performance issues and have some of the following performance related questions:

How much can I, or better yet should I, cram into one Excel.run function? What are things I need to consider to determine when to break things out into more than one Excel.run call?

In general, how many ranges should I create inside one call?

How big of ranges should I be using before I need to perhaps split up one large range into multiple smaller ranges?

Will calling await ctx.sync() more or less often do anything to help in relation to this?

EDIT: This question was motivated by: https://stackoverflow.com/a/44424045/3806701

Could you please explain the -1 so I can do better in the future? Thanks. — cs_pupil, Jun 08 '17 at 17:01
It's a very good question. It takes a fairly long answer to do it justice, though, so let me try to get to it in a few day with a detailed answer. — Michael Zlatkovsky - Microsoft, Jun 08 '17 at 17:28
Generally, is it better to have `Excel.run` **only once** in my top level function (and pass ctx down to nested functions) or is it better to use `Excel.run` **inside each nested function** or does it matter? — cs_pupil, Oct 16 '17 at 22:28
apologies for not responding sooner, I had every intention to get back to this thread, but it fell off my radar. Please see my answer below. — Michael Zlatkovsky - Microsoft, Oct 23 '17 at 02:06

score 2 · Accepted Answer · answered Oct 23 '17 at 02:05

Apologies for not getting back to this thread sooner.

Several observations:

If you are manipulating a large number of ranges (ranges are special), you definitely want to do them in as large of blocks as possible (e.g., set values as one giant array on a single range, rather than set values cell by cell and create a bunch of Range objects). The creation of any new API object is not cheap, but Excel Ranges are particularly not cheap. There are also some performance issues that are being currently investigated by the team, I should be able to share more once we've finished the investigation.
In terms of the internal implementation, you can think Excel.run as task container (maybe like a "using" statement in C#)? And with Ranges in particular, if you go over a couple thousand (you can try it experimentally), things do start slowing down considerably. So from that perspective, you do want to release your Excel.run sooner rather than later (don't do a 10-minute pause waiting for user input in it), and -- until we have a workaround per the investigation I mention above -- you probably do want to keep your Excel.run-s reasonably small, to let that memory footprint decrease.
That being said, unless you are creating 1000s upon 1000s of API objects, in normal circumstances each "context.sync" or "Excel.run" is a process-boundary or network roundtrip. And so from that perspective, passing down the context to helper functions is preferable to having several different independenly-awaited-upon Excel.run-s.

For more information, I encourage you to read Building Office Add-ins using Office.js, a book by Yours Truly (but with all profits to charity, so that I don't have to feel guilty when I recommend it to folks :-)). There is a long (11 pages) section on the internal implementation if you are curious, and for Ranges in particular, you may find it interesting to read "A special (but common) case: objects without IDs". You may also find the section on "A more complex context.sync example" useful, particularly around my prescribed pattern for "Splitting work across multiple subroutines"

Hope this helps!

Office.js Performance: How much should I put into one Excel.run function?

1 Answers1

Linked