Recent

Author Topic: Anyway to speed up worksheet calculation  (Read 1753 times)

kjteng

  • Full Member
  • ***
  • Posts: 149
Anyway to speed up worksheet calculation
« on: February 09, 2019, 04:19:53 am »
Worksheet recalculation takes about 2.2 second for a spreadsheet that contains more than 5000 formulas cells, This is so event when there is no change in value (i.e. press F2 followed by Enter) or if the change in cell value onlly affect one other cell.
Is there any way to speed up this?

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Anyway to speed up worksheet calculation
« Reply #1 on: February 09, 2019, 11:11:06 am »
Please be more specific. Maybe you should post a simple workbook which shows the issue. The following test recalculates 5000 formulas within a few milliseconds:

Code: Pascal  [Select]
  1. program Project1;
  2.  
  3. uses
  4.   SysUtils,
  5.   fpspreadsheet, fpstypes, xlsxOOXML;  
  6.  
  7. var
  8.   b: TsWorkbook;
  9.   sh: TsWorksheet;
  10.   r: Cardinal;
  11.   t: TDateTime;
  12. begin
  13.   b := TsWorkbook.Create;
  14.   try
  15.     sh := b.AddWorksheet('Test');
  16.     for r :=0 to 4999 do begin
  17.       sh.WriteNumber(r, 0, r);
  18.       sh.WriteFormula(r, 1, '=A'+IntToStr(r+1));
  19.     end;
  20.     t := Now;
  21.     b.CalcFormulas;
  22.     WriteLn('Time: ', FormatDateTime('s.zzz" sec"', now - t));
  23.     b.WriteToFile('test.xlsx', sfOOXML, true);
  24.   finally
  25.     b.Free;
  26.   end;
  27.  
  28.   ReadLn;
  29. end.  

You speak of pressing the F2 key, does this imply that you work with the visual spreadsheet controls?
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 149
Re: Anyway to speed up worksheet calculation
« Reply #2 on: February 20, 2019, 07:39:21 am »
Sorry for the late reponse because I was away.

Yes, I was working with visual controls (TsWorksheetGrid)

Attached is my test program for reference. My observations are as follows:

(I) 5000 formula: A1=B1, A2=B2, A3=B3 ...
    Approximate calculation time: 
      16 ms (if workbook not attached to grid <click test1 followed by Calc button>)
      203 ms (if workbook is attached to grid <click Attach followed by Calc button>)
 
(II) 5000 formula: A1=sum(B1:Z1), A2=sum(B2:Z2), A3=sum(B3:Z3) ...
     Approximate calculation time:
       1108 ms (if workbook not attached to grid <click test2 followed by Calc button>)
       1341 ms (if workbook is attached to grid <click Attach followed by Cacl button>)
   
(III) Open attached excel file bk5000formula.xls. Editing in the grid became very slow if boAutoCalc is set to True.


I would be grateful if you could advise on the following:

i. How to improve my code to speedup the worksheet grid

ii. Is it possible to refresh the grid when all the visible cells are recalculated (I think currently the grid is refreshed after all the formula are recalculated);

iii. As it is now, I am setting boAutoCalc=false so at least the speed is acceptable on faster pc. However, in manual calculation mode, worksheetGrid only display formula (instead of values)- this has lead to another request as posted in https://forum.lazarus.freepascal.org/index.php/topic,44208.0.html

kjteng

  • Full Member
  • ***
  • Posts: 149
Re: Anyway to speed up worksheet calculation
« Reply #3 on: February 20, 2019, 07:41:52 am »
Attached xls file

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Anyway to speed up worksheet calculation
« Reply #4 on: February 20, 2019, 12:39:25 pm »
Now that I have your file I set up a non-gui program loading the file and re-calculating the cells - and it takes a bit more than 1 second, comparable to your result. So, I think this is the best that we can get from fpspreadsheet.

This slow calculation is caused by the very simple formula calculation engine implemented: it invalidates all formula results, iterates through the worksheet and when it finds a formula with an invalid result, tries to calculate it. When this is successful the formula is marked as being calculated; if not it iterates across all non-calculated cells again, etc. etc. This recursive procedure is rather easily coded but very ineffective.

A faster solution would construct a calculation tree of the cells which are affected directly or indirectly by the modified cell, so that less cells have to be calculated and less traversals of the workbooks are required.

Unfortunately I don't have the time at the moment to implement something like this. But maybe there is someone out there who can provide a patch?

Until this happens you will have to live with the current speed.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

440bx

  • Hero Member
  • *****
  • Posts: 795
Re: Anyway to speed up worksheet calculation
« Reply #5 on: February 20, 2019, 01:46:37 pm »
This slow calculation is caused by the very simple formula calculation engine implemented: it invalidates all formula results, iterates through the worksheet and when it finds a formula with an invalid result, tries to calculate it. When this is successful the formula is marked as being calculated; if not it iterates across all non-calculated cells again, etc. etc. This recursive procedure is rather easily coded but very ineffective.
By any chance, wouldn't the calculations be slowed down by screen redrawing ?... I mention this because, as inefficient as the calculation method is, today's machines are very fast and 2 seconds is a lot of cycles.  That makes me think that, just maybe, a significant amount of time may be being spent on redrawing/updating the spreadsheet cells.

If that happens to be the case, sending the window a WM_SETREDRAW (false) message before starting the calculations followed by another set to (true) once the calculations are completed might improve the operation's times (might need an InvalidateRect and UpdateWindow as well.)

Just a thought.

using FPC v3.0.4 and Lazarus 1.8.2 on Windows 7 64bit.

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Anyway to speed up worksheet calculation
« Reply #6 on: February 20, 2019, 02:42:56 pm »
By any chance, wouldn't the calculations be slowed down by screen redrawing ?...
Of course. But the 1 second measured by myself without GUI is comparable to the values measured by kjteng for GUI (grid attached) or "semi-GUI" (grid not attached) which by themselves are only 200 ms apart. Therefore I guess the pure painting code already has been ruled out. I still need to look into the details to understand where the 200ms come from.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

440bx

  • Hero Member
  • *****
  • Posts: 795
Re: Anyway to speed up worksheet calculation
« Reply #7 on: February 20, 2019, 03:40:34 pm »
Now that I have your file I set up a non-gui program loading the file and re-calculating the cells - and it takes a bit more than 1 second, comparable to your result.
Of course. But the 1 second measured by myself without GUI is comparable to the values measured by kjteng for GUI (grid attached) or "semi-GUI" (grid not attached) which by themselves are only 200 ms apart.
If I read at the speed that spreadsheet does the recalculations I probably wouldn't have missed that you had already discounted that possibility.
using FPC v3.0.4 and Lazarus 1.8.2 on Windows 7 64bit.

kjteng

  • Full Member
  • ***
  • Posts: 149
Re: Anyway to speed up worksheet calculation
« Reply #8 on: February 23, 2019, 08:32:04 am »
Thanks for the replies.

So for now the best way is to set boAutoCalc to false and call calcSheet or calcFormulas when necessary.

BTW is there a method to recalculate specific column, row or cell (when the workbook is not in autocalc mode)? 

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Anyway to speed up worksheet calculation
« Reply #9 on: February 23, 2019, 10:13:27 am »
is there a method to recalculate specific column, row or cell (when the workbook is not in autocalc mode)?
No
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 149
Re: Anyway to speed up worksheet calculation
« Reply #10 on: February 24, 2019, 04:03:39 am »
My worksheet has fixed structure (i.e. number of  row/col and formula wont change). Every time when my apps load data from various sources, I know exactly which row/col need to be recalculated.  As such, the calculation can be speed up by restricting the recalculaton on specific range only.

I have added the following method which seems to work for me though I am not sure of the 'side effects' on the whole worksheet (my understanding of the internal coding of fpspread is still very shallow).  Would appreciate your advice.
Code: Pascal  [Select]
  1. procedure TsWorksheet.CalcRange(r1, c1, r2, c2: Cardinal);
  2. var rr, cc: Cardinal;
  3. begin
  4.   if (boIgnoreFormulas in Workbook.Options) then
  5.     exit;
  6.   inc(FWorkbook.FCalculationLock);
  7.   try
  8.     for rr := r1 to r2 do
  9.       for cc := c1 to c2 do
  10.         CalcFormula( FFormulas.FindFormula(rr, cc));
  11.   finally
  12.     dec(FWorkbook.FCalculationLock);
  13.   end;
  14. end;      
« Last Edit: February 24, 2019, 09:08:31 am by kjteng »

wp

  • Hero Member
  • *****
  • Posts: 5652
Re: Anyway to speed up worksheet calculation
« Reply #11 on: February 24, 2019, 12:27:53 pm »
I don't want to add this to fpspreadsheet because it is too risky because it does not consider cells outside the range specified. Even if you say today that you know exactly which cells need to be recalculated will you still know this next year when you have forgotten all the details? But anyway, maybe there should be a way to allow user-defined calculation strategies, of course at user's risk. Therefore, I added an event OnCalcWorkbook to the workbook. If a handler is provided, this code will be executed whenever a workbook is to be recalculated, bypassing the internal fpspreadsheet routines.

I am attaching a little demo in which this new event is applied. As expected, after editing a cell the result cells update immediately now because only two cells are recalculated instead of 5000.

[EDIT]
Let me repeat: this is as the programmer's risk. Using this event means: You know it better, so show that you know it better. If you, for example, edit a formula to address cells outside the row of the formula, the formula cell will not update its result when that out-of-row referenced cell changes. Or, if you plan to add a vertical sum for a colum (=Sum(A1:A2500)) then the sum will not be updated when you edit a cell within the data block.
« Last Edit: February 24, 2019, 01:26:46 pm by wp »
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

kjteng

  • Full Member
  • ***
  • Posts: 149
Re: Anyway to speed up worksheet calculation
« Reply #12 on: February 24, 2019, 01:14:29 pm »
Tqvm