Hi there,
Is there a reference guide anywhere that provides the speed of operation for the LO CALC functions?
In this instance, I’m interested in finding out whether it’s less processor intensive to query COUNT() or MAX(). The records being interpreted by MAX() will be in clusters where the index number for reference on up to 8 rows is identical (pre-processed) and the COUNT() process would just divide the result by 8 and the fraction would indicate the number of partial components of the final cluster. (I don’t need anything to convert decimal eighths to component parts - I have been able to do that in my sleep since I was able to count to eight)
I’m aware that any (pre-)processing for either of the functions would need to be considered to provide the fully definitive answer but it seems logical that there must be a “function performance” reference somewhere - or is it something that comes with Eons of programming practice ;))?
I don’t think you will find the information you hope for giving reliable (relative) values. They simply might depend on too many parameteres.
Anyway: Both the functions you mentioned are fast. More attention might require functions needing to do a kind of statistics like FREQUENCY() e.g., or …
I tested again a bit on my (about 10 years old) PC and got around 6.4 ms for a MAX() and a COUNT() and a CONTIF() as well when applied to an array of 10 000 elements.
===Edit 2020-11-29 about 14:35===
The code announced in my comment below:
Sub runFunctionStopWatch() REM support for manually arranged testing
REM define the needed constants:
Const useRegEx = True, fnName = "COUNTIF", tally = 1000
Const sheetN = "TestSheet", outRgN = "b2:e2"
Const rgN1 = "a2:a10001", rgN2 = "", val1 = 0.1, val2 = 0, tx1 = "<", tx2 = "" REM Partly unused!
REM Create the needed objects
fa = CreateUnoService("com.sun.star.sheet.FunctionAccess")
fa.RegularExpressions = useRegEx
doc = ThisComponent
sheet = doc.Sheets.getByName(sheetN)
rg1 = sheet.getCellRangeByName(rgN1)
outRg = sheet.getCellRangeByName(outRgN)
outDA = outRg.getDataArray
REM Assemble the arguments. Example to the effect of =COUNTIF(A2:A10001; "<" & 0.1)
arguments = Array(rg1, tx1 & val1)
REM Start stopwatch and run:
ts = fa.callFunction("NOW", Array()) REM The Basic function Now() works differently and isn't suitable here.
For j = 1 To tally
res = fa.callFunction(fnName, arguments)
Next j
REM Stop stopwatch:
te = fa.callFunction("NOW", Array())
REM Evaluate, make output:
tr = te - ts
outDA(0)(0) = res
outDA(0)(1) = (tr/tally) * (24*60*60)
outDA(0)(2) = tally
outDA(0)(3) = fnName
outRg.setDataArray(outDA)
End SubThanks for investigating it for me. Your response prompts another question - How did you time it? Clearly timing the function over 10,000 elements is the way to go but are you using a manual stopwatch or is there a timing function somewhere in CALC. I had no idea where to look for a timing function ;(.
Again, thanks for your endeavours.
Timing is done by calculating the difference of two NOW() results.
However, the Basic function Now() isn’t the same as the Calc function NOW(), and has a resolution of 1s only.
See the example code appended to my answer.
For service above and beyond the call of duty, you have my thanks.