COM components, VBA and performance – part 2

So part one was interesting, but actually not quite what I had set off to look at.

As Rob pointed out I was creating, using and destroying the objects each iteration. So that gives a reasonable view of the lifetime cost, assuming you create, call 5 functions and destroy. If you are doing anything different then that version of the test would not be overly helpful.

So I dropped everything and re-did the tests (hopefully properly – let me know!) I split out the cost to create and the cost to call into separate columns. I added a new section on the same page, with a link to a new set of test projects. I also included an xll version this time. Don’t bother using it from a worksheet cell as it doesn’t return a value so it errors or returns 0.

The new tests even things up quite a bit, C++ is still well faster, but overall early bound will probably be better than late bound, whatever the language. But note its still twice as fast to create a C++ object late bound as a VB one early. So with lots of creation and limited function calls, C++ late bound would be better than VB early bound.

VB6 did prove to be faster than VBA in the calling part, which I have never seen in use, maybe I don’t do complicated enough calcs. Mind you the VBA benefitted from being in a module rather than a class, stucturing it the same as the VB version adds 2 seconds of object creation/deletion overhead. I knew there was a reason I avoided excessive Object Orientation in VBA.

The thing I really wanted to test was the cost to call, early v late, and it seems late bound is about 20 times slower to call. So you pay twice for using late binding, once at object creation and then again at every function call.

Interestingly though I did some tests on the performance of individual functions (actually the same functionality) and found that SpeedTest1 was twice as fast as SpeedTest10, for late bound components, I didn’t see a difference with the early bound version. This would suggest if you are using late binding you should get your most used functions to the top of the function table (roughly write them first in the class in VB).

Have a play with the second set of test routines and let me know how you go. And of course if you can see any more weaknesses in my testing let me know. I find these results a bit more reasonable, but don’t that that put you off challenging my approach, I’d love to hear of a better approach to testing.

Also as a matter of interest is anyone getting client pressure/encouragement/interest to move away from VB6?

No one I speak to seems overly worried.



3 Responses to “COM components, VBA and performance – part 2”

  1. Marcus Says:

    — Object Orientated VBA —
    The class overhead in VBA came as a surprise. I keep nearly all my commonly used code in classes (e.g. common dialogs, dbs interactions). I also separate GUI and business logic in UserForms. The form only contains GUI code, the business logic is in a separate module which may or may not contain class calls.

    — Client pressure/encouragement/interest to move away from VB6? —
    Some have mentioned .Net, but that appears to be more dropping buzz words to the nerd rather than understanding the implications.
    At some companies, the IT dept have placed a ban on new VB6 development but have yet to provide a suitable alternative (.Net has yet to be rolled out).
    More important is that the solution works and that they have access to the source code (should I get hit by a bus) although not necessarily the IP.

  2. Simon Says:

    I feel a bit in limbo really, I’m not sure what realistic migration path there is at the moment. Maybe I should stop worrying and just carry on with VB6, no one else seems bothered.

  3. Dennis Wallentin Says:

    >>no one else seems bothered

    No ;)

    For me it’s a question of adding new tools to the toolbox and then it’s business as usual.

    Larger corporate dictate the premises and some of them have implemented .NET but still maintain classic VB etc solutions.

    Small corporates are not aware of VB et al. They just want to have workable solutions that runs.

    Lately I have come to the conclusion that the major benefit of .NET is that desktop solutions have become more interesting (again) for customers in general.

    Kind regards,

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: