memory consumption lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Hansvb · 2016-07-08 20:11 With FPspread sheet i write datasets to different worksheets. I notice that when there a lot of rows and columns to export, the memory consumption is verry high. I can not use Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i workbook.OnWriteCellData := @WriteCellDataHandler;because i write to different worksheets. Is there a way to lower the memory consumption? Or am i doing something wrong? When i use Ole Excel automation, there is almost no memory use at all. But then the export is verry verry slow. Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TExport_To_File.SaveAs_FPExcelFile_Complete_lvwoz_scan(  Adbgrid: TDBGrid; ASheetName: widestring);var  sheet: TsWorksheet;  szLeesuit : String;  f, R: Integer;begin  screen.Cursor:=crHourglass;   if (Active_DBGrid.DataSource = nil) or     (Active_DBGrid.DataSource.DataSet.IsEmpty = True) then    begin      Frm_main.Logging.WriteToLog('INFORMATIE | De dataset is leeg, er is geen export naar Excel aangemaakt.');      //hier eventueel een leeg werkblad laten aanmaken.    end  else    begin //De export naar Excel uitvoeren      Frm_Main.Logging.WriteToLog('INFORMATIE | Start met het opslaan van ' + ASheetName);       //Werkblad aanmaken      sheet := MyWorkbook.AddWorksheet(Asheetname);      szLeesuit :='';                  //kopteksten      for f := 0 to  Adbgrid.DataSource.DataSet.FieldCount -1 do        begin          sheet.WriteText(0, f, widestring(Adbgrid.DataSource.DataSet.Fields[f].FieldName));        end;       //de data naar Excel schrijven      with Adbgrid.DataSource.DataSet do        begin          Adbgrid.DataSource.DataSet.DisableControls;          First;          for R := 0 to Adbgrid.DataSource.DataSet.RecordCount-1 do            begin              for F := 0 to Adbgrid.DataSource.DataSet.FieldCount-1 do                begin                szLeesuit :='';                  if Adbgrid.DataSource.DataSet.Fields[F].AsString <> '' then                  szLeesuit :=  Adbgrid.DataSource.DataSet.Fields[F].AsString;                sheet.WriteText(R+1, F+0, widestring(szLeesuit) );              end;              next;            end;          First;   //weer terug naar het eerste record.          Adbgrid.DataSource.DataSet.EnableControls;           //tussentijds opslaan (dus na elke query)          try            if SaveDialog_FileName <> '' then              MyWorkbook.WriteToFile(widestring(SaveDialog_FileName));          except            on E: Exception do              begin              Frm_main.Logging.WriteToLog('FOUT      | Het tussentijds opslaan van het Fpspreadsheet Workbook is mislukt.');                Frm_main.Logging.WriteToLog('FOUT      | Een onverwachte uitzondering heeft plaats gevonden:');                Frm_main.Logging.WriteToLog('FOUT      | ' + E.Message);              Frm_main.Logging.WriteToLogAndFlush('FOUT      | ');              end;          end;        end;    end;     screen.Cursor:=crdefault;end; ---------------------------------------------------------------------- wp · 2016-07-08 21:21 Memory consumption of fpspreadsheet is high because the entire workbook is held in memory. A lot of optimization has been applied in the past to reduce the memory load per cell, I still see a few places where a few bytes can gained, but in total this will be a marginal effect. Excel, on the other hand, applies some tricky disk swapping mechanism which is not available here. Of course somebody can write a patch for it... To overcome the memory limitation virtual mode was introduced. This allows to write cells "on the fly" without keeping them in memory, and this is what you mean with the OnWriteCellData event. Unfortunately I made a design error and assigned the virtual mode to the workbook, not to the worksheet. Therefore, you cannot handle worksheets differently in virtual mode. At the moment I am busy with updating the Turbo Power Visual PlanIt components. When I am done the virtual mode design flaw will be fixed. Most probably, however, it will break existing code. I am not the expert with OLE automation. When I applied it in some old Delphi project I soon got disappointed by this low-performance programming model and the interference with already running Excel instances. I did notice that writing an Excel file cell by cell (as you do) is extremely slow, but I found somewhere a note that speed increases considerably if cell ranges are used and the cells are addressed as elements in such arrays - sorry, I don't know any more where I had picked this up. Since you deal with databases (I guess), did you consider using tools provided by your database engine? I did a lot of work with Delphi and Microsoft's ADO these days; there was an extension of the SELECT query which could be used to write to Excel (and other formats). I am posting here the code that I had used. Note that this is Delphi code, it will not work with Lazarus, but maybe you can get similar things with your database engine, or with ODBC, or with ZEOS, I don't know: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i type  TExportFormat = (    efMDB,    efExcel3, efExcel4, efExcel5, efExcel8,    efLotusWK1, efLotusWK3, efLotusWK4,  // Lotus WK4 is not working    efDBase3, efDBase4, efDBase5,    efParadox3, efParadox4, efParadox5,    efText,    efHTML,    efXML  ); const  SQL_Mask = 'SELECT %s INTO [%s] IN "%s" %s FROM %s';  //                 |        |       |   |        |  //               fields     |       |   |      |  //                     dest table   |   |        |  //                      dest database |        |  //                              database format  |  //                                         source dataset  DBCodes : array[TExportFormat] of string = (    '',    '"Excel 3.0;"', '"Excel 4.0;"', '"Excel 5.0;"', '"Excel 8.0;"',    '"Lotus WK1;"', '"Lotus WK3;"', '"Lotus WK4;"',    '"dBASE III;"', '"dBASE IV;"', '"dBASE 5.0;"',    '"Paradox 3.x;"', '"Paradox 4.x;"', '"Paradox 5.x;"',    '"Text;"',    '"HTML Export;"',    ''  ); function ADOExportQuery(AConnection:TADOConnection;   ASourceSQL,ADestFile:string;  AFormat:TExportFormat; ADestTableName:string) : integer;const  WhiteSpace = [' ', #13, #10, #9];  Ls = 6;  // = Length('SELECT');  Lf = 4;  // = Length('FROM');var  SQL : string;  p, p1, p2, p3 : integer;  done : boolean;  sql1, sql2, _sql : string;begin  result := -1;  if (ASourceSQL='') or (ADestFile='') or (AConnection=nil) then    raise Exception.Create('Incomplete parameters.');   if AFormat=efXML then begin    result := ADOExportToXML(AConnection, ASourceSQL, ADestFile);  end else begin    while (ASourceSQL<>'') and (ASourceSQL[1] in WhiteSpace) do       System.Delete(ASourceSQL, 1, 1);    _sql := Uppercase(ASourceSQL) + ' ';    p1 := Ls+1;    while (_sql<>'') and (_sql[p1] in WhiteSpace) do inc(p1);    p2 := 0;    done := false;    while not done do begin      p := pos('FROM', _sql);      if p<>0 then begin        done := (_sql[p-1] in WhiteSpace) and (_sql[p+Lf+1] in WhiteSpace);        if not done then begin          p2 := p2 + p;          _sql := copy(_sql, p+Lf, Length(_sql));        end;      end else        done := true;    end;    p3 := p2 + Lf;    while (p2>0) and (ASourceSQL[p2] in WhiteSpace)    do dec(p2);    while (p3 not working !      efExcel3,      efExcel4 :         begin           if FileExists(ADestFile) then DeleteFile(ADestFile);           SQL := Format(SQL_Mask,             [AFieldList, fnam, ADestFile, DBCodes[AFormat], ASourceTable]);         end;      efExcel5,      efExcel8 :        begin         if FileExists(ADestFile) then DeleteFile(ADestFile);         SQL := Format(SQL_Mask,           [AFieldList, ADestTableName, ADestFile, DBCodes[AFormat],           ASourceTable]);        end;      efMDB :        SQL := Format(SQL_Mask,          [AFieldList, ADestTableName, ADestFile, DBCodes[AFormat],            ASourceTable]);      else        raise Exception.Create('ExportTable: Fileformat not supported.');    end;  with AConnection do begin      wasConn := Connected;      Connected := true;      Execute(SQL, result);      if not wasConn then Connected := false;    end;  end;end; ---------------------------------------------------------------------- Thaddy · 2016-07-09 09:06 Quote from: wp on July 08, 2016, 11:21:43 pm. Excel, on the other hand, applies some tricky disk swapping mechanism which is not available here. Well, actually the Excel behavior  is available by default when you use the standard windows functions for memory allocation / de- allocation in your memory manager. That would be VirtualAlloc and the likes with proper flags to allow swapping to disk instead of forcing into actual memory hardware. (I told many times the FPC memory manager sucks, here's another example) In Delphi the memory manager on top of the OS code allows this and that should work. Holding just the sheet in memory, though, would be an improvement either way. I concur. But there are no 'tricks'  involved other than OS memory management behavior. There are alternative memory managers available to solve the issue, including one I wrote myself for fun. Even very simple ones that just use the heap calls under windows. The offset is that they - mostly - consume more physical memory than FPC's default memory manager. But they will swap automatically. Note this write up is highly Windows specific! Warranted by the appearance of Excel in this story ---------------------------------------------------------------------- wp · 2016-07-14 19:49 @Hansvb: The new fpspreadsheet version on trunk has a modified virtual mode. The properties VirtualColCount and VirtualRowCount, as well as the event OnWriteCellData, have been moved from the workbook to the worksheet. Therefore, is is possible now to treat worksheets individually when they are written in virtual mode. As already noted above, this modification breaks existing virtual mode code. But it's easy to fix, the compiler will tell you what's wrong: old code                   ---> new code -------------------------------------------------------------- MyWorkbook.VirtualRowCount ---> MyWorksheet.VirtualRowCount MyWorkbook.VirtualColCount ---> MyWorksheet.VirtualColCount MyWorkbook.OnWriteCellData ---> MyWorksheet.OnWriteCellData And, the first parameter of the OnWriteCellData event handler is a TsWorksheet now, no longer a TObject. [EDIT] When you apply this to your project be sure to set workbook option boBufStream, otherwise all cells will be written to a temporary memory stream which will overflow memory in the same way as non-virtual mode, just a bit delayed. ---------------------------------------------------------------------- Hansvb · 2016-07-31 07:25 Thanks, i will try this ---------------------------------------------------------------------- Abrax · 2016-12-12 13:04 Dear Experts, I'm working with a really huge .xlsx table (93.880 rows x 37 columns). The System is win10 64bit, 8GB RAM. During the loading of the table my program consumes 5-6GB memory, and the loading takes 20sec. After it is loaded, the memory consumption falls to 700MB. Running the .exe on 4GB RAM (= user PC) the loading of the .xlsx takes 15 minutes, because of the swapping. I tried every option (boBufStream, boFileStream) and also tried the demo application (db_export_import) with a 100.000 x 37 table with boVirtualMode but the effect was the same: high peek memory consuption and a falling back. I used the debugger to find the cause, and I found the procedure call (parser.Parse(src, ADoc);) in the fpsxmlecommon.pas, which eats up the memory, but I got stuck here, because the step-by-step debugger did not jump in to the laz2_XMLRead. Any idea to decrease the hunger of the parser? ---------------------------------------------------------------------- wp · 2016-12-12 18:25 I can confirm this issue, but I don't have a solution, yet. To debug into laz2_XMLRead you can set the -option "-gw2" or "-gw" in "Additions and overrides" of the project options (see screenshot). This recompiles your project and all required packages with Dwarf(2) debug info. ---------------------------------------------------------------------- Abrax · 2016-12-13 12:44 Thanks to the advice. The guilty is the procedure TXMLReader.ParseContent; (laz2_XMLRead) , it eats the memory. The stack is OK, the heap should be checked, but the Heaptrc freeze the IDE and give no result. I suppose the procedure allocate new piece of memory again and again at every call and the System has not enough time (or thread) to the garbage collection. Unfortunately this examination is far beyond the scope of the project. The basic concept is wrong to work with such big spreadsheet. This is a fact. The MS Excel is dying in an other way when I try to manipulate this .xlsx. As last solution I try to quarry the data from the .xlsx by hand and write to a temporary .csv like file or TSringList at runtime. Actually I only have to find and read records from the spreadsheet, and TStringList quite fast. ---------------------------------------------------------------------- KennethH · 2024-07-30 14:42 Old topic I know, but I just went down this rabbit hole, so maybe I can help shed light on it through my pain. Yes, TXMLReader.ParseContent; (laz2_XMLRead) is the culprit. But I decided to dig deeper and understand why. It turns out that the nodes of the XML are class-based. A high pain-point is: TXMLReader.ParseAttribute  and CreateElementBuf() (laz2_XMLRead). This will turn every node in the XML into a class. This class has FList for its children and various other properties. For example, TDOMNode_WithChildren.InstanceSize is 80 (FPC 64-bit Windows). This adds up quickly. My sample Excel file rendered into an XML with 9 million nodes.  Each of these caused the creation of a DomElement and a DomAttribute, so ~160 bytes per node. For a file of about for a file of about 20MB, we see ~1.5GB memory used  just on these two classes alone. This didn't even count the InstanceSize of their FLists, and whatever other helper objects they make. imo this cannot be solved with the native Lazarus XML-reading procedures. I did comment out units laz2_xmlread, laz2_DOM and the resulting list of errors looks relatively contained. Perhaps substituting a "dumb" XML reader is a go-forward option. ---------------------------------------------------------------------- PascalDragon · 2024-07-31 20:39 Quote from: Thaddy on July 09, 2016, 11:06:03 amQuote from: wp on July 08, 2016, 11:21:43 pm. Excel, on the other hand, applies some tricky disk swapping mechanism which is not available here. Well, actually the Excel behavior  is available by default when you use the standard windows functions for memory allocation / de- allocation in your memory manager. That would be VirtualAlloc and the likes with proper flags to allow swapping to disk instead of forcing into actual memory hardware. There are no flags for VirtualAlloc that can force a page to stay in the physical memory. If one wants memory to stay in physical memory then one has to use VirtualLock on that memory. FPC's default memory manager doesn't use that by itself, but only HeapAlloc, thus any page can be paged. Quote from: Thaddy on July 09, 2016, 11:06:03 am(I told many times the FPC memory manager sucks, here's another example) The memory manager works good enough for the majority of the applications out there. Other with specific needs can replace the memory manager if desired. Quote from: Thaddy on July 09, 2016, 11:06:03 amIn Delphi the memory manager on top of the OS code allows this and that should work. Code, please, or it didn't happen. ---------------------------------------------------------------------- wp · 2024-08-01 10:01 Quote from: KennethH on July 30, 2024, 04:42:09 pmPerhaps substituting a "dumb" XML reader is a go-forward option. Then go ahead and rewrite the xlsx reader in your way (I won't do it, at least not in the near future) - fpspreadsheet is open to allow this (https://wiki.lazarus.freepascal.org/FPSpreadsheet#Adding_new_file_for mats, you can register your xlsx reader as user format). ---------------------------------------------------------------------- marcov · 2024-08-01 11:30 Quote from: KennethH on July 30, 2024, 04:42:09 pmI did comment out units laz2_xmlread, laz2_DOM and the resulting list of errors looks relatively contained. Perhaps substituting a "dumb" XML reader is a go-forward option. (There is sax_xml but it is quite skeletal. That is an fcl-xml unit that didn't get the laz_/laz2_ treatment ) ---------------------------------------------------------------------- BeniBela · 2024-08-01 21:50 Quote from: KennethH on July 30, 2024, 04:42:09 pm A high pain-point is: TXMLReader.ParseAttribute  and CreateElementBuf() (laz2_XMLRead). This will turn every node in the XML into a class. This class has FList for its children and various other properties. For example, TDOMNode_WithChildren.InstanceSize is 80 (FPC 64-bit Windows). This adds up quickly. My sample Excel file rendered into an XML with 9 million nodes.  Each of these caused the creation of a DomElement and a DomAttribute, so ~160 bytes per node. For a file of about for a file of about 20MB, we see ~1.5GB memory used  just on these two classes alone. This didn't even count the InstanceSize of their FLists, and whatever other helper objects they make. Classes were a bad idea Someone made an XML library for Delphi which just uses 8 bytes per node: https://blog.grijjy.com/2020/10/07/an-xml-dom-with-just-8-bytes- per-node/ You would think it is obviously impossible to store seven 8-byte pointers in just 8-bytes, but with bit tricks, he managed to do it