View previous topic :: View next topic |
Author |
Message |
billycar Sentinel
Joined: Nov 02, 2007 Posts: 26 Location: Central Piedmont VA / USA
|
Posted: Thu Dec 11, 2008 8:56 am Post subject: Speed of Executing a NeoOffice Basic Sub on Different Pages |
|
I'm curious why the same function that retrieves the current time and places it in the cell, takes noticeably longer on certain pages, than on the others? The data retrieval function I use is "=TEXT(NOW(); "hh:mm")".
I also keep a running sum in minutes, of the difference in start and end times of my tasks, by the somewhat cumbersome column calculation:
=60*HOUR(SUM(Q4:Q202)) + MINUTE(SUM(Q4:Q202))
where Q4 is the start of tasks, and Q202 is an arbitrary maximum (202 being Infinity),
and Qi is the difference in start and end times, or column of differences calculated by:
= IF(Pi>Oi; Pi-Oi;"")
What is used in the cells Oi and Pi is
=TEXT(NOW(); "hh:mm").
1. What should I look at to determine why on page 1, it takes ~5 seconds to insert the TEXT(NOW(); ...) and it is almost instant on pages 2 and 3?
The number of tasks are somewhat comparable on each page: There are 51 rows on page 1, 41 and 70 on pages 2 and 3. Both pages 2 and 3 produce near instant insertion of current time in the cell. The "Infinity" for each page is 202, 195, and 210.
The first page is linked to pages 2 and 3, by propagating a Week Date to each page from the first. Then each page calculates the Days of the Week Dates for a top row display.
2. How might I optimize the column calculation ... looks like I'm calculating the total SUM(Q2,Q202) twice ... but not sure how to factor it out. In a SUB I write myself?
Somewhat thinking out loud here ... _________________ Bill Connelly, Musician and Painter
artsite:http://mysite.verizon.net/moonstoneartstudio/
myspace.com:http://www.myspace.com/moonstoneartstudio |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Thu Dec 11, 2008 9:32 am Post subject: Re: Speed of Executing a NeoOffice Basic Sub on Different Pa |
|
If you uncheck the Tools :: Cell Contents :: AutoCalculate menu, does the speed improve?
If yes, then the slowness most likely is not due to the formula that you are inputting. Instead, NeoOffice is recalculating all of the existing formulas in the sheet and that is what is causing the slowness.
Patrick |
|
Back to top |
|
|
shades Keymaker
Joined: Aug 18, 2005 Posts: 96
|
Posted: Thu Dec 11, 2008 10:56 am Post subject: |
|
On the Excel side, there are some functions that are identified as volatile, meaning they are recalculated every time something is done on the workbook. Some affect individual worksheets. In this case, NOW() would be considered a volatile function.
Not sure if the same on Calc,but seems consistent. |
|
Back to top |
|
|
billycar Sentinel
Joined: Nov 02, 2007 Posts: 26 Location: Central Piedmont VA / USA
|
Posted: Thu Dec 11, 2008 12:11 pm Post subject: Re: Speed of Executing a NeoOffice Basic Sub on Different Pa |
|
pluby wrote: | If you uncheck the Tools :: Cell Contents :: AutoCalculate menu, does the speed improve? |
No ... same effect.
On the average, it takes 6 seconds to complete the time fetch and insertion into the cell on Page 1.
Page 2 and 3, a maximum of 2 seconds. Often, faster ... "instant" feeling.
This is the same whether AutoCalculate is checked or not.
Are functions treated differently on the first page of a multiple page spreadsheet? Something about initializing them, always, on use of the first page, then they are "ready" for subsequent pages?
What about display refresh rate?
Continuing to think out loud ... _________________ Bill Connelly, Musician and Painter
artsite:http://mysite.verizon.net/moonstoneartstudio/
myspace.com:http://www.myspace.com/moonstoneartstudio |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Thu Dec 11, 2008 12:22 pm Post subject: |
|
You might be suffering from bug 2621. This bug in NeoOffice's underlying OpenOffice.org code was found to be triggered by having cells with lengthy text in the current sheet. Those cells would trigger the text bounds in all cells in the sheet to be recalculated (a very expensive process) every time there was a change in the sheet.
If you make a copy of the spreadsheet and in that copy, delete any empty rows does that stop the problem? If not, if you select the entire sheet and then set resize the row heights, does that change anything?
Patrick |
|
Back to top |
|
|
billycar Sentinel
Joined: Nov 02, 2007 Posts: 26 Location: Central Piedmont VA / USA
|
Posted: Thu Dec 11, 2008 11:16 pm Post subject: |
|
I tried resizing all rows and columns (essentially doubling them using Format > Column > Width; Format > Row > Height).
It seemed to have no effect ... other than to lengthen the time to retrieve the Time and Insert into the cell ... still faster on pages 2 & 3.
On the Extreme: I might delete everything except one row on each page and see what happens ... back in a moment ... _________________ Bill Connelly, Musician and Painter
artsite:http://mysite.verizon.net/moonstoneartstudio/
myspace.com:http://www.myspace.com/moonstoneartstudio |
|
Back to top |
|
|
billycar Sentinel
Joined: Nov 02, 2007 Posts: 26 Location: Central Piedmont VA / USA
|
Posted: Thu Dec 11, 2008 11:23 pm Post subject: |
|
Deleting all but 2 rows on each page (Sum of column of differences(1 value), and 1 row Start/Stop and Difference), and all text ... I now have even, but slow response times ... about 5 taps of my foot for each retrieve and insert.
Doesn't seem to get rid of the problem documented as bug #2621. _________________ Bill Connelly, Musician and Painter
artsite:http://mysite.verizon.net/moonstoneartstudio/
myspace.com:http://www.myspace.com/moonstoneartstudio |
|
Back to top |
|
|
pluby The Architect
Joined: Jun 16, 2003 Posts: 11949
|
Posted: Thu Dec 11, 2008 11:27 pm Post subject: |
|
billycar wrote: | Deleting all but 2 rows on each page (Sum of column of differences(1 value), and 1 row Start/Stop and Difference), and all text ... I now have even, but slow response times ... about 5 taps of my foot for each retrieve and insert.
Doesn't seem to get rid of the problem documented as bug #2621. |
Can you upload the stripped down spreadsheet somewhere that we can download it and look at it? That seems to be an awful long time for a simple set of formulas.
Patrick |
|
Back to top |
|
|
Markk Operator
Joined: Mar 15, 2007 Posts: 43 Location: Wisconsin US
|
Posted: Fri Dec 12, 2008 8:29 am Post subject: |
|
While OO Basic is really un-optimized and slow (like a true interpreter in the basic 1968 sense) this is unusual. I cannot reproduce it.
I created a spreadsheet, went to Sheet3 and cell B2 and entered
=text(NOW();"hh:mm")
and the result was instantaneous. I then went to Sheet1 cell B2 and did the same and the result was the same.
Just to reverse things I closed this spreadsheet, opened another and entered the formula in Sheet1:B2 first then on Sheet2:A2. No noticable difference and all came back as fast as I could see.
This is NeoOffice 2.2.5 Patch 4 on an 2.2 Ghz Macbook with 4 Gig memory and current OSX 10.5. |
|
Back to top |
|
|
shades Keymaker
Joined: Aug 18, 2005 Posts: 96
|
Posted: Fri Dec 12, 2008 8:39 am Post subject: |
|
The point of the slowing though has to do with the number of calculations that have to be done on the spreadsheet. So a simple formula on the worksheet will not slow down. It is when they combinations and numbers of formulas that contain the volatile functions begin to take its toll. Again this observation is based on years of use with MS Excel. |
|
Back to top |
|
|
Markk Operator
Joined: Mar 15, 2007 Posts: 43 Location: Wisconsin US
|
Posted: Fri Dec 12, 2008 9:37 am Post subject: |
|
shades wrote: | The point of the slowing though has to do with the number of calculations that have to be done on the spreadsheet. So a simple formula on the worksheet will not slow down. It is when they combinations and numbers of formulas that contain the volatile functions begin to take its toll. Again this observation is based on years of use with MS Excel. |
That is true but that is not what the original poster said - to wit:
"I'm curious why the same function that retrieves the current time and places it in the cell, takes noticeably longer on certain pages, than on the others? The data retrieval function I use is "=TEXT(NOW(); "hh:mm")".
Thus the amount of calculation shouldn't matter - it is the same in both cases even to the sheet as he shows in later posts. He is saying he is getting different times. I do not get different times. I just put 1000 rows of iterative junk in and I got the same result as before - no difference.
I do wonder about some kind of cache or loading effect, where he is perhaps memory constrained and the spreadsheet is re-calcing from the front and reloading there first. One test to make would be to swap the order and names of the spreadsheet sheets and see if the times change. |
|
Back to top |
|
|
billycar Sentinel
Joined: Nov 02, 2007 Posts: 26 Location: Central Piedmont VA / USA
|
Posted: Fri Dec 12, 2008 9:37 am Post subject: |
|
I put my file reduced "out there", but I cannot test to see how to hand it off to you:
Note: I had to remove the file previously linked here ... I solved the problem later in this thread ...
Thanks for all your attention to this issue ...
For timing purposes, I'm running a PCI Graphics Yikes! G4 overclocked to 450MHz. 1GB RAM. I experienced "instant" reply on the complete spreedsheet, pages 2 and 3, where page 1 was delayed. _________________ Bill Connelly, Musician and Painter
artsite:http://mysite.verizon.net/moonstoneartstudio/
myspace.com:http://www.myspace.com/moonstoneartstudio
Last edited by billycar on Sun Dec 14, 2008 10:23 am; edited 1 time in total |
|
Back to top |
|
|
sardisson Town Crier
Joined: Feb 01, 2004 Posts: 4588
|
Posted: Fri Dec 12, 2008 9:44 am Post subject: |
|
It won't help now, but my understanding is that NeoOffice 3 will be *much* faster dealing with spreadsheets with lots of formulæ/calculations....
Smokey _________________ "[...] whether the duck drinks hot chocolate or coffee is irrelevant." -- ovvldc and sardisson in the NeoWiki |
|
Back to top |
|
|
narf The Anomaly
Joined: Jan 21, 2007 Posts: 1075
|
Posted: Fri Dec 12, 2008 9:49 am Post subject: |
|
billycar wrote: | I put my file reduced "out there", but I cannot test to see how to hand it off to you:
mysite.verizon.net/moonstoneartstudio/PracticePlanner/PianoPracticePlannerE.ods |
Thanks. I was able to download this using curl in /Applications/Terminal
Code: | curl -O mysite.verizon.net/moonstoneartstudio/PracticePlanner/PianoPracticePlannerE.ods |
I'll look a little closer and see what I can see. Maybe I will have to dust off my G4
--fran |
|
Back to top |
|
|
sardisson Town Crier
Joined: Feb 01, 2004 Posts: 4588
|
Posted: Fri Dec 12, 2008 9:52 am Post subject: |
|
billycar wrote: | I put my file reduced "out there", but I cannot test to see how to hand it off to you:
mysite.verizon.net/moonstoneartstudio/PracticePlanner/PianoPracticePlannerE.ods
Suggestions? How to download an ods file in Firefox? |
The problem is Verizon's webserver; they're telling web browsers that the file is a web page, not a file to be downloaded You can load the file and do Cmd-S (or possibly drag the link directly into the Firefox download manager) to save the file.
I assume Verizon, like most large ISPs, does not provide a facility for users to add additional extension->MIME type mappings (side note: I wonder if anyone has investigated getting the OpenDocument formats into Apache's defaults?), or you could correct the problem that way. The other way to work around Verizon's problem is to zip the file before uploading it (with the "Create an Archive of foo"/"Compress Foo" items in the Finder's context menu); it's redundant (and may cause Safari to completely tear apart the enclosed .ods on download; not sure), but Verizon probably has the right settings set up for .zip files
Smokey _________________ "[...] whether the duck drinks hot chocolate or coffee is irrelevant." -- ovvldc and sardisson in the NeoWiki |
|
Back to top |
|
|
|