Download it now!
Bug 45607 - Particular BASIC Macro execution significant slower than with OOo, NeoOffice, Symphony
Summary: Particular BASIC Macro execution significant slower than with OOo, NeoOffice,...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.0 release
Hardware: x86-64 (AMD64) All
: high major
Assignee: Markus Mohrhard
URL:
Whiteboard: target:3.6
Keywords: perf
Depends on:
Blocks: mab3.5
  Show dependency treegraph
 
Reported: 2012-02-04 01:15 UTC by Arnolf Best
Modified: 2015-12-15 11:31 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
More comfortabel testing (9.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-02-05 02:03 UTC, Rainer Bielefeld Retired
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Arnolf Best 2012-02-04 01:15:51 UTC
Curious about the LibreOffice, I wanted to run my application which was created in OpenOffice calc. It was a surprise to see a significant difference in execution time between LO and OO. After isolating the problem, I could measure the performance in both packages.

Please run the following test macro which fills an area of 200 rows with a formula in the column 1 and a string in the column 2.

Execution time on my computer:

OpenOffice  3.3.0 -  8 seconds
LibreOffice 3.4.5 - 15 seconds

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

Sub test
 Const END_ROW As Integer = 199

 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

 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

 start_time = GetSystemTicks()

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

 Dim row As Integer

 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)

 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)) + "."
 End If
End Function
Comment 1 Alex Thurgood 2012-02-04 03:12:26 UTC
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
Comment 2 Alex Thurgood 2012-02-04 03:25:27 UTC
IMO this should be a blocker, can anyone seriously imagine advising people to switch with performance that bad ?

Alex
Comment 3 Alex Thurgood 2012-02-04 03:30:07 UTC
Adding to blockers for 3.5 release
Comment 4 Jean-Baptiste Faure 2012-02-04 04:57:44 UTC
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
Comment 5 Markus Mohrhard 2012-02-04 05:04:52 UTC
This is not a blocker. This is just a normal issue.

We need to run callgrind on it and see where the problem is.
Comment 6 Oliver Brinzing 2012-02-04 05:49:29 UTC
,
Comment 7 Arnolf Best 2012-02-04 06:46:45 UTC
(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
Comment 8 Jean-Baptiste Faure 2012-02-04 12:16:15 UTC
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
Comment 9 Arnolf Best 2012-02-04 13:58:55 UTC
(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
Comment 10 Jean-Baptiste Faure 2012-02-04 15:11:22 UTC
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
Comment 11 Arnolf Best 2012-02-04 15:38:02 UTC
(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
Comment 12 pierre-yves samyn 2012-02-04 23:43:44 UTC
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
Comment 13 Rainer Bielefeld Retired 2012-02-05 01:59:50 UTC
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.
Comment 14 Rainer Bielefeld Retired 2012-02-05 02:03:28 UTC
Created attachment 56617 [details]
More comfortabel testing

Simply click the button to execute test macro
Comment 15 Arnolf Best 2012-02-05 02:38:34 UTC
(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
Comment 16 Markus Mohrhard 2012-02-05 02:55:23 UTC
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.
Comment 17 Rainer Bielefeld Retired 2012-02-05 03:04:17 UTC
Component due to latest comments 
I again forgo to assign ...
  Markus for now!

@Markus:
Please feel free ...
Comment 18 Alex Thurgood 2012-02-05 04:25:42 UTC
(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
Comment 19 pierre-yves samyn 2012-02-05 05:06:44 UTC
(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
Comment 20 Markus Mohrhard 2012-02-07 16:26:42 UTC
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.
Comment 21 Markus Mohrhard 2012-02-10 13:56:18 UTC
Thanks to Eike I have a fix for 3.6 but I'm not sure if we want that fix in 3.5.
Comment 22 Markus Mohrhard 2012-02-10 14:01:26 UTC
I'm down to 9s from 35s on a reasonably fast computer in a debug/dbgutil build.
Comment 23 Markus Mohrhard 2012-02-10 14:02:58 UTC
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.
Comment 24 Arnolf Best 2012-02-11 11:27:47 UTC
Congratulations!

Good work. Maybe LO calc becomes faster than OO calc.
Unfortunately, there is no news about the next OO release from Apache.
Comment 25 Robinson Tryon (qubit) 2015-12-15 11:31:21 UTC
Migrating Whiteboard tags to Keywords: (perf)
[NinjaEdit]