Recent

Author Topic: how to Improve speed performance of multiple sheet writes  (Read 5125 times)

witenite

  • New Member
  • *
  • Posts: 41
how to Improve speed performance of multiple sheet writes
« on: April 23, 2017, 11:11:03 am »
Hi,
my program using fpspreadsheet is coming along quite nicely. I have noticed though that when my program loads data from a schematic file into the table (about 5000 cells worth) it takes around 3-4 seconds to do so. This is on an otherwise very fast/powerful computer. My program decodes the data from the CAD file it is reading very quickly, however when I enable the ability to write all the data to the table using something like:

sheet.WriteText(RowNumber, ColNumber, textString);

the process suddenly slows down. Note I decrypt all the data from the table on a row by row basis (IE I write a full row at a time, consisting of about 30 cells). I have enclosed the entire Schematic Read (and table write) procedure inside a tblProperties.BeginUpdate and tblProperties.EndUpdate (where tblProperties is of type TsWorksheetGrid) but this does not appear to help. Any pointers?

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: how to Improve speed performance of multiple sheet writes
« Reply #1 on: April 23, 2017, 03:12:12 pm »
I have enclosed the entire Schematic Read (and table write) procedure inside a tblProperties.BeginUpdate and tblProperties.EndUpdate (where tblProperties is of type TsWorksheetGrid) but this does not appear to help. Any pointers?
There's no attachment... Since this may be a complex topic I'd appreciate if you could condense the issue into a simple compilable project. So: remove everything from the project which is not related to your issue and write dummy data to the worksheet(grid). Of course, the demo project must show the same effect. Pack everything except compiler-generated files into a shared zip which you can upload here.

How many rows and columns (30?) do you write to? I could run my standard test for these conditions.

Did you try to write to a worksheet which is not attached to a grid? I am not 100% sure if Begin/Update of the grid really suppress everything which is broadcast to the grid.

Another reason which slows down the grid is automatic row height calculation because this runs through every cell and checks for all the exotic formatting issues supported such as vertical text or character-by-character formatting ("rich-text").

[EDIT]
The following console project writes dummy strings (the current date/time) into 5000 rows and 30 columns. It takes 0.3 s on my 6-year-old system.

Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. uses
  6.   SysUtils,
  7.   fpstypes, fpspreadsheet;
  8.  
  9. const
  10.   ROW_COUNT = 5000;
  11.   COL_COUNT = 30;
  12.  
  13. var
  14.   book: TsWorkbook;
  15.   sheet: TsWorksheet;
  16.   t: TDateTime;
  17.   r, c: Cardinal;
  18. begin
  19.   t := now;
  20.   book := TsWorkbook.Create;
  21.   try
  22.     sheet := book.AddWorksheet('Table 1');
  23.     for r := 0 to ROW_COUNT - 1 do
  24.       for c := 0 to COL_COUNT - 1 do
  25.         sheet.WriteText(r, c, FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', now));
  26.   finally
  27.     sheet.Free;
  28.   end;
  29.  
  30.   t := now - t;
  31.   WriteLn('Time elapsed: ', t*24*60*60:0:3, ' sec');
  32.  
  33.   ReadLn;
  34.  
  35. end.
« Last Edit: April 23, 2017, 10:13:50 pm by wp »

witenite

  • New Member
  • *
  • Posts: 41
Re: how to Improve speed performance of multiple sheet writes
« Reply #2 on: April 26, 2017, 12:33:26 pm »
Hi WP,
I've been spending the last couple of evenings trying to get a shortened version of my program running, along with your code, but without success. If I run your code in a brand new project on its own, I can get it to compile just fine on its own, and it will execute all the way to the very end. but as soon as it reaches the end of the procedure (line 35 in your example) it crashes and the program never recovers. Granted I have monitored how fast it is, and it is very fast cycling through all the rows/columns, but then it crashes on exit. Note I am running no other code other than what you have given me here. I have also tried adding a TsWorksheetGrid object on my form and commenting out the  " book := TsWorkbook.Create"  (and .free) but it still does not work.

I can't see how it is different from my actual application program, which never crashes, but is very very slow writing all columns/rows. Note if I remove the book := tblProperties reference in my actual program, and then use the " book := TsWorkbook.Create" it again writes all the rows/columns very quickly, but then nothing appears in the grid on my form!! Very frustrating, as I just cannot see why your original program here just crashes on me all the time, yet my vastly more complicated program does not.

How do I make sure my workbook is attached to tblProperties? I see in my TsWorksheetGrid properties, that WorkbookSource is always "tblProperties.internal". there are no other options, so I don't know whether my book connects to the grid properly.

In terms of the automatic row height calculations slowing things down (that you mentioned) how do I avoid that? Note I am using same size font for my entire table.

Should the program example of yours that you sent 23rd April be able to run autonomously, or will it still need TsWorkSheetGrid component on the form? My only option right now seems to be to zip up my entire application and send that to you, as I am unable to get the simple (project) program working without crashing!

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: how to Improve speed performance of multiple sheet writes
« Reply #3 on: April 26, 2017, 12:59:40 pm »
Are you saying that my program is crashing at the end? How does it crash? Or does it just stop responding? In the latter case, did you notice the "ReadLn" at the end? I am on Windows, and if I run a compiled program from Lazarus the console window usually disappears when the program is finished and I can't check the output. Therefore, I routinely add a "ReadLn" at the end of the consol program which makes the program stop and wait for my ENTER key. Maybe it is this what is confusing you.

There's bug, though, in my demo: The "Free" must go to the "book", not to the "sheet":

Code: Pascal  [Select][+][-]
  1.   book := TsWorkbook.Create;
  2.   try
  3.     sheet := book.AddWorksheet('Table 1');
  4.     for r := 0 to ROW_COUNT - 1 do
  5.       for c := 0 to COL_COUNT - 1 do
  6.         sheet.WriteText(r, c, FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', now));
  7.   finally
  8. //    sheet.Free;   <--- wp: this is nonsense. It is always the Workbook which must be destroyed because the workbook owns the worksheets.
  9.     book.Free;
  10.   end;

I think you do not know how my sample code can be used in connection with a worksheetgrid: Just use the code as it is, but remove the book.Free (or sheet.Free in the original post) and the try-finally block because the book will be used by the grid. Call the grid method LoadFromWorkbook(book) to attach an existing workbook to the grid.

In the attachment there is a working example. It takes the code above and attaches the loaded workbook to the worksheet grid. Instead of populating the workbook of the worksheetgrid directly this way has the advantage that the variable "book" does not "know" of the worksheetgrid and therefore its notifications about changes are not responded (every worksheet.WriteText(...) sends a message which is distributed to all visual FPSpreadsheet controls using the worksheet).

[P.S.]
I investigated why BeginUpdate/EndUpdate are not working for you and found out: These are inherited methods of the grid's ancestor and only block unnecessary repaint operations. But the notifications do a lot more, and you must call the worksheetgrid's EnableNotifications/DisableNotifications to inhibit this. Using these two methods around the population of the grid gets you back to the original speed of a workbook not attached to a grid. Look at this code - it is running in 0.3 seconds for 150,000 cells

Code: Pascal  [Select][+][-]
  1. procedure TForm1.FormCreate(Sender: TObject);
  2. const
  3.   ROW_COUNT = 5000;
  4.   COL_COUNT = 30;
  5. var
  6.   t: TDateTime;
  7.   r, c: Cardinal;
  8. begin
  9.   t := now;
  10.   sWorksheetGrid1.Workbook.DisableNotifications;
  11.   try
  12.     for r := 0 to ROW_COUNT - 1 do
  13.       for c := 0 to COL_COUNT - 1 do
  14.         sWorksheetGrid1.Worksheet.WriteText(r, c, FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', now));
  15.   finally
  16.     sWorksheetGrid1.Workbook.EnableNotifications;
  17.   end;
  18.  
  19.   t := now - t;
  20.   Caption := 'Time elapsed: ' + FormatDateTime('s.zzz', t);
  21. end;
« Last Edit: April 26, 2017, 01:23:47 pm by wp »

witenite

  • New Member
  • *
  • Posts: 41
Re: how to Improve speed performance of multiple sheet writes
« Reply #4 on: April 27, 2017, 12:01:50 am »
Morning WP,
apologies for the confusion (I keep doing this around midnight!). When I say crashed, what I should really say is that the program becomes totally unresponsive. I use Ubuntu Linux version 16.04 OS. In Ubuntu when a program becomes unresponsive, the form becomes greyed out (until it comes back to life and works again). So no errors or exceptions raised, it just greys out and stays that way for 30 seconds or more. I have never waited beyond that to see whether it recovers. I was thinking this morning that in all likelihood, the TsWorksheetGrid is probably processing all the rows/columns in the background (not the sheet.WriteText, that part has already executed). The small program I have put together using your code is therefore probably demonstrating exactly what my program is doing, however because there are so many more rows and columns (30x5000 cells) it is going to take about 21 times longer than my program (currently 70 col x 100 rows = 7000 cells) to complete it's job. I will test this tonight by waiting to see whether it takes about 65 seconds to complete and become responsive once more.

PS. I'm running an I7 4.2GHz quad core with 16GB memory etc. so this machine is no slouch.

To clarify, I used your code to generate a GUI app, as opposed to a console program. I have done this because I am trying to use TsWorksheetGrid to present a GUI table. If the above application (and my mentioned test above) works, I'll send the code to you and you can compile for Windows and see whether you are getting similar results.

Note too that I am not using your latest code version (my version is about 6 months old). As I am using Code Typhon IDE I haven't got around to updating to your latest code yet. I am not sure what revision of your code CT have incorporated into their latest library build. I do need to update though, as I know you have fixed the column move method or procedure in recent months.

Your point about using worksheetgrid's EnableNotifications/DisableNotifications instead of BeginUpdate/EndUpdate is certainly another light in the darkness  :) Thank you! Will this alleviate the resource overhead caused by automatic row height calculations and other exotic formatting issues, or should I be presetting specific cell attributes myself prior to cell population? At the moment the only thing I do is change background colour for header rows, and set the font size for all cells.

Appreciate your help and time, thanks WP!
« Last Edit: April 27, 2017, 12:03:53 am by witenite »

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: how to Improve speed performance of multiple sheet writes
« Reply #5 on: April 27, 2017, 12:24:06 am »
Your point about using worksheetgrid's EnableNotifications/DisableNotifications instead of BeginUpdate/EndUpdate is certainly another light in the darkness  :) Thank you! Will this alleviate the resource overhead caused by automatic row height calculations and other exotic formatting issues, or should I be presetting specific cell attributes myself prior to cell population? At the moment the only thing I do is change background colour for header rows, and set the font size for all cells.

EnableNotifications/DisableNotifications does not speed up row height calculation. But I think that your formatting will not cause any slow-down at all.

witenite

  • New Member
  • *
  • Posts: 41
Re: how to Improve speed performance of multiple sheet writes
« Reply #6 on: April 27, 2017, 01:07:58 pm »
Hi WP,
as promised I finally have some semi-working code for you. Basically I have used your code, with a few additions/alterations. I have tried different options as you will see in my .pas file, including using the enable/disable notifications, book creation etc. However the best/only way I was successful was to have the TsWorksheetGrid component on my form, and point the book and sheet to that. This is all in my program as attached here. I reduced the number of cells from your original 5000x30 to 500x30. At 5000 the program goes unresponsive and never recovers. At 500 it takes about 22 seconds (being unresponsive) before it recovers and all cells are successfully filled as expected.

So to recap, this is running on my Ubuntu Linux machine (version 16.04, Intel I7 4.2GHz Quad core, 16G RAM, 512GB M.2 SSD). I'm using Lazarus compiler in Code Typhon and building for Linux in debug mode). Actually, that is something I have not tried yet (but again, too late for this evening) and that is to disable debug mode and run optimized code. Debug normally does not have such a significant impact, but perhaps I should try this anyway. I'm curious to see what speed/performance you achieve on your windows machine with the same code.

wp

  • Hero Member
  • *****
  • Posts: 11830
Re: how to Improve speed performance of multiple sheet writes
« Reply #7 on: April 27, 2017, 01:40:47 pm »
This code fills the grid almost instantly:

Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   t := now;
  4.  
  5.   book := myTestGrid.Workbook;
  6.   sheet := myTestGrid.Worksheet;
  7.   book.DisableNotifications;
  8.   try
  9.     for r := 1 to ROW_COUNT do
  10.       for c := 0 to COL_COUNT - 1 do
  11.         sheet.WriteText(r, c, FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', now));
  12.   finally
  13.     book.EnableNotifications;
  14.   end;
  15.  
  16.   t := now - t;
  17.   sheet.WriteText(0, 0,FormatDateTime('nn:ss.zzz',t));
  18. end;

Your original code contained a line adding a new sheet to the workbook, but it did not activate the new sheet. Because the grid, by default, shows the first sheet of a workbook, the entered data did not show up.

This code, based on your original version, works instantly for me, too:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.  
  4.   t := now;
  5.  
  6.   book := myTestGrid.Workbook;
  7.   sheet := myTestGrid.Worksheet;  // not necessary...
  8.   book.DisableNotifications;
  9.   try
  10.     sheet := book.AddWorksheet('Table_1');  // <--- create a new worksheet
  11.     for r := 0 to ROW_COUNT - 1 do
  12.       for c := 0 to COL_COUNT - 1 do
  13.         sheet.WriteText(r, c, FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', now));
  14.   finally
  15.     book.EnableNotifications;
  16.     book.SelectWorksheet(sheet);  // <-- show new worksheet
  17.   end;
  18.  
  19.   t := now - t;
  20.  
  21.   sheet.WriteText(0, 0,FormatDateTime('nn:ss.zzz',t))  
  22. end;

witenite

  • New Member
  • *
  • Posts: 41
Re: how to Improve speed performance of multiple sheet writes
« Reply #8 on: April 28, 2017, 01:56:33 pm »
WP, once again I am in your debt! both solutions worked for me (I tried them both on my test program, and then finally implemented the first solution with my application under development). Time now to fill 5000x70 cells is less than 400ms. The missing key for me was the book.SelectWorksheet(sheet) in your second code solution. I'm sure others will find this post useful, so once again Thank You Very Much.

 

TinyPortal © 2005-2018