VSTO excel object performance issue

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

P开发者_JAVA百科rivate 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'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.