Tested on Macbook Pro with MacOSX 10.6.8 (Snow Leopard) :

OOo 3.2.1 : 16.272 seconds
OOo 3.3.0 : 17.642 seconds
OOo-dev 3.4.0 : 17.994 seconds

LibO 3.3.4 : 42.992 seconds
LibO 3.4.3 : 42.296 seconds
Lib0 3.4.4 : 41.727 seconds
LibO 3.4.5 : 41.65 seconds
LibO 3.5 RC2 : 140.075 seconds !!!!!!!   <=======


Lotus Symphony 3.0.1 : 2.25 seconds !! 
but fails to insert names correctly, result below :
#NOM?	abc

NeoOffice 3.1.2 patch 8 : 5.75 seconds, same erroneous input as for Symphony
NeoOffice 3.2.1 patch 3 : 4.24 seconds, same erroneous input as for Symphony

Well, it does show one thing, that LO is noticeably slower at this particular task than OOo !!!   Ooops !


Alex

IMO this should be a blocker, can anyone seriously imagine advising people to switch with performance that bad ?

Alex

Adding to blockers for 3.5 release

Same results for me with LO 3.5.0 rc3+ under Ubuntu 11.10 x86_64 : ~18 seconds
But if I replace the value uf string fun by
 fun = "=IF(not(ISBLANK(INDIRECT(""B"" & ROW())));row()&""."";"""")"
the macro gives the same result but runs in 0.736 second.
So the problem is in the call of function show_number().

@reporter: if you agree with that, you should update the summary of this bug
report with something more informative.

Best regards. JBF

This is not a blocker. This is just a normal issue.

We need to run callgrind on it and see where the problem is.

,

(In reply to comment #4)
> But if I replace the value uf string fun by
>  fun = "=IF(not(ISBLANK(INDIRECT(""B"" & ROW())));row()&""."";"""")"
> the macro gives the same result but runs in 0.736 second.
> So the problem is in the call of function show_number().
> 
> Best regards. JBF

Yes. You are right, however, in my original application, I use also much more complicated functions, which cannot be simply replaced like this one.

With best regards

Arnolf Best

It seems that there is a problem with AutoCalculate option: if you uncheck this option the macro runs in less than 1 second.

Best regards. JBF

(In reply to comment #8)
> It seems that there is a problem with AutoCalculate option: if you uncheck this
> option the macro runs in less than 1 second.
> 
> Best regards. JBF

That's true.

I ran the test after unchecking the AutoCalculate option (by the way, I need this option checked normally) and setting END_ROW to 1999.

The results are as follows:

OpenOffice  3.3.0 - 6,1 seconds
LibreOffice 3.4.5 - 7,5 seconds

I always had the impression that the performance of the OOo calc is higher than that of the LO calc. This is strange. Since the LO package has been optimized by removing a lot of obsolete and unused code, I would rather expect the contrary.

With best regards

Arnolf Best

The subject has been discussed on the French users lists and Michel Rudelle suggested to modify your macro by adding 
oDoc.enableAutomaticCalculation(False) 
at the beginning of the macro
and 
oDoc.enableAutomaticCalculation(True) 
at the end.

I did my tests with LO 3.5.0 rc3+ and LO 3.4.5 and the modified macro runs in less than 1 second, AutoCalculate activated (before and after the macro).

Best regards. JBF

(In reply to comment #10)
> oDoc.enableAutomaticCalculation(False) 
> at the beginning of the macro
> and 
> oDoc.enableAutomaticCalculation(True) 
> at the end.

Many Thanks!

This a valuable tip for writing macros. Nevertheless, the differences in performance between OOo and LO should be investigated and cleared.

Best regards

AB

Hello

With my environnment(Intel 2 Duo 3.07GHz CPU, 4 GB RAM Win 7 64) the initial procedure runs 9.687 (for 200 lines added)

Let me add a few tips

1. Display management

Select each cell before filling it is useless. Remove this reduces to 9.47

2. Locking the document

The pair of instructions lockControllers unLockControllers can locks the document during treatment to reduce to 6.47

3. Management recalculation

As already said, inhibit automatic recalculation for the loop can reduce to 0.218 (2.979 for 2000 lines added)

4. Management of data type

4.1 NR (the first argument of the procedure show_number) receives the result of the ROW function. This is a number but it is unnecessary to convert to text to concatenate with the "." (it is also unnecessary to add the TRIM function). With 2000 lines added it can reduce the execution time from 2.979 to 2.948 and let the function itself faster.

4.2 Using the  "integer" type for a variable that holds the line numbers can be "dangerous" as this type is limited to 32,767 while the number of lines is now 1,048,576 (error message "Inadmissible value or data type. Overflow ")

Below is the code with these changes

Regards
Pierre-Yves

'*****************************************
option explicit

Sub test
Const END_ROW As Integer = 1999

Dim document   As Object
Dim dispatcher As Object

document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Dim oDoc   As Object
Dim oSheet As Object
Dim oRange As Object
Dim oCell  As Object
dim fenDoc as Object

oDoc   = ThisComponent
oSheet = oDoc.getCurrentController.ActiveSheet

' Select and clean the test cell range

oRange = oSheet.getCellRangeByPosition(0, 0, 1, END_ROW)
oDoc.CurrentController.select(oRange)

dispatcher.executeDispatch(document, ".uno:ClearContents", "", 0, Array())

Dim fun As String

fun = "=SHOW_NUMBER(ROW(); ISBLANK(INDIRECT(""B"" & ROW())))"

Dim start_time As Long

' Fill the test cell range:
' column 1: formula for displaying the row number
' column 2: a constant string

Dim row As Integer

thisComponent.lockControllers
thisComponent.enableAutomaticCalculation(false)

start_time = GetSystemTicks()

For row = 0 To END_ROW
' oRange = oSheet.getCellRangeByPosition(0, row, 0, row)
' oDoc.CurrentController.select(oRange)
 oCell = oSheet.getCellByPosition(0, row)
 oCell.setFormula(fun)
 oCell = oSheet.getCellByPosition(1, row)
 oCell.String = "abc"
Next row

oRange = oSheet.getCellRangeByPosition(0, 0, 0, 0)
oDoc.CurrentController.select(oRange)

'oRange = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
'oDoc.CurrentController.select(oRange)

thisComponent.enableAutomaticCalculation(true)
thisComponent.calculateAll
thisComponent.unlockControllers

Print "Test executed in " + (GetSystemTicks() - start_time)/1000 + " s"

End Sub

'**************************************************************************

Function show_number(ByVal nr As Integer, ByVal empty As Boolean) As String
show_number = ""
If Not empty Then
'  show_number = Trim(Str(nr)) + "."
 show_number = nr & "."
End If
End Function

I did some tests won WIN7 64Bit:

OOo 3.1.1            11s
LibO 3.3.0 Portable   9s
LibO 3.4.1RC         14s
LibO 3.4.2RC         24s
LibO 3.4.5           15s
LibO Master Jul 11   32s
LibO Master Sept 11  Test impossible
LibO Master [1]      23s
LibO 3.5.0Beta0      44s
LibO 3.5.0Beta1      42s
LibO 3.5.0RC2        55s 
LibO 3.5.0RC3        73s                         

I did not check whether settings have an influence 

[1]: Server installation of Master "LibO-dev 3.5.0 – WIN7 Home Premium (64bit) ENGLISH UI [(Build ID:  d38713d-5d03837-ca7e6f5-c4bb9bd-ce71330)]" (Voreppe_Win32_Tinderbox 111115)

It seems the mess started with 3.4.0, becomes worse the longer LibO exists, but some versions are "performance islands"  where the problem is not as big as in versions eford.

@Noel:
Can you help? Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.

Created attachment 56617 [details]
More comfortabel testing

Simply click the button to execute test macro

(In reply to comment #12)
Hi,

Of course, you are absolutely right with your remarks, but it was not my goal
to create an optimal test macro, rather the opposite, e.g. I do not select each
cell before filling in my original application. The aim was just to exhibit the
performance differences between OOo and LO. Moreover, I can imagine that it is
possible to create another test examples with more complicated functional
relationships between different cells, for which one cannot avoid the
performance decrease so easily like in my simple test.

The problem is still open. OOo and LO have the same roots. What is the cause
for the performance difference between them? Even the optimised test macro is running slightly faster on OOo.

With best regards

Arnolf Best

This is a calc issue and not a basic issue. I need to run it in callgrind and check if it is related to our other issue in this area.

Component due to latest comments 
I again forgo to assign ...
  Markus for now!

@Markus:
Please feel free ...

(In reply to comment #12)
> Hello
> 

> Below is the code with these changes
> 
> Regards
> Pierre-Yves


Hi Piere-Yves,
Tested your changes on same Macbook as initial macro execution for comparison :

NeoOffice 3.1.2p8 : 6.569s
NeoOffice 3.2.1p3 : 6.357s

OOo 3.2.1 : 3.261s
OOo 3.3.0 : 3.292s
OOo 3.4-dev : 4.024s

LO 3.3.4 : 5.617s
LO 3.4.3 : 5.644s
LO 3.4.4 : 5.768s
LO 3.4.5 : 5.634s
LO 3.5RC2 : 14.222 <=======

Symphony 3.0.1 : 3.659s


Alex

(In reply to comment #18)
> LO 3.5RC2 : 14.222 <=======

For avoidance of doubt:
- I do not want to hijack this thread of his *legitimate* subject
- I wanted to add some advice as necessary (the details provided by Arnolf suggest that they were useless)
- I started my comment with: "Let me *add* a few tips..."

Regards
PYS

After looking a bit closer I even doubt that it is an annoying bug. It only happens if you use basic macros in a calc formula but I don't yet understand what is going wrong.

The problem is neither directly in the calc code nor in the basic code but at least what I get from my totally useless callgrind log somewhere in i18pool.

An easy workaround to the bug might be to disable auto calculate at the beginning of the macro and enable it again at the ending.

Thanks to Eike I have a fix for 3.6 but I'm not sure if we want that fix in 3.5.

I'm down to 9s from 35s on a reasonably fast computer in a debug/dbgutil build.

There is still a second performance problem that would allow us to become even faster but I have not yet an idea how to solve that.

Congratulations!

Good work. Maybe LO calc becomes faster than OO calc.
Unfortunately, there is no news about the next OO release from Apache.

Migrating Whiteboard tags to Keywords: (perf)
[NinjaEdit]