Time taken to register xll

Feb 10, 2014 at 6:41 PM
Hi,

I am seeing that the time taken by Excel-DNA to register my xll is of the magnitude of 7 minutes, i.e. "Registering library..." stays in the status bar for about 7-odd minutes.

I calculated time taken by my AutoOpen and it is around 700 - 800 milliseconds.

I know Excel-DNA has to load the CLR etc., but is 7 minutes expected?

If yes, could you please explain where this time is spent?
If no, how can I improve it?

Thanks
Coordinator
Feb 10, 2014 at 7:20 PM
Edited Feb 10, 2014 at 7:21 PM
That doesn't sound right. My test add-ins are admittedly small, but load in fractions of a second (after the CLR is loaded into the process).

What does your .dna file look like?
What takes so long in your AutoOpen?

Excel-DNA goes through the ExternalLibrary assemblies and looks for methods to register. If you have many libraries, or large ones with make public static methods, it can take a while. You can speed up by understanding and restricting what gets registered. One easy mistake is to add extra references that you need, but which doesn't contain stuff you want to register, with <ExternalLibrary ...> tags in the .dna file instead of <Reference ...> tags.

Another optimization you can add is to configure your .dna file with: <ExternalLibrary ... ExplicitExports="true" />. Then only methods with [ExcelFunction] or [ExcelCommand] attributes will be registered.

If it still takes more than a minute after looking at these options, you can contact me privately and I'd be happy to look at your project.

-Govert
Feb 10, 2014 at 7:56 PM
Hi,

I am extremely sorry, I meant 7 seconds, not minutes. Is 7 seconds also too much?

My AutoOpen connects to a JVM and gets function registration information via reflection on the JVM side, so I am not too worried about 700-800 milliseconds taken by my AutoOpen.

My dna file looks like below. It has only one external library.

<DnaLibrary Name="OptimusXL" RuntimeVersion="v4.0">
<ExternalLibrary Path="OptimusXL.dll" ComServer="true"/>
</DnaLibrary>

I don't have any [ExcelFunction] or [ExcelCommand] attributed methods. Since registration information is coming at runtime, I am using RegisterMethods(List<MethodInfo> methods, List<object> methodAttributes, List<List<object>> argumentAttributes). But since this is all happening inside my AutoOpen which takes 700-800 milliseconds, I am only concerned about what excel-dna is doing in other 6 seconds.

Thanks.
Coordinator
Feb 10, 2014 at 8:07 PM
If you don't have any methods that you want to have automatically registered, apart from the ones in your RegisterMethods call, I think setting ExplictExports="true" should help.

If the issue is that the reflection pass in Excel-DNA takes too long because your assembly is huge, you might refactor it into a part that is exposed to Excel-DNA (with the AutoOpen and the COM server), and another assembly that you reference from there, but not add as an <ExternalLibrary>, with the bulk of the implementation in.

You should also compare the first load, vs. reloading (just say File->Open and pick your .xll again), to see how much the CLR loading takes - but I doubt loading the CLR take seconds.

I can't really tell if 7 seconds is too much. But you could also start with a new, minimal add-in, and check the timings as you add stuff. Of you can profile that actual Excel-DNA code, but that would be a bit more work. Certainly I've not tried to optimize the registration performance much, so there might be some low hanging fruit.

-Govert
Feb 10, 2014 at 8:33 PM
I tried:
<DnaLibrary Name="OptimusXL" RuntimeVersion="v4.0">
<ExternalLibrary Path="OptimusXL.dll" ComServer="true" ExplictExports="true"/>
</DnaLibrary>
But it doesn't seem to have changed anything.

My assembly is 560KB, don't think that's huge.

I did the xll reloading as you suggested, it still displayed "Registering library ..." for around 7 seconds.

My dll has many assemblies referenced. When do all the referenced assemblies are loaded? Before or after putting "Registering library ..." on the status bar?

I am going to load a minimal add-in and give it a try.
Feb 10, 2014 at 8:43 PM
I loaded a minimal addin with just one [ExcelFunction] and explicit exports off. Nothing changed.
Coordinator
Feb 10, 2014 at 8:49 PM
Try the ExcelDna.xll in the Distribution directory of the Excel-DNA download.

First open Excel.
Then File->Open the ExcelDna.xll.

On my machine the Registering... flashes for maybe 1/3 of a second, and that includes loading the CLR.

I suspect something else is wrong.
  • What Windows / Excel version?
  • What processor?
  • Anti-Virus?
-Govert
Coordinator
Feb 10, 2014 at 9:10 PM
Are you perhaps checking this with a debugger attached?
That will make the loading take much longer than usual.

-Govert
Feb 11, 2014 at 2:06 PM
ExcelDna.xll from the distribution directory indeed flashes "Registering..." for less than a second on my machine.

I am on windows 7 64 bit, 32 bit excel 2010.
Processor is Intel Xeon 3.3 GHz, 6 cores. So this isn't a problem.

Anti-virus could be a reason, but ExcelDna.xll loaded in less than a second, so I doubt it is.

I loaded release version of my xll without VS attached, it's still flashing "Registering..." for about 7 seconds.

I will look around and post the reason here once I find it.
Coordinator
Feb 11, 2014 at 2:13 PM
So your minimal add-in also takes that long when loading without the debugger attached?

Maybe some kind of certificate thing, which goes out to the network?
Is anything signed - any funny security settings in Office?

-Govert
Feb 11, 2014 at 2:47 PM
I connected procmon to excel.exe.

It's doing "Process Profiling" for total of 6-7 seconds. And that's the missing piece. Although I don't know what does Process Profiling include. Any ideas?

27:33.6 EXCEL.EXE 9536 RegQueryValue HKCU\Software\Policies\Microsoft\office\14.0\common\shipasserts\DisableShipAsserts SUCCESS Type: REG_DWORD, Length: 4, Data: 1
27:34.0 EXCEL.EXE 9536 Process Profiling SUCCESS User Time: 1.3728088, Kernel Time: 2.2464144, Private Bytes: 62,836,736, Working Set: 76,275,712
27:35.0 EXCEL.EXE 9536 Process Profiling SUCCESS User Time: 2.3712152, Kernel Time: 2.2464144, Private Bytes: 65,740,800, Working Set: 79,183,872
27:35.0 EXCEL.EXE 9536 RegQueryValue HKCU\Software\Policies\Microsoft\office\14.0\common\shipasserts\DisableShipAsserts SUCCESS Type: REG_DWORD, Length: 4, Data: 1
27:35.0 EXCEL.EXE 9536 RegQueryValue HKCU\Software\Policies\Microsoft\office\14.0\common\shipasserts\DisableShipAsserts SUCCESS Type: REG_DWORD, Length: 4, Data: 1
27:36.0 EXCEL.EXE 9536 Process Profiling SUCCESS User Time: 3.3696216, Kernel Time: 2.2464144, Private Bytes: 65,855,488, Working Set: 78,946,304
27:37.0 EXCEL.EXE 9536 Process Profiling SUCCESS User Time: 4.3524279, Kernel Time: 2.2620145, Private Bytes: 73,768,960, Working Set: 86,863,872
27:38.0 EXCEL.EXE 9536 Process Profiling SUCCESS User Time: 5.3352342, Kernel Time: 2.2620145, Private Bytes: 74,219,520, Working Set: 87,400,448
27:39.0 EXCEL.EXE 9536 Process Profiling SUCCESS User Time: 6.3336406, Kernel Time: 2.2620145, Private Bytes: 75,714,560, Working Set: 89,133,056
27:40.0 EXCEL.EXE 9536 RegQueryKey HKLM\SOFTWARE\Microsoft\Fusion\PublisherPolicy\Default SUCCESS Query: HandleTags, HandleTags: 0x100

Does my xll (and dll) need to be signed?

The assemblies xll refers to and uses are indeed signed but it's embedded and doesn't go out to the network.

Office security, I am not sure about.
Coordinator
Feb 11, 2014 at 4:16 PM
I think the 'Process Profiling' is just something ProcMon does every second or so. Not related to your issue.

There's no point in signing your own .dlls which are included in the .xll.
Signing the .xll itself (after packing) with SignTool is optional. But you're better off signing nothing for getting to the bottom of this issue.

The ExcelDna.dna file in the Distribution will load into .NET runtime 2.0, while your stuff (including minimal test) is probably loading .NET 4.0. You might try to change the ExcelDna.dna file with RuntimeVersion="v4.0" to see if that makes a difference. You can also make a minimal compiled add-in targeting NET 2.0,

The ExcelDna.xll in the distribution should take longer to load than a minimal compiled example, since it invokes the VB compiler during startup to compile the code in the .dna file.

-Govert
Feb 11, 2014 at 4:34 PM
I think in my firm, some network access is made to load/run something else, also some services are run.
I think that should is it. Need to follow up with few people.

Just wanted to make sure this isn't an issue with excel-dna.
Thanks a lot.