Speed of Executing a NeoOffice Basic Sub on Different Pages

billycar

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 ...

pluby · The Architect Joined: Jun 16, 2003 Posts: 11949

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

shades · Keymaker Joined: Aug 18, 2005 Posts: 96

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.

billycar

pluby · The Architect Joined: Jun 16, 2003 Posts: 11949

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

billycar · 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 ...

billycar · 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.

pluby · The Architect Joined: Jun 16, 2003 Posts: 11949

Markk · 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.

shades · Keymaker Joined: Aug 18, 2005 Posts: 96

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.

Markk · Posted: Fri Dec 12, 2008 9:37 am Post subject:

billycar · 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.

sardisson · Town Crier Joined: Feb 01, 2004 Posts: 4588

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

narf · The Anomaly Joined: Jan 21, 2007 Posts: 1075

sardisson · Town Crier Joined: Feb 01, 2004 Posts: 4588