VB.NET / COM服务器代码方式比Excel VBA代码慢 [英] VB.NET/COM Server code way slower than Excel VBA code

问题描述

背景

我有一个客户需要Excel VBA代码才能将公式值移动到VB.NET。他从事提供财务分析的业务，在这种情况下作为Excel加载项提供。我已经将VBA翻译成在单独的DLL中运行的VB.NET代码。 DLL被编译为COM服务器，因为，Excel，可调用的.NET UDF必须是。到目前为止，这样好：Excel单元格有= foo（Range1，Range2，...），调用VB.NET Com Server的UDF，并且单元格获得一个与VBA代码值匹配的值。

问题

VB.NET代码的运行速度较慢。我可以拉伸一系列基于VBA的公式，并得到即时计算。我可以拉伸相当范围的基于VB.NET的公式，计算需要5-10秒。

我有以下几种可能性：

1. VBA的原生编译因为没有开关而更快


2. 每个UDF调用可以加载和卸载DLL



3. 从DLL中调用Excel WorksheetFunction方法很昂贵
$ b $ b

我不认为（2）是真的，因为我把调用附加到共享新，公共新和最终化函数中的文件，我得到的是：

 共享子新
公共子新
 Finalize 
  
 
 
 
当我打开电子表格，反复展开公式范围，并关闭电子表格。
 
 
 
我不认为（3）是真的，因为文件写作表明Application对象只创建一次。
 
 
 
 问题 
 
 
 
如何知道需要多少时间？如何在这个环境中配置文件？有没有明显的增强？
 
 
 
在最后一个类别中，我试图减少一个Application对象的创建数量（用于WorkSheetFunction调用） / p> 
 
 
 < Guid（1ECB17BB-444F-4a26-BC3B-B1D6F07D670E）& _ 
< ClassInterface（ClassInterfaceType.AutoDual）> _ 
< ComVisible（True）> _ 
< ProgId（Library.Class）> _ 
公共类MyClass 
私有共享应用程序作为应用程序'非常烦人
  
 strong>采取的方法 
 
 
 
我试图通过重写我自己来减少对Excel数学函数的依赖。我替换了Min，Max，Average，Stdev，Small，Percentile，Skew，Kurtosis等等。我的UDF代码调用到Excel少得多。不可避免的调用似乎将一个Range作为参数，并将其转换为.NET内部使用的数组。
解决方案
 
 
 
 DLL被编译为COM服务器
，因为，Excel可调用的.NET 
 UDF必须是
 
 
一个show-stopper的位，如果是真的，我同意。但是，当然，这是不是真的，为什么我会开始这样的方式... 
 
 
 
您可以在C ++中针对Excel SDK编写UDF，交付他们作为XLL，一件事。这是银行定量分析师的常见做法; 
 
 
 
另一个不太痛苦的选择，我最近才碰到过，是< a href =http://groups.google.com/group/exceldna/web/introducing-exceldna---dotnet-for-applications =nofollow noreferrer> ExcelDNA ，AFAICT提供令人讨厌的SDK / XLL位与一个方法来挂接你的.NET DLL。它足够酷，它甚至允许你加载源代码，而不是建立一个单独的DLL，这是伟大的原型（它利用的事实，CLR实际上包含编译器）。我不知道性能：我没有尝试基准，但它似乎解决了COM互操作问题，这是众所周知的ghastly。
 
 
 
除此之外，我只能批准其他建议：尽可能少地引用您的工作簿，其内容和Excel应用程序。每次通话费用。
 
Background

I have a client who needs Excel VBA code that produces formula values moved to VB.NET. He is in the business of providing financial analytics, in this case delivered as an Excel add-in. I have translated the VBA into VB.NET code that runs in a separate DLL. The DLL is compiled as a COM Server because, well, Excel-callable .NET UDFs have to be. So far, so good: Excel cells have "=foo(Range1, Range2, ...)", the VB.NET Com Server's UDF is called, and the cell obtains a value that matches the VBA code's value.

The problem

The VB.NET code is way slower. I can stretch a range of VBA-based formulas and get instantaneous calculation. I can stretch a comparable range of VB.NET-based formulas and the calculation takes 5-10 seconds. It is visibly slower and unacceptable to the client.

There are a few possibilities that occur to me:

native compilation of VBA is faster because of the absence of a switch
the DLL may be loaded and unloaded for each UDF call
the DLL calls Excel WorksheetFunction methods and requires an Application object, and creating the Application object is expensive
calling an Excel WorksheetFunction method from the DLL is expensive
I don't think that (2) is true because I put calls to append to a file in the Shared New, the Public New, and Finalize functions, and all I get are:
Shared Sub New
Public Sub New
Finalize
when I open the spreadsheet, repeatedly stretch a formula range, and close the spreadsheet.

I don't think (3) is true because the file writing shows that the Application object is created only once.

The question

How do I figure out what is taking the time? How to profile in this environment? Are there obvious enhancements?

In the last category, I have tried to reduce the number of creations of an Application object (used for WorkSheetFunction calls) by making it Shared:
<Guid("1ECB17BB-444F-4a26-BC3B-B1D6F07D670E")> _
<ClassInterface(ClassInterfaceType.AutoDual)> _
<ComVisible(True)> _
<ProgId("Library.Class")> _
Public Class MyClass
    Private Shared Appp As Application ' Very annoying
Approaches taken

I've tried to reduce the dependence on Excel mathematical functions by rewriting my own. I've replaced Min, Max, Average, Stdev, Small, Percentile, Skew, Kurtosis, and a few more. My UDF code calls out to Excel much less. The unavoidable call seems to be taking a Range as an argument and converting that to a .NET Array for internal use.
解决方案

  
The DLL is compiled as a COM Server
  because, well, Excel-callable .NET
  UDFs have to be
A bit of a show-stopper if true, I agree. But of course, it isn't true at all, why else would I have started that way...

You can write your UDFs in C++ against the Excel SDK and deliver them as an XLL, for one thing. It's a common practice among quantitative analysts in banks; in fact they seem to enjoy it, which says a lot about them as a group.

Another, less painful option, that I've only recently come across, is ExcelDNA, which, AFAICT, provides the nasty SDK/XLL bit with a way to hook up your .NET DLLs. It's sufficiently cool that it even lets you load source code, rather than building a separate DLL, which is great for prototyping (it makes use of the fact that the CLR actually contains the compiler). I don't know about performance: I haven't attempted to benchmark it, but it does seem to get around the COM Interop issue, which is well-known to be ghastly.

Beyond that, I can only endorse other recommendations: reference your workbook, its content and the Excel application as little as possible. Every call costs.

                        
这篇关于VB.NET / COM服务器代码方式比Excel VBA代码慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！