CODEWITHSUNDEEP
c++performancevba
Stepan1010
Stepan1010

Reputation: 3136

What are the major reasons for run time differences between simple arithmetic operations - VBA vs. C++?

This C++ program takes from 20 to 25 seconds to run on my relatively slow computer:

#include <iostream>

int main()
{
int i;
double test = 456;

for (i = 0; i < 900000000; i++) {
    test = (test / 0.99999999);
}
    std::cout << "The value of test is " << test <<".\n";
    return 0;
}

This excel vba macro takes from 37 to 40 seconds to run on my relatively slow computer:

Sub Macro1()
Dim i As Long
Dim test As Double
test = 456

For i = 0 To 900000000
    test = (test / 0.99999999)
Next i

Cells(1, 1).Value = test
End Sub

Is this difference typical(of C++ vs. non-compiled languages)? What are the major factors contributing to this difference in time? Is the fact that C++ is compiled the most important factor? Thanks.

Info:

For C++ I used Code::Blocks with GCC

For VBA I used Excel 2010.

For Code::Blocks it has a built in timer in it's console.

For excel I used a an iPhone stopwatch(didn't feeling like using CHighResTimer http://www.ozgrid.com/forum/showthread.php?t=56900)

Upvotes: 1

Views: 4955

Answers (4)

skyscraper
skyscraper

Reputation: 1

I guess to put it into layman's terms imagine how VB was built, underneath it it is tapping something previously developed in C++. sO, NO MATTER HOW YOU SLICE IT there is a hidden C++ "middle man" beneath everything VB does in most cases, so certain things are being done twice in essence, such as those built in error safeguards, interps, etc.

I also found difference with VBA as a standard project or the same functionality built into a in process DLL with AutoCAD VBA but did not time the performance difference but I could see the in-process was running faster, as in VBA will also run faster than connecting into AutoCAD from external VB. It seems the allocate the available memory for you in VBA and VB is using its own process, I don't know but it is a place to look into for speed improvement.

Upvotes: 0

evanmcdonnal
evanmcdonnal

Reputation: 48106

The difference is common and the biggest difference is that C++ is compiled. I'm surprised the difference isn't orders of magnitude larger. Most performance benchmarks show C/C++ to be 10+ times faster than scripting languages. How are you compiling the C++ code? I bet it runs in 5-10 seconds if compiled at the highest level of optimizations. If you're not already, you should compile with a -O3 flag and time it.

I don't know much about VBA but if you're compiling the C++ with full optimizations I think it's more likely VBA is running through a just in time compiler and then executing the same as VB.NET would on the CLR. The performance you show there is more like VC++ vs. VB.NET than VC++ vs. any scripting language.

Upvotes: 1

Konrad Rudolph
Konrad Rudolph

Reputation: 545766

Your benchmark is off, the margin should be way larger.

For instance, on my computer, the C++ code takes 7.46s (compiled without optimisations, timed with the Unix time tool); the VBA code by contrast took 46s. This is a much more realistic margin (but I wouldn’t have been surprised by an even larger one – conventional, non-optimising interpreters are usually at least a factor ten slower).

For one thing, VBA first has to read in the code and either interpret it line by line or transform it into an intermediate representation. Nevertheless, this intermediate code representation is still interpreted, not run directly by the CPU (or did you count the compilation time in C++?).1

In addition, VB(A) does a lot of redundant stuff in this code which is absent in C++: for instance, every single integer addition (which is implicit in your code in the incrementation of the For loop variable) is guarded against overflow. Essentially, this VB code

i = i + 1

is transformed to the following pseudo intermediate code:

if i + 1 overflows then
    raise an overflow error
end if
i = i + 1

… for every integer operation. That consumes quite a lot of runtime. The same goes for array (or here: cell) access operations.

1 I think VBA uses an intermediate representation called P-code rather than line by line code interpretation.

Upvotes: 4

escargot agile
escargot agile

Reputation: 22389

I don't believe the code lines in VBA are interpreted one-by-one over and over again, there must be some cache, otherwise the factor would be much larger than 1:2.

There can be many differences. Naturally the transition from a high-level language to CPU instuctions is different between different languages. C++ compilers are optimized for performance to meet a vast range of applications, which is probably not that important in Office products.

Upvotes: 1

Related Questions