Slow Calc Macros

users
#1

I did a python macro that adds sheets, renames them and fills in some formula in select cells. It also has a portion that deletes the sheets.

I find that the sheet creation runs faster although there's more lines of code running. Whereas the sheet removal macro is just 2lines and runs 3-5times slower. Both routines use 'FOR' loops (the creating routine even has a nested loop).

What could be responsible? Does calc recalculate for each removed sheet? Can that feature be toggled off via macro just before the removal loops ...and toggled back on?

Thanks in advance :slight_smile:

#2

Am 04.08.2011 09:03, Onyeibo Oku wrote:

I did a python macro that adds sheets, renames them and fills in some formula in select cells. It also has a portion that deletes the sheets.

I find that the sheet creation runs faster although there's more lines of code running. Whereas the sheet removal macro is just 2lines and runs 3-5times slower. Both routines use 'FOR' loops (the creating routine even has a nested loop).

What could be responsible? Does calc recalculate for each removed sheet? Can that feature be toggled off via macro just before the removal loops ...and toggled back on?

Thanks in advance :slight_smile:
---------------------------------------------
from twohot@device.mobile :slight_smile:

Removed sheets may have references, a new sheet can not have any references nor is it referenced.

bAC = isAutomaticCalculationEnabled()
doc.enableAutomaticCalculation(False)
do_stuff
doc.enableAutomaticCalculation(bAC)

#3

Thanks Andreas,

I've done that but the problem persists. I also made another
observation. One of the reserved sheets have formulas spanning about
10columns by 1750rows (some of them having up to 4-6levels of nested
'IF's with conditional formats). When I remove that sheet, the macro
executes instantly.

What I don't understand is why Calc still computes that sheet while
deleting other sheets. The macro makes no reference to it and it
doesn't reference the deleted sheets. The 'culprit' sheet uses named
cell refs but all within itself except one cell that references a
cache sheet which never gets deleted. More baffling is that it happens
with AutomaticCalculation set to False.

I wonder if watching the huge range with a listener will produce
better performance results.

#4

Perhaps you can also add

doc.lockControllers()

do something

doc.unlockControllers()

#5

<snip>

Removed sheets may have references, a new sheet can not have any
references nor is it referenced.

bAC = isAutomaticCalculationEnabled()
doc.enableAutomaticCalculation(False)
do_stuff
doc.enableAutomaticCalculation(bAC)

Thanks Andreas,

I've done that but the problem persists. I also made another
observation. One of the reserved sheets have formulas spanning about
10columns by 1750rows (some of them having up to 4-6levels of nested
'IF's with conditional formats). When I remove that sheet, the macro
executes instantly.

What I don't understand is why Calc still computes that sheet while
deleting other sheets. The macro makes no reference to it and it
doesn't reference the deleted sheets. The 'culprit' sheet uses named
cell refs but all within itself except one cell that references a
cache sheet which never gets deleted. More baffling is that it happens
with AutomaticCalculation set to False.

I wonder if watching the huge range with a listener will produce
better performance results.

Perhaps you can also add

doc.lockControllers()

do something

doc.unlockControllers()

Thanks Andrew,

I've tried that. No Joy :frowning:

I removed formulas from the 'Culprit' sheet between Row11 and Row 1760,
ran the sheet-creation/deletion macro, and the speed was excellent. So
it seems OOo/Libreoffice is iterating those cells at each sheet
deletion. *That is not good*. Either the 'IF' formula statements
attract some performance hits or the named-referencing is doing
something weird.

I still have one formula summing the 1750rows ... and the macro runs
fine. Looks like I have to deduce some kind of algorithm to avoid the
'IF's or whatever is causing this drag.

#6

Hi :slight_smile:
Sorry for side-tracking this but what is this listener you refer to? Just
curious because it's new to me.

Regards from
Tom :slight_smile:

#7

General rule of thumb: 99% of all spreadsheet macros are potentially harmful, counter productive, badly implemented and reveal nothing but the author's ignorance against the underlying spreadsheet application.
Could it be that a most simple database would solve all your problems without a single line of silly Basic?

#8

Hi

I'm thinking of the best way to explain. its a code that is attached to a software object that monitors that object for changes and runs another code as a result.

For example you may want your alarm to ring by 7pm so you set up a code to watch (listen to) the system clock and tell that code to run the alarm when the clock strikes 7pm. That code is a kind of listener. In Calc you can script codes to monitor cell ranges for changes and run other codes based on the result of that change.

Hope that was descriptive enough

#9

Hi :slight_smile:
Lol, wow!! That sounds scarily intriguing. Suddenly gives me 1 more reason to
trust OpenSource more than proprietary code!
Thanks and regards from
Tom :slight_smile:

#10

1) True but what I'm doing is more computational than relational though the end result will be a material for building a proper database. A spreadsheet is more suited for this ... Trust me on this.

2) All the macro does is create and remove sheets as needed (for now). All calculations are done by the spreadsheet formulas ... The 'IF' statement I mentioned is a spreadsheet logical function.

3) Its Python not BASIC.

#11

Hi,

1) True but what I'm doing is more computational than relational though the end result will be a material for building a proper database. A spreadsheet is more suited for this ... Trust me on this.

2) All the macro does is create and remove sheets as needed (for now). All calculations are done by the spreadsheet formulas ... The 'IF' statement I mentioned is a spreadsheet logical function.

3) Its Python not BASIC.

---------------------------------------------
from twohot@device.mobile :slight_smile:

From: Andreas Säger <villeroy@t-online.de>
Date: Sat, 06 Aug 2011 12:34:13
To: <users@global.libreoffice.org>
Reply-To: users@global.libreoffice.org
Subject: [libreoffice-users] Re: Slow Calc Macros

General rule of thumb: 99% of all spreadsheet macros are potentially
harmful, counter productive, badly implemented and reveal nothing but
the author's ignorance against the underlying spreadsheet application.
Could it be that a most simple database would solve all your problems
without a single line of silly Basic?

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Depending on the complexity of the calculations you might find Base
easier to use. Relational databases support normal arithmetic and simple
statistical functions (sum, count, average) because they are implemented
in SQL. The more complex functions are not supported by SQL such as trig
or log.

#12

Hi,

1) True but what I'm doing is more computational than relational though
the end result will be a material for building a proper database. A
spreadsheet is more suited for this ... Trust me on this.

Okay, I may be wrong here because I speak as bound by the level of
knowledge I have on spreadsheets and database management, My
experience in dbase is small compared to spreadsheets. (See comments
below for and example of what I'm doing. Maybe someone can advise me
better.) I could be a moron after all. I am open to suggestions.

From: Andreas Säger <villeroy@t-online.de>
Date: Sat, 06 Aug 2011 12:34:13
To: <users@global.libreoffice.org>
Reply-To: users@global.libreoffice.org
Subject: [libreoffice-users] Re: Slow Calc Macros

General rule of thumb: 99% of all spreadsheet macros are potentially
harmful, counter productive, badly implemented and reveal nothing but
the author's ignorance against the underlying spreadsheet application.
Could it be that a most simple database would solve all your problems
without a single line of silly Basic?

Depending on the complexity of the calculations you might find Base
easier to use. Relational databases support normal arithmetic and simple
statistical functions (sum, count, average) because they are implemented
in SQL. The more complex functions are not supported by SQL such as trig
or log.

--
Jay Lozier
jslozier@gmail.com

So, let me float this problem. I probably will get a better approach
to it by sampling the pros. If you work in an educational facility and
want to reel in lots of result sheets from teachers. You are dealing
with people who find spreadsheets foreign and databases extra-alien
and geeky. These people are the source of data for the main database.

On the other hand, you want to consider speed of data generation and
data portability. You want to set up a template to allow these people
supply this information at little or no cost using existing
infrastructure and with minimal training. There is no local network
yet, and the internet in the facility is not for result management and
you need to start building these data NOW!

Another thing to consider is that the results must be presented in a
certain way. Forms can do this ... Spreadsheets can also do that. But
these forms should be dynamic to spread results across legal size
papers depending on number of candidates. This presentation is for
printing purposes only and not for the Dbase.

Please suggest the best route. My route now is to provide these people
with a spreadsheet template that takes care of their usual result
analysis and grading. It organises the results into sheets for
printing using a pre-designed sheet that it copies as necessary for
more sheets. We have to option of submitting the spreadsheet document
for use in building institution wide DB or using CSV versions instead.
I am doing this as a contribution to an institution that will only
accept a movement to IT based management when they see something that
works and it has to be done fast and on-the-go without disturbing the
programme and the employees much.

What do you guys suggest? Base for those teachers' inputs (or should
it be Calc?), while the rest can be strictly DB programs. I want this
to be done with opensource solutions since I'm already suggesting
opensource OS for use in same institution. Thanks

#13

Hi,

Onyeibo Oku schrieb:

I did a python macro that adds sheets, renames them and fills in some
formula in select cells. It also has a portion that deletes the
sheets.

I find that the sheet creation runs faster although there's more
lines of code running. Whereas the sheet removal macro is just 2lines
and runs 3-5times slower. Both routines use 'FOR' loops (the creating
routine even has a nested loop).

What could be responsible? Does calc recalculate for each removed
sheet? Can that feature be toggled off via macro just before the
removal loops ...and toggled back on?

If you delete a sheet in the UI, then an undo object is generated. Does this happen in your macro too?

Kind regards
Regina

#14

Yes.

You can undo almost everything done with a macro ... So far, I've been
able to undo all changed made by my macros. I haven't come across an
incomplete redo yet.

Regards
Onyeibo

#15

Hi Onyeibo,
twohot schrieb:

Hi,

Onyeibo Oku schrieb:

I did a python macro that adds sheets, renames them and fills in some
formula in select cells. It also has a portion that deletes the
sheets.

I find that the sheet creation runs faster although there's more
lines of code running. Whereas the sheet removal macro is just 2lines
and runs 3-5times slower. Both routines use 'FOR' loops (the creating
routine even has a nested loop).

What could be responsible? Does calc recalculate for each removed
sheet? Can that feature be toggled off via macro just before the
removal loops ...and toggled back on?

If you delete a sheet in the UI, then an undo object is generated. Does
this happen in your macro too?

Kind regards
Regina

Yes.

You can undo almost everything done with a macro ... So far, I've been
able to undo all changed made by my macros. I haven't come across an
incomplete redo yet.

Perhaps 'undo' is the reason, that delete is slower than insert. But I don't know any way to disable 'undo' to test my guess.

Kind regards
Regina

#16

Hi,

> Hi,
>
>
>> 1) True but what I'm doing is more computational than relational though
>> the end result will be a material for building a proper database. A
>> spreadsheet is more suited for this ... Trust me on this.
>>

Okay, I may be wrong here because I speak as bound by the level of
knowledge I have on spreadsheets and database management, My
experience in dbase is small compared to spreadsheets. (See comments
below for and example of what I'm doing. Maybe someone can advise me
better.) I could be a moron after all. I am open to suggestions.

>> From: Andreas Säger <villeroy@t-online.de>
>> Date: Sat, 06 Aug 2011 12:34:13
>> To: <users@global.libreoffice.org>
>> Reply-To: users@global.libreoffice.org
>> Subject: [libreoffice-users] Re: Slow Calc Macros
>>
>> General rule of thumb: 99% of all spreadsheet macros are potentially
>> harmful, counter productive, badly implemented and reveal nothing but
>> the author's ignorance against the underlying spreadsheet application.
>> Could it be that a most simple database would solve all your problems
>> without a single line of silly Basic?
>>

> Depending on the complexity of the calculations you might find Base
> easier to use. Relational databases support normal arithmetic and simple
> statistical functions (sum, count, average) because they are implemented
> in SQL. The more complex functions are not supported by SQL such as trig
> or log.
>
> --
> Jay Lozier
> jslozier@gmail.com

So, let me float this problem. I probably will get a better approach
to it by sampling the pros. If you work in an educational facility and
want to reel in lots of result sheets from teachers. You are dealing
with people who find spreadsheets foreign and databases extra-alien
and geeky. These people are the source of data for the main database.

On the other hand, you want to consider speed of data generation and
data portability. You want to set up a template to allow these people
supply this information at little or no cost using existing
infrastructure and with minimal training. There is no local network
yet, and the internet in the facility is not for result management and
you need to start building these data NOW!

Another thing to consider is that the results must be presented in a
certain way. Forms can do this ... Spreadsheets can also do that. But
these forms should be dynamic to spread results across legal size
papers depending on number of candidates. This presentation is for
printing purposes only and not for the Dbase.

Please suggest the best route. My route now is to provide these people
with a spreadsheet template that takes care of their usual result
analysis and grading. It organises the results into sheets for
printing using a pre-designed sheet that it copies as necessary for
more sheets. We have to option of submitting the spreadsheet document
for use in building institution wide DB or using CSV versions instead.
I am doing this as a contribution to an institution that will only
accept a movement to IT based management when they see something that
works and it has to be done fast and on-the-go without disturbing the
programme and the employees much.

What do you guys suggest? Base for those teachers' inputs (or should
it be Calc?), while the rest can be strictly DB programs. I want this
to be done with opensource solutions since I'm already suggesting
opensource OS for use in same institution. Thanks

What your problem may need is web-type interface that users can input
their data and it is transfer another application for processing and
report generation. This is typically done with a webpage feeding the
data to the appropriate tables in a database. Depending on your data
analysis SQL may be sufficient or you can export to a spreadsheet.
Databases can do very basic arithmetic and statistical calculations. The
reports can generated in Base or you can import your data into Writer
document. This depends on the amount of data and how you wish to present
it.

The web interface can allow users to construct queries by selecting the
type of information and entering any constraints. This can be so the
user is oblivious to the fact they are interacting with a database.
Printable reports can be generated, if desired.

The key to constructing a workable database is the proper modeling of
the data relationships so the user enters each once and the minimum
amount of related tables are created. Ideally each piece of data is
found only once in a some table. For example, a business would have one
table of customer addresses and would link customer orders to this table
so you could get the shipping information. When you log on to Amazon.com
from your log on they may know your preferred shipping address and can
access your previous orders.

#17

Played around the code. Seems COUNTIF's and COUNTBLANK's perform
expensive iterations so I replaced those. Now the deletion speed is
nearly same as the creation speed ... I wish I can get more speed though
:slight_smile: I'd hate to run this in M$ Office and get a better performance with
VBA. I hope that doesn't happen because I need to convince my employers
that Open-Source is the way forward.

Hmm, so LibreOffice stores strings in unicode. Interesting. Why didn't
any of you guru-istic forerunners tell me I could use the terminal for
debugging? This is so sweet. :slight_smile:

#18

Do not know if this will aid you in the testing, but here goes.

1. Open Calc or Write,
2. Go To: Tools --> Option,
3. Select (expand): LibreOffice,
4. Select: Memory,
5. Under: Undo,
6. Set 'Number of steps', to zero '0',
7. Click on the 'Ok' button,
8. Close LibreOffice,
9. Reopen LibreOffice,
10. Conduct test.

Reset to 'Number of steps', to zero '100' when done.

Please let us all know if this works for you.