COM components, VBA and performance

The last post looked at some of the other issues, this one is looking at performance.
Specifically performance of the interface and trying to ignore execution speed difference effects. Execution speed is definitely important, but that is for another test.
I used a simple routine that passes 2 longs, 1 by val and 1 by ref, it then sets the value of second to the first.
The reason I did this was to preserve the function return value for success/failure as per COM standards.

I used 5 similar functions so there would be a worthwhile function table, and called each one in turn. Increasing to 10 functions (but keeping 5 calls) added about 10% to the time of the late bound VB version. Which function was called has an impact, presumably depending on its position in the COM function table.

I did it on my rickety old laptop running Visual Studio 6.0 and Excel 2003.
I did a few different test projects, heres the results:
I had to put them on the codematic site to get a decent layout.

The C++ projects were done with ATL, MFC only really does late binding.
What is interesting is that late binding to an ATL component is still much faster than early binding to a VB dll.
So the next time someone tells you early binding is faster than late binding, be sure to tell them language has a bigger impact.
No real surprise that the native win32 version was 10 times faster than COM. Thats the price you pay for simplicity and robustness.
Its worth noting the VBA performance – its 20x faster than an early bound VB6 dll. The actual functions are going to have to be doing some significant work to overcome that calling overhead.

[Edit – Note this was a test to create an object, call some (5) methods on it and destroy it (well leave VBA to, a bit sloppy I know), a more useful test would be to break out the 2 components of time – 1. create/destroy the objects, 2. call their methods. Expect a post covering that tomorrow, and thanks for pointing it out Rob. The mix of creating/destroying objects versus calling methods on that object will have a significant effect on performance.]

I didn’t test exe servers as this was purely a performance test. They might catch up in some SETI like massively parallel grid computation, but for Excel devs, fairly safe to assume their performance would not be near the top.

So if performance is important the choices from best to worst (excluding keeping things in VBA):
win 32 is the fastest
setting a reference to an ATL COM object is next
late binding to an ATL is much slower
setting a reference to a VB activex dll is worse again
late binding to a VB component is over 100 times slower than win 32

Of course to be sure the results are valid for whatever you may be doing, you would need to test the specifics. (ie don’t sue me if you spend 6 months rewriting you VB dll as a native win 32 DLL only to find its 10x slower. Do tell us though – I’ll send you a lollipop for your trouble.
The 2 COM dlls, the win 32 one and the 5 workbooks are linked to from that page above, let me know how you get on. (The COM ones need regsvr32-ing)
If anyone fancies doing the .net equivalent let me know. I am assuming .net would be worse again as there are more interfaces to pass through, anyone want to prove me wrong?

If anyone can see a fault with my reasoning, or gets different results let me know that too. I am a little surprised at the results so suggestions as to why they might be right/wrong are very welcome.
Cheers

Simon

Advertisements

5 Responses to “COM components, VBA and performance”

  1. Rob Bruce Says:

    Simon,

    I’ve only taken a brief look at your examples, but it seems that you have not included a ‘cached’ COM example – the VB example that I’ve just looked at creates and destroys half a million instances of the COM server. That’s [i]bound[/i] (if you’ll excuse the pun) to be expensive. In the real world, if you expected to use a COM object so extensively, you would create it once and call its method(s)/property(s) 500,000 times, wouldn’t you? I’m willing to bet that the performance difference between VBA and VB/COM (early bound) is entirely wiped out if you do this.

    Rob

  2. Ross Says:

    Fascinating,
    Interesting that the C++ LB is faster than a VB early.
    Can I infer then that it takes about 11 seconds to late bind a VB com component (21-10) and about 5 if it’s written in C (7-2)? If this is true (for your system!) then the cal time of these 2.5m function calls is 2 seconds in C and 10 in VB?

    I guess one question I have is that is the VBA/VB result “fair”? By which I mean if the function was more complex would the fully complied nature of the VB offer improvements – If the above statement are valid it might be suggested that we would have to have a longer run time than 10 seconds to see anyway.

    As for .Net, I have built some com interop classes, and called them with early binding – but they are not functions. I did notice that the performance was miles better (faster) than when a managed com addin was called, which is so slow it worries me!

    Good Stuff Simon.

  3. Simon Says:

    Rob
    Good spot!
    I think I have caught myself out by being slack and not using a proper set of preformance routines.
    What I have measured is the creation of an object, and then calling 5 methods on it, all 500k times.
    What I should have done was measure the cost to create, and then as you say, use a cached object to measure the cost to call.
    Let me run some more tests and publish. Initial results suggest you are right on the VB/VBA effect.
    Doh!
    Ross, let me do some more testing and do an update, the figures are ok, but its heavily dependent on your create/use/destroy mix. I need to do some more work in that area to break things out better.
    cheers
    Simon

  4. Simon Says:

    Ross
    Re fairness, I’m just trying to look at the cost to call each type. so no its not the whole picture. But it highlights the issue that you better hope your external component is quicker than VBA to recover the cost to create and call out to it.
    AS you say the more complex your function, the more likely it is to be worthwhile to move to something else. Eventually even .net becomes feasible – eventually! (probably!)
    cheers
    Simon

  5. Marcus Says:

    Excellent article Simon. The other point to highlight is ‘fit for purpose’. As a general runabout there’s nothing wrong with the family sedan. But if you have a need for speed then you’ll need a Porsche.

    If the solution displays a few screens and retrieves or submits some data then a VB COM solution is more than adequate keeping in mind the ROI for development time. However, as your tests highlight, it’s inadequate for building a function library for a trading system.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: