VSTO excel object performance issue

Question

Hi I wrote this little AddIn sample to show you a performance issue and how to avoid it

Can someone explain me why and how it works ?

It is just a parse of an excel workbook and runned in the main excel process (0) and a random thread created by the timer.

Thanks !

Public Class ThisAddIn

Dim a As System.Windows.Threading.Dispatcher = System.Windows.Threading.Dispatcher.CurrentDispatcher()
Dim t As New Threading.Thread(New Threading.ParameterizedThreadStart(AddressOf threadTest))
Dim tm As New System.Timers.Timer(20000)
Delegate Sub TestHandler()
Dim tt As TestHandler = AddressOf test

Private Sub ThisAddIn_Startup() Handles Me.Startup
    tm.AutoReset = True
    tm.Start()
    AddHandler tm.Elapsed, AddressOf threadTest
End Sub

Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

End Sub

Public Sub test()
    Dim appE As Excel.Application = Globals.ThisAddIn.Application
    Dim wb As Excel.Workbook = appE.ActiveWorkbook
    Dim ws As Excel.Worksheet = wb.ActiveSheet
    Dim rng As Excel.Range = ws.Cells(1, 1)

    Dim nbit As Integer = 10000
    For i = 1 To nbit
        rng.Value = i
    Next
End Sub

Private Sub threadTest()
    ' 800 ms
    Dim o() As Object
    a.Invoke(tt, o)

    '12 seconds !
    test()

End Sub
End Class

You are asking us to figure out if your add-in works and why? It seems like you would be the one most able to do that. — IAmTimCorey, May 09 '11 at 14:51
I don't see any parsing of the worksheet going on here. All I see is that you are trying to write a number from 1 to 10,000 to cell A1 in a for-loop within a threaded call. — code4life, May 09 '11 at 15:01
One more thing to realize is that each time you call rng.Value, this is actually a COM function call that you are making. In VSTO, the best way to reduce the performance overhead is to reduce the overall COM function calls as best as you can. — code4life, May 09 '11 at 15:03
my program do a parse, this is only a sample to show the problem with a simpler AddIn. I want to know why an invoke is nescessary to achieve good performances. — Avlin, May 09 '11 at 15:24

score 1 · Accepted Answer · edited May 23 '17 at 12:19

You're diving into the world of COM threading models. This is a good a start as any: http://msdn.microsoft.com/en-us/library/ms693344(VS.85).aspx.

If the code runs on the Excel main thread (which you achieve by setting up the Dispatcher), the COM calls are not marshaled across different threads. Since you have many COM calls (each .Value counts as one) the overhead adds up to the differences you see.

One of the reasons why the marshaling is expensive in this context, is that the Excel COM objects are running in a single-threaded apartment (STA), which means there is a message loop set up (actually a Windows message loop) in Excel to serialize the COM calls. Every cross-apartment call you make results in a message being posted to this message loop, which is the processed on the main Excel thread.

So the two cases differ in performance due to the COM cross-apartment marshaling. It's actually remarkably fast, given what is going on behind the scenes.

In both cases, making a single call to set a large range's .Value to an array of values will be much faster. And for the fastest (million cells a second) way to set data into your Excel sheet, see here: Fastest way to interface between live (unsaved) Excel data and C# objects.

VSTO excel object performance issue

1 Answers1