Single column bug?
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
alex80 · 2015-06-08 05:07

Hi guys,

Recently I decided to try if new FPSpreadsheet may work for my new Mac
software project better than proprietary components  and was really
surprised how fast it is (offtopic: I once spent a month to port
xlsReadWrite to Mac and was shocked by a code quality and bugs... only
a few hours spent to replace Int->NativeInt to compile with ppcx64,
for 295eur...).
But I found that when you write single column  - FPSpreadsheet slows
exponentially, and fresh code from svn even works worse than 1.4.3. 

Writing 10 columns and 10k rows numbers takes only ~700ms on my Mac,
or ~770ms with FPS 1.4.3, but writing only single column of 10k rows
takes ~850ms with 1.4.3 and ~2100ms with svn.
More interesting is that writing 20k rows with code from svn takes 10s
(1.4.3 = 6.3s), 30k rows - 26s (17.5s), 40k rows - 49s (33s), 100k
rows was stopped after 5 minutes (but 10 columns with 100k rows
finishes in 7s!).

I've attached chart I build for writing speed, time is shown on Y axis
in ms, number of cells/rows is on X-axis.  BUT, If I write some other
cell, like col=10, row = 10, cellvalue = 0, time will be linear (lines
= fps-hack, fpssvn-hack)!

Wondering if anyone has ideas how to fix the issue.

p.s. Sorry for second question, not sure what is the best place to
ask:  I'm working on patch to FPSpreadsheet, to allow using charts
with xlsx (yet very simple), wondering what would be the best way to
share my code with others,

----------------------------------------------------------------------
alex80 · 2015-06-08 05:34

Forgot to add:
Writing number to last row WriteNumber(0,N=10000,0) does not help,
only writing a number to another column (any row) helps.

I also found that most part of time is taken by cells creation
(probably by TsRowColAVLTree.AddCell, FindCell works pretty fast),
saving to file takes the same time. Still have no clue why AVL tree
works well when there is at least one cell in another column.
Of course VirtualMode helps, but cell creation+writing times are the
same as using extra cell, and won't work easily for writing a list 
"[col,row] = value"

----------------------------------------------------------------------
taazz · 2015-06-08 05:38

interesting test case. Any chance of posting the source code of the
test?

----------------------------------------------------------------------
alex80 · 2015-06-08 05:54

Sure:
Code: [Select]program fpstest;
{$mode objfpc}{$H+}
uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  Classes, SysUtils, Math, dateutils,

           fpsTypes,
           fpspreadsheet,xlsbiff8, xlsxooxml, fpsopendocument;

procedure test_fps;
 var
    MyWorksheet: TsWorksheet;
    MyWorkbook: TsWorkbook;
    i, j, N, ColCount: Cardinal;
    TempFile : String;
    t1, t2, t3 : TDateTime;
    rndn : Double;
  begin
     readln(N);
    TempFile :='/tmp/test'+IntToStr(N)+'.xlsx';
    ColCount := 1;
    // Write out all test values
    t1 := Now;
    MyWorkbook := TsWorkbook.Create;
    try
      MyWorkSheet:=MyWorkBook.AddWorksheet('Test');
      // HACK - uncomment this line to get linear speed
      //MyWorkSheet.WriteNumber(10, 10, 1);
      for i := 0 to N-1 do
           begin
             rndn := random*100;
             for j := 0 to ColCount -1 do
                             MyWorkSheet.WriteNumber(i, j, rndn); //
MyWorkSheet.WriteNumber(MyWorkSheet.AddCell(i,j),rndn);
                 //if (i mod 1000 =0) then write('.');
           end;
      t2 := now;
      MyWorkBook.WriteToFile(TempFile, sfOOXML, true);
    finally
      MyWorkbook.Free;
    end;
    t3 := Now;
    Writeln('Time generation =
'+IntToStr(MilliSecondsBetween(t1,t2))+'ms');
    Writeln('Time Writing =
'+IntToStr(MilliSecondsBetween(t2,t3))+'ms');
  end;


begin
 writeln('N = ?');
 test_fps;
end.

P.s. I've not tested under other platforms, only Mac with stable
Lazarus 1.2.6 + fpc 2.6.4.

----------------------------------------------------------------------
alex80 · 2015-06-08 06:05

Sorry, it looks like I missed TsWorksheet.AddCell(), issue is not
connected with AVLTree - my fault.
Issue is with this line
TsWorksheet.AddCell(ARow, ACol: Cardinal): PCell;             
....
if FLastColIndex = 0 then FLastColIndex := GetLastColIndex(true) ....
So, as long as I have only one column  - FLastColIndex=0, and
GetLastColIndex(true) iterates thru the all cells!

Not sure, if that is good solution and should be submitted to svn, but
it works for me – I've used $FFFFFFFF as "unassigned" value, similarly
to FFirstRowIndex/FFirstColIndex:

constructor TsWorksheet.Create; 
913
FLastRowIndex := $FFFFFFFF;
914
FLastColIndex := $FFFFFFFF;

function TsWorksheet.AddCell(ARow, ACol: Cardinal): PCell;  
1876
if FLastColIndex = $FFFFFFFF then FLastColIndex :=
GetLastColIndex(true)
1878
if FLastRowIndex = $FFFFFFFF  then FLastRowIndex :=
GetLastRowIndex(true)

function TsWorksheet.GetLastColIndex(AForceCalculation: Boolean =
false): Cardinal;
2106
  if AForceCalculation or (FLastColIndex = $FFFFFFFF) then

function TsWorksheet.GetLastRowIndex(AForceCalculation: Boolean =
false): Cardinal;
2209
  if AForceCalculation or (FLastRowIndex = $FFFFFFFF) then

function TsWorksheet.GetCol(ACol: Cardinal): PCol; 
5465
    if FLastColIndex = $FFFFFFFF   

function TsWorksheet.GetRow(ARow: Cardinal): PRow;  
5439
    if FLastRowIndex = $FFFFFFFF then


Now,10k rows in a single A column are written in a ~100ms, and 1M rows
take only 9.9s (Excel 2011 opens this file in 12s!)

----------------------------------------------------------------------
wp · 2015-06-08 10:22

Thanks for this analysis. Your fixes plus a corresponding test case in
fpsspeedtest are included in r4184 (this is tagged as v1.6 which I am
planning to release soon).