Speed comparison between VB and OOo Basic - Some results

nom

I didn't do this for the sake of comparison, but rather was looking for the fastest way to accomplish a task. Having done this I now know which one is fastest, and to my suprise it is OOo Basic !

I first started off with OOo Basic. The task is to look at the content of a cell in a Calc sheet and find out if it contains characters that have ASCII numbers > 127.

I need to process more than 28,000 cells with each cell containing strings on average 6 characters long.

This takes about 40 seconds in OOBasic.

Because I thought this was too slow I wanted to do the same thing in VB since I assumed it would be faster. To my suprise it took 70 seconds to complete!

****
Update: After I finished writing the subs I found a better way of performing the search (after reading a tutorial by Bernard Marcelly). I will try this when I get the time and post back with the results. The idea being that instead of checking each cell at a time (This is actually where I think VB looses time), load all the data into memory as an array (right at the beggining) and loop through the array. So perhas VB will be faster in this case.... will see.

nom

DannyB · Posted: Tue Apr 20, 2004 9:26 am Post subject:

I suspect that each UNO operation has a fixed constant overhead. And I think it may be in the low milliseconds. Ouch! Oowee! (I think I read this fact somewhere on the www.openoffice.org website in a uno developer area. Also, naturally a concept like "milliseconds" of overhead is purely dependant on how fast your computer runs! But I'm just repeating what I read.)

What this means is that the performance is going to be directly related to the number of UNO calls you make.

Some uno bridges may have a higher per-function-call overhead than others -- especially if they communicate over TCP/IP.

If you don't understand the API, you may be using "psuedo properties" (for lack of an official term) thinking there is no overhead, because you cannot see the function calls.

Imagine a loop like....

dfrench · Posted: Tue Apr 20, 2004 12:12 pm Post subject:

nom · Posted: Fri May 14, 2004 6:38 am Post subject:

I made some time and did a speed comparison between VB and StarBasic (SB), but this time loaded the data into memory.

For some insight this is what I use to do (OK I know it is poor Embarassed

):

nom · Posted: Fri May 14, 2004 7:04 am Post subject:

While I was studying Bernard's code I thouhgt of another way of doing the search.

As a reminder this is how he does it:

DannyB · Posted: Fri May 14, 2004 7:28 am Post subject:

Your new results are initially surprising, but not after you analyze where the time is being spent.

Here is an outline of how I think the time was spent in old vs. new....

Old way (roughly)....
1. For loop
1.1. Get/manipulate cell values (uno calls -- inside loop)

New way (roughly)....
1. Get array of values (uno call -- outside loop)
2. For loop
3. Put array of values (uno call -- outside loop)

Now instead of looking at old vs. new, let's look at OOoBasic vs. VB.

In OOoBasic, you have in the old way a loop with a uno call inside it. In the new way you have moved the uno calls outside the loop. Shaved off a significant fraction of the runtime. (31 sec vs. 19 sec.)

So in VB, you do mostly the same. Move the inside the loop uno calls outside the loop. But you get a speed difference of 73 sec. vs. 1 sec. Why? Because VB compiles to native machine code! OOo Basic must still be interpreted at some level. (This makes sense. I can't imagine the OOo developers having to implement a native compiler for every platform OOo runs on.) In VB, most of that 73 second runtime is spent in UNO calls, plus the overhead of the COM to UNO bridge from Windows Automation over to UNO method calls. By having an uno call before and after the loop, you only have two expensive COM-UNO calls instead of a bunch of them inside the loop. Then the native code is just manipulating local array data. At lightning speed. In fact, it probably takes less than 1 second. And the runtime may actually be dominated by the uno call before the loop!

DannyB · Posted: Fri May 14, 2004 7:34 am Post subject:

nom · Posted: Fri May 14, 2004 8:35 am Post subject:

DannyB · Posted: Fri May 14, 2004 8:42 am Post subject:

There have been various VB examples of passing arrays of com.sun.star.beans.PropertyValue to both loadComponentFromURL and storeToURL.

In those cases, an array is passed, with the extra parens. Also of note is that the arrays are always expressly dimensioned.

So what if you tried this....

Dimension a new array, whose size is exactly the size of the dynamic array which was returned from the spreadsheet. In a nested For loop, copy, element by element, the values from the dynamic array to the dimensioned array.

Then pass the dimensioned array name, using parens as the argument.

Any luck with that?
_________________
Want to make OOo Drawings like the colored flower design to the left?

nom · Posted: Fri May 14, 2004 9:55 pm Post subject:

Got it working! Thanks for the hint Danny.
Actually I don't need a temporary array, I just had to define the dimension of the array to start of with. The trick in VB is to use the ReDim definition and extract the number of rows and column from the selected range. After that everything works nicely!

The working code is here: