5 ms·

I'm curious, is there a way to "profile" an excel application? If you suspect a sheet is taking longer to calculate than it should are there tools that can help

I'm curious, is there a way to "profile" an excel application? If you suspect a sheet is taking longer to calculate than it should are there tools that can help you drill down to discover the bottleneck?

tjic 10y ago
Great question; I'd love to read an answer.
imglorp 10y ago
I'm curious if that analysis could feed a parallelization step. Then you could offload work chunks to a compute farm.
- ricksplat 10y ago
  I don't think you even need to go that far. Can you optimise your implementation so that parallelisable chunks could run across all your cores? This sounds like the kind of think Excel may even do "automatically" but like many such things you usually need to structure your implementation in a suitable fashion.
  - imglorp 10y ago
    It would be kind sad if MS didn't already do that.
    - ricksplat 10y ago
      It's a NP complete problem. What I mean is, there is no generalisable solution to this. Optimising algorithms employed by sophisticated technical users (think `-O`optimisation; Java hotspot) typically employ sets of heuristics that run the code in more optimal ways based on certain patterns. To trigger these you usually have to be aware of them and write your code in a certain way. Given that Excel isn't really targeted at these kinds of user (heavy lifting typically offloaded to external libraries) it wouldn't be "Sad" if they didn't already do it, and I would in fact be pleasantly surprised. Which is why I'm asking the question. EDIT: Turns out you're right! https://msdn.microsoft.com/en-us/library/office/bb687899.aspx https://msdn.microsoft.com/en-us/library/office/bb687899.asp...
      - dastbe 10y ago
        To trigger these you usually have to be aware of them and write your code in a certain way. Compiler writers also look for common patterns in people's code and figure out how to optimize them. when you have both years (decades) of legacy code and developers who don't even know what patterns are optimized, you as a compiler writer need to optimize the code that is being written.
        ricksplat 10y ago
        Yes that's what I meant. Common patterns = Heuristics as a developer you need to be aware of the common patterns the compiler is looking for.
    - ddeck 10y ago
      Excel uses multiple cores for calculations. You can configure how many to prevent it sucking up the entire CPU for long calculations. It's a pretty easily parallelizable problem since the sheet is just a dependency tree with the cells as nodes. The build-in functions (e.g. probability distributions etc.) could also be multi-threaded, although I'm not sure if they are. Our external API called from cells was written in C++ and already multi-threaded. More info here: https://msdn.microsoft.com/en-us/library/office/bb687899.aspx https://msdn.microsoft.com/en-us/library/office/bb687899.asp...
- doppenhe 10y ago
  ex Excel PM. There is a project that was between the Excel team and the high performance computing team at Microsoft for exactly this purpose. Not surprisingly mostly used by investment banks and insurance companies. (https://msdn.microsoft.com/en-us/library/ff877825(v=ws.10).aspx https://msdn.microsoft.com/en-us/library/ff877825(v=ws.10).a...)
ricksplat 10y ago
I googled it: "Excel Profiler" turns up a number of 3p products such as this http://www.decisionmodels.com/FastExcelV3Profiler.htm http://www.decisionmodels.com/FastExcelV3Profiler.htm
- cm2187 10y ago
  On decisionmodels, the website looks like it is coming straight from the 90s (but after all so does excel) but it is a gold mine of stuff to know before one can claims to really understand excel. I highly recommend the reading to anyone aspiring to be a "poweruser".
  - osullivj 10y ago
    Seconded. decisionmodels.com has excellent stuff on the difference between F9, sh-F9 and ctrl-sh-F9, and explains when the formula graph gets rebuilt. When you're building calc heavy XLL addins it's important to have a clear understanding of the behaviour of the calc engine code invoking your addin.

tjic 10y ago

Great question; I'd love to read an answer.

imglorp 10y ago

I'm curious if that analysis could feed a parallelization step. Then you could offload work chunks to a compute farm.

ricksplat 10y ago
I don't think you even need to go that far. Can you optimise your implementation so that parallelisable chunks could run across all your cores? This sounds like the kind of think Excel may even do "automatically" but like many such things you usually need to structure your implementation in a suitable fashion.
- imglorp 10y ago
  It would be kind sad if MS didn't already do that.
  - ricksplat 10y ago
    It's a NP complete problem. What I mean is, there is no generalisable solution to this. Optimising algorithms employed by sophisticated technical users (think `-O`optimisation; Java hotspot) typically employ sets of heuristics that run the code in more optimal ways based on certain patterns. To trigger these you usually have to be aware of them and write your code in a certain way. Given that Excel isn't really targeted at these kinds of user (heavy lifting typically offloaded to external libraries) it wouldn't be "Sad" if they didn't already do it, and I would in fact be pleasantly surprised. Which is why I'm asking the question. EDIT: Turns out you're right! https://msdn.microsoft.com/en-us/library/office/bb687899.aspx https://msdn.microsoft.com/en-us/library/office/bb687899.asp...
    - dastbe 10y ago
      To trigger these you usually have to be aware of them and write your code in a certain way. Compiler writers also look for common patterns in people's code and figure out how to optimize them. when you have both years (decades) of legacy code and developers who don't even know what patterns are optimized, you as a compiler writer need to optimize the code that is being written.
      - ricksplat 10y ago
        Yes that's what I meant. Common patterns = Heuristics as a developer you need to be aware of the common patterns the compiler is looking for.
  - ddeck 10y ago
    Excel uses multiple cores for calculations. You can configure how many to prevent it sucking up the entire CPU for long calculations. It's a pretty easily parallelizable problem since the sheet is just a dependency tree with the cells as nodes. The build-in functions (e.g. probability distributions etc.) could also be multi-threaded, although I'm not sure if they are. Our external API called from cells was written in C++ and already multi-threaded. More info here: https://msdn.microsoft.com/en-us/library/office/bb687899.aspx https://msdn.microsoft.com/en-us/library/office/bb687899.asp...
doppenhe 10y ago
ex Excel PM. There is a project that was between the Excel team and the high performance computing team at Microsoft for exactly this purpose. Not surprisingly mostly used by investment banks and insurance companies. (https://msdn.microsoft.com/en-us/library/ff877825(v=ws.10).aspx https://msdn.microsoft.com/en-us/library/ff877825(v=ws.10).a...)

ricksplat 10y ago

I googled it: "Excel Profiler" turns up a number of 3p products such as this http://www.decisionmodels.com/FastExcelV3Profiler.htm http://www.decisionmodels.com/FastExcelV3Profiler.htm

cm2187 10y ago
On decisionmodels, the website looks like it is coming straight from the 90s (but after all so does excel) but it is a gold mine of stuff to know before one can claims to really understand excel. I highly recommend the reading to anyone aspiring to be a "poweruser".
- osullivj 10y ago
  Seconded. decisionmodels.com has excellent stuff on the difference between F9, sh-F9 and ctrl-sh-F9, and explains when the formula graph gets rebuilt. When you're building calc heavy XLL addins it's important to have a clear understanding of the behaviour of the calc engine code invoking your addin.