0

I have a method CalculateLinearRegression() that calls the LinEst function of an Excel.Application.WorksheetFunction class. LinEst is called roughly 3,500 times each time CalculateLinearRegression() is called. I inject the WorksheetFunction class into my method so I know that only one instance of the class gets created.

Each call of CalculateLinearRegression() takes around 10 minutes and I am certain it is the LinEst function that is slowing everthing down (removing the call to LinEst and hard coding the results means the method to run in a couple of seconds).

I appreciate that LinEst is doing a fair bit of computation, but I doubt it's doing so much as to cause this delay, therefore I think it is probably the call to the Excel function that is adding on the lag. Does anyone know of either a way of speeding this up, or a C# direct replacement for the LinEst function?

Ben
  • 3,926
  • 12
  • 54
  • 87
  • Have you profiled it at all? It *may* be the interop getting in the way, but as the method is [fairly well documented](http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.worksheetfunction.linest.aspx) have you looked at reimplementing it yourself? – Rowland Shaw Oct 24 '12 at 09:35
  • 2
    You could call a macro/used defined fuction instead of multiple calls. – weismat Oct 24 '12 at 09:36
  • See also: http://stackoverflow.com/q/7792088/50447 – Rowland Shaw Oct 24 '12 at 09:38
  • 1
    Yep. If it's the interop transition you're making 3500 times then you'd be better to make that transition once if possible and call a macro as suggested by weismat. – cirrus Oct 24 '12 at 09:39

3 Answers3

1

There are some alternative C# implementations of LinEst, but I can't vouch for any directly. There's a discussion here for instance: What is the C# equivalent to LINEST from Excel?

I'd like to add some support to your long running function too, but there's not quite enough information in your question to comment. Are you able to post a sample of what it looks like? Is it just the Excel call that's slowing everything down?

Community
  • 1
  • 1
cirrus
  • 5,624
  • 8
  • 44
  • 62
1

Personally I would steer well clear of using Excel interop unless you absolutely need to (e.g. for saving a file to a binary MS excel format). Automating Excel involves some fairly horrific costs in terms of performance.

If you just need the functionality offered by LINEST consider using code from an article such as the following: http://www.codeproject.com/Articles/25335/An-Algorithm-for-Weighted-Linear-Regression Although I haven't used the code myself, the article seems to be highly regarded.

SpruceMoose
  • 9,737
  • 4
  • 39
  • 53
1

Each call to the Excel object model such as WorksheetFunction has to go through Interop-Com. Since the overhead of Interop is high multiple calls are slow.

One simple solution might be to switch to the XLL interface (which is many times faster) using Excel-DNA.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38