10

I have an application which deals with excel. Recently I encountered a problem with very slow creation of Excel object.

I've recreated the issue with this simple code:

Microsoft.Office.Interop.Excel.Application xlApp;
xlApp = new Microsoft.Office.Interop.Excel.Application();

The second line causes the delay.

In order to measure the time needed for new object allocation, above code has been extended with time tracking solution and the results are conclusive. In NORMAL situation, above code executes in 0.5s while in case of FAULTY-BEHAVIOR it can take up to 5 minutes.

There are no memory leaks and excel objects are being properly freed. My solution has been running 24/7 whole year without any issues. I'm not sure if it's important but the application is running on 20 separate user's sessions (server machine). So there are 20 copies of this application running at the same time and it may result in 20 copies of Excel running at the same time.

First time the issue has been noticed 2 months ago and has been solved by upgrade of Office (2010 -> 2013). This time I have more time to investigate and sadly results aren't promising.

Facts:

  • only one machine is currently affected by this issue (24 cpu cores, 24GB of Ram)
  • CPU isn't stressed at all when the "delay" happens
  • I've tried using "process monitor" application to verify what happens when we "new Excel.Application()" constructor (to see if there is any excessive disk/memory/cpu usage) - no signs of resources limitations. No sign of log files related to COM objects, etc.
  • The only issue here is this few minutes of delay. All other Excel Interop commands work as usual.

Main Question:

  • Is there a way to debug this Microsoft.Office.Interop.Excel.Application() constructor to see which part is an issue here?

External content

EDIT - additional test

PowerPoint constructor is not affected by the delay

ppApp = new Microsoft.Office.Interop.PowerPoint.Application();
adrian.krzysztofek
  • 889
  • 6
  • 21
  • 2
    As far as I know this constructor launches Excel app. Can you launch it manually on this machine and see how long does it take to load? Maybe it's installing plugins? – ttaaoossuuuu Jan 23 '14 at 14:10
  • I think Interop disables plugins by default when Excel launches. Is the app running under the logged in users profile or under a service account? – Allan Elder Jan 23 '14 at 14:18
  • @Taosique - Excel can be launched manually with no delay. – adrian.krzysztofek Jan 23 '14 at 14:22
  • @Allan Elder - logged in users only (as access to clipboard is required later use). There are no extra plugins installed - it's plain Office installation. – adrian.krzysztofek Jan 23 '14 at 14:23
  • If you do not explicitly Need Office for interop, but for file creation, you could use openXML-SDK directly or the wrappers which map interop to openXML. – TGlatzer Jan 23 '14 at 14:27
  • @Grumbler85 - one of requirements is to simulate a user who copies given range from Excel to clipboard and then pastes it into PowerPoint. To make it more difficult, you have to wait for macros to finish loading data from Analysis Services Cubes. Sadly, I cannot shift to openXML unless I can achieve all above requirements. – adrian.krzysztofek Jan 23 '14 at 14:34
  • 1
    Well - then forget my comment, just wanted to Point out, that at least creation can be done way more efficiently than using interop. – TGlatzer Jan 23 '14 at 14:41
  • Can you confirm that you are cleaning the objects properly, if you run the application several times it dos not let process hanging? with this best of a machine it takes a very long time to this become a problem. – Pedro.The.Kid Jan 27 '14 at 15:42
  • @Pedro.The.Kid No worries. There are no extra Excel processes running. Also issue doesn't go away after server restart/reboot - so it's not a problem with low resources. – adrian.krzysztofek Jan 27 '14 at 15:50
  • Addition validations: See if the office DLL are the latest ones as when you start Excel it runs the latest and the new Application() runs the compiled version. Additionally see if you can relate this to a KB update. This smells allot like a AV or Excel Issue and not code related. – Pedro.The.Kid Jan 27 '14 at 16:08
  • I use Interop.Excel on a daily basis. try the following XLS = new Application { Visible = false }; I can open 25 copies of excel using my worker threaded process in a fraction of a second, Not on SSD drive. you also should look at my answer here on how to clear excel properly. [link](http://stackoverflow.com/questions/17367411/cannot-close-excel-exe-after-interop-process/21024691#21024691) Also, make sure anytime, anytime you reference a worksheet, anything with excel no double dot. and clear it before assigning to another object. – Lawrence Thurman Jan 28 '14 at 01:45
  • @Lawrence Thurman - I also use it on daily basis. It's only one of many machines which is affected by the issue. Also, this delay triggers way before I use any worksheet. (see my description above) – adrian.krzysztofek Jan 28 '14 at 08:21
  • @Pedro.The.Kid IMO it's 100% not code related. That's why I wanted to debug the constructor to see what checks and resources are being used in the background. There were no windows updates since middle of December and the issue reappeared on 15th of January. Sadly, turning off AV doesn't help at all. Excel version didn't change either. – adrian.krzysztofek Jan 28 '14 at 10:19

2 Answers2

9

I've found solution on my own. I'll post it as someone else may encounter similar problem and it can save him hours/days of investigation.

What i did to find solution?

I've analyzed test application (basically only one line where new excel application is being created) with Process Monitor and it didn't show anything important. Then I repeated analysis with newly started Excel process. It highlighted numerous reads of windows registry

HKEY_USERS\S-1-5-21-2929665075-1795331740-364918325-1024\Software\Microsoft\Office\15.0\Excel\Resiliency\DocumentRecovery

Under above location I've discovered tens of thousands of keys. They all were created by Excel's "auto-recovery" functionality. Because of the numbers, loading them when starting new Excel object was taking about 40 seconds. This number was additionally being multiplied by another 10-20 simultaneously loaded sessions (did I mention my application is running on 20 user sessions?).

Solution: Removal of "Resilency" registry tree does the trick.

Why all these "auto-recovery" entries were there in a first place? I guess I don't handle closing of Excel very well and it "thinks" I'm having regular crashes and "tries" to help.

Now what's left is preventing it from happening all over again. I'll have a closer look at my ExcelClose() function.

Thanks for your attention - Adrian

adrian.krzysztofek
  • 889
  • 6
  • 21
2

I don't think the problem is with this constructor. Try to create the object dynamically: 

var obj = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));

Then cast it to Microsoft.Office.Interop.Excel.Application:

var xlApp = (Microsoft.Office.Interop.Excel.Application)obj;
MessageBox.Show(xlApp.Name);

I'd expect the slow-down to move to the Activator.CreateInstance call.

Anyway, you can try to work it around by placing the following into you app.config file (more details):

<runtime>
    <generatePublisherEvidence enabled="false"/>
</runtime>

I'd also suggest to make sure you're running the latest VSTO Runtime and the latest Office PIAs.

noseratio
  • 59,932
  • 34
  • 208
  • 486