RSS Feeds

VBA vs OOo Basic Performance II

This article follows on from VBA vs OOo Basic Performance where I discussed OOo Basic performance and coding efficiency, and one of the specific issues was “how long does it take to load a reasonably large Calc column into an array for processing in Basic.  So I set myself a little challenge: what is the fastest way to load the values from an worksheet range Sheet1.D1:D2000 (which happen to contain random numbers from 0 to 1,000,000,000 into a vector a(0 to 19999)?  Part of my own reasons is that I am characterising the OOo Basic (OOB) compiler and RTS, but I decided to post this because it might give those readers you are interested in speeding up slow running code.

I lay out seven different variants below and their timing on my Laptop.  The run-times for what might superficially seem to be stuff that the compiler would all compile down to the same code anyway are in fact spread by a factor of four.  But the bottom line is that there are some simple rules of thumb for getting your code to run fast:

Even though this last approach is by far the fastest, I don't really recommend it unless you are extremely confident in your programming skill, but what this does demonstrate is that Basic currently handles LoLs inefficiently which is a pity given that the are a core mechanism for UNO data bundling.  Ideally we should extend the OOB syntax in line with VBA and allow programmatic constructs such as a(i) (0) to manipulate LoLs.

Perhaps a simpler interim might be to add a couple of helper routines. For example if we had a ColumnSlice(<array> or <LoL>, <index,...) to return by value a single column from an Matrix or LoL; then the code for Testt 6 could be simplified to the following which would then be the fasted method whilst still retaining an acceptable level of simplicity:

y = ThisComponent.Sheets.Sheet1.getCellRangeByPosition(3,0,3,n).DataArray
z = ColumnSlice(y,0) 
For i = 0 To n : x(i) = z(i) : Next i 

(And yes, this is another one for an enhancement request)

The Test Routines

I have the full test harness if anyone wants it, but to shorten this post, I trimmed down to the key extract.  The timings were on my laptop averaged over ten iterations.

Test 1 — 10.68 Secs

Sub Test1(x, n) : Dim i  ' Full routine shown for the 
1st Test 
With ThisComponent.Sheets.getByName("Sheet1") 
For i = 0 To n : x(i) = .getCellByPosition(3,i).getString() : Next i 
End With 
End Sub

Test 2 — 10.81 Secs

For i = 0 To n : x(i) = ThisComponent.Sheets.getByName("Sheet1").getCellByPosition(3,i).getString() : Next i

Test 3 — 10.16 Secs

For i = 0 To n : x(i) = ThisComponent.Sheets.Sheet1.getCellByPosition(3,i).String : Next i

Test 4 — 8.09 Secs

s = ThisComponent.Sheets 
For i = 0 To n : x(i) = s.Sheet1.getCellByPosition(3,i).String : Next i

Test 5 — 5.17 Secs

s = ThisComponent.Sheets.Sheet1 
For i = 0 To n : x(i) = s.getCellByPosition(3,i).String : Next i

Test 6 — 3.52 Secs

y = ThisComponent.Sheets.Sheet1.getCellRangeByPosition(3,0,3,n).DataArray	
For i = 0 To n : r = y(i) : x(i) = r(0) : Next i

Test 7 — 2.79 Secs

c = ThisComponent.Sheets.Sheet1.getCellByPosition(3,n+2) 
zGbl = x 
c.Formula = "=TEST7CALLBACK(" & n & ";D1:D" & (n+1) & ")" 
ThisComponent.calculate() 
c.clearContents(-1) 
Function TEST7CALLBACK(n, a)  ' a is Variant(1 to n+1, 1 to 1) 
  Dim i :   For i = 0 To n : zGbl(i) = a(i+1,1) : Next i 
  TEST7CALLBACK = zGbl(1) 
End Function