https://bugs.documentfoundation.org/show_bug.cgi?id=173022
Bug ID: 173022
Summary: Manipulating spreadsheet data in bulk with the API is
very slow
Product: LibreOffice
Version: unspecified
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: sdk
Assignee: [email protected]
Reporter: [email protected]
Description:
I wanted to create a macro which rotates the data in a range of cells in a
spreadsheet by 180° for a project. The macro is easy to write but it runs very
slowly.
Steps to Reproduce:
* Enable macros with Tools->Options, LibreOffice->Security, Macro Security and
select medium.
* Open the attached spreadsheet.
* Click “Enable macros” when it asks.
* Click on one of the numbers and then press Ctrl+A to select the whole sheet.
You should see in the status bar that 100 rows × 702 columns are selected. If
there are numbers in the millions then something has gone wrong.
* Click Tools->Macros->Run macro…
* Select big-sheet.ods->KnittingMacros->rotate_selection_slow
* Click Run
* Wait for a bit until a dialog pops up saying how long the macro took.
Actual Results:
The macro takes about 30 seconds on my machine.
Expected Results:
It should run at a lot faster.
Reproducible: Always
User Profile Reset: No
Additional Info:
I tried profiling LibreOffice with callgrind while the macro is running and it
looks like the slowness is caused because every cell update triggers a
broadcast which calls ScTabView::UpdateFormulas. Internally in the LibreOffice
code it looks like bulk updates are handled by creating an
ScDocShellModificator object on the stack. This temporarily prevents the
broadcasts until the object is destroyed.
I had a go at making a patch to expose this functionality via the API. It adds
a method to spreadsheet documents called executeSheetJob which takes an XJob as
a parameter. The job is run while being protected with an
ScDocShellModificator. If you apply the patch then you can see the difference
if you run the rotate_selection_fast version of the macro. On my machine it
runs in about 4 seconds.
https://gerrit.libreoffice.org/c/core/+/209013
I also made a BASIC version of the macro which is in the document as
RotateSelection and RotateSelectionFast in the BASICKnittingMacros library.
This version is unbearably slow because in addition to the problems above the
UI is updated after every cell modification. The Fast version shows that it’s
possible to use the new method from BASIC. It does improve the speed but it’s
still unreasonably slow.
Just out of curiosity I also tried making a C++ version of the function as an
extension to see if Python is adding any overhead. It runs at about the same
speed.
https://github.com/bpeel/rangerotator
--
You are receiving this mail because:
You are the assignee for the bug.
