Office.js性能:我应该在一个Excel.run函数中放入多少? [英] Office.js Performance: How much should I put into one Excel.run function?
问题描述
我正在处理一些大型电子表格(约30,000行)，并且遇到了一些性能问题，并且遇到了以下一些与性能相关的问题:
我可以或者最好将一个cc功能塞入一个函数?我需要考虑哪些事情以确定何时将事情分解为多个Excel.run呼叫?
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?
会打电话给await ctx.sync()或多或少这样做吗?
Will calling await ctx.sync() more or less often do anything to help in relation to this?
编辑:
这个问题的产生源于: https://stackoverflow.com/a/44424045/3806701
This question was motivated by: https://stackoverflow.com/a/44424045/3806701
推荐答案
对于不尽快返回该线程的歉意.
Apologies for not getting back to this thread sooner.
几个观察结果:
-
如果要处理大量的范围(范围是特殊的)，则肯定要以尽可能大的块进行操作(例如，将值设置为一个巨型数组)而不是逐个单元格地设置值并创建一堆Range对象).创建 any 新API对象并不便宜，但是Excel Ranges特别是 不便宜.团队目前正在调查一些性能问题，完成调查后，我应该可以分享更多的信息.
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.
就内部实现而言，您可以将Excel.run视为任务容器(可能像C#中的"using"语句)?尤其是Ranges，如果您超过数千(可以尝试)，事情的确会开始放慢速度.因此，从这个角度来看，您确实希望尽快而不是稍后发布Excel.run(不要在等待用户输入的情况下暂停10分钟)，并且-直到我们针对上面提到的调查找到解决方法为止-您可能确实想将Excel.run -s保持较小，以减少内存占用.
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.
话虽这么说，除非您在1000个API对象上创建1000个对象，否则在通常情况下，每个"context.sync"或"Excel.run"都是进程边界或网络往返.因此，从这个角度来看，将上下文传递给帮助器函数要好于拥有几个独立等待的Excel.run-s.
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.
有关更多信息，我建议您阅读使用Office.js构建Office加载项，由Yours Truly预订(但要把所有利润都捐给慈善事业，这样一来，当我向人们推荐它时，我就不必感到内gui :-)).如果您好奇的话，会在内部实现上有一个很长的篇幅(共11页)，尤其是对于Ranges，阅读"一种特殊(但很常见)的情况:没有ID的对象"可能会很有趣. ".您可能还会发现"更复杂的context.sync示例"部分很有用，尤其是在我为"将工作分解为多个子例程"
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"
希望这会有所帮助！
这篇关于Office.js性能:我应该在一个Excel.run函数中放入多少?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
