[SOLVED] DBGrid.DataSource.DataSet.Last crash when large number of rows in table lazarus / 16-Databases ---------------------------------------------------------------------- Gizmo · 2021-12-16 20:04 Folks A user reported to me that my program would crash when saving the results of a TDBGrid to HTML. I have a condition that asks for RecordCount. If it is greater than 20K, it uses a filestream to write the rows to a HTML file. That is what it SHOULD do. But, there is a problem. If there are a lot of records - say 400K - the DBGrid itself populates fine. I click my Save to HTML button which triggers another function which writes the data in DBGrid to HTML. It does so either using RAM or a filestream. First thing it does is ask "How many records are there? If it is more than 20K I need to use a filestream". Below is that function. 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 function TfrmSQLiteDBases.CountGridRows(DBGrid : TDBGrid) : integer;var  NoOfRows : integer = Default(integer);begin  result := -1;  DBGrid.DataSource.DataSet.Last;                    // Navigate forward to the last record to ensure all are loaded for RecordCount  DBGrid.DataSource.DataSet.First;                   // Position the dataset on the first record.  NoOfRows := DBGrid.DataSource.DataSet.RecordCount; // Now get the true count  If NoOfRows > -1 then result := NoOfRows;   It works fine if the count is not too high. 20K works OK. 40K works OK. But 400K causes this line to crash : DBGrid.DataSource.DataSet.Last;  Going through it again with the debugger, what happens is the following function is called from DBGrid.DataSource.DataSet.Last when Last is invoked : 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 function TCustomDBGrid.GetDataSource: TDataSource;begin  {$ifdef dbgDBGrid}  DebugLn('%s.GetDataSource', [ClassName]);  {$endif}  Result:= FDataLink.DataSource;  // THIS LINE FIRES AND THEN CAUSES THE CRASH NEVER RETURNING TO THE CODE end;   I noticed using Top (on Linux) that while Result:= FDataLink.DataSource;  was running, the RAM usage went up and up using all 8Gb of the machine when it is faced with such high numbers of rows. Disk activity increases as data starts being swapped at 7Gb usage and then it just bombs out. So my question is : if your DBGrid source has a large volume of data in it, and you must know how many rows are in the table and not just how many are shown on screen (thus the use of Last and First) how can I use DBGrid.DataSource.DataSet.RecordCount, as it seems unable to handle such high numbers (or my implementation is incorrect more likely)? ---------------------------------------------------------------------- balazsszekely · 2021-12-16 20:22 Please try to disable the controls before the count: 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 function TfrmSQLiteDBases.CountGridRows(DBGrid : TDBGrid) : integer;var  NoOfRows : integer = Default(integer);begin  result := -1;  DBGrid.DataSource.DataSet.DisableControls;  try    DBGrid.DataSource.DataSet.Last;                    // Navigate forward to the last record to ensure all are loaded for RecordCount    DBGrid.DataSource.DataSet.First;                   // Position the dataset on the first record.    NoOfRows := DBGrid.DataSource.DataSet.RecordCount; // Now get the true count  finally    DBGrid.DataSource.DataSet.EnableControls;  end;    If NoOfRows > -1 then result := NoOfRows;end; Also if you use a database server a "select count(id) from tablename" is always the fastest way to get the record count. Where id is a primary key. ---------------------------------------------------------------------- Gizmo · 2021-12-17 08:05 Thanks GetMem as ever for your valuable inputs. Sadly, this does not appear to have solved the issue. Exactly the same high CPU and RAM usage as before. I'll have to keep looking. Maybe USE_BIGBLOCKS_MEM_ON_DISK is one answer? https://githubmemory.com/repo/PascalCoinDev/PascalCoin/issues/207 ---------------------------------------------------------------------- Zvoni · 2021-12-17 10:38 Which DBMS? And what's the SQL-Equivalent for the Source of the DBGrid? EDIT: Agree with GetMem: Fire off a SELECT COUNT() against the table BEFORE doing anything, and leave the Last-Method out ---------------------------------------------------------------------- Gizmo · 2021-12-17 10:40 SQLite. I am wondering whether to revert back to what I had before 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   DBGrid.DataSource.Dataset.DisableControls;  DBGrid.DataSource.DataSet.First;  while not DBGrid.DataSource.DataSet.EOF do  begin    inc(NoOfRows, 1);    DBGrid.DataSource.DataSet.Next;  end;  DBGrid.DataSource.Dataset.EnableControls;  UPDATE : this method also doesn’t work for LARGE volumes of data. I did it this way previously but never tested it with hundreds of thousands of rows. Only tens of thousand. I'm going to dig down into the Last procedure and see if I can identify anything... ---------------------------------------------------------------------- GAN · 2021-12-17 19:10 I think you need to disable the TDataSource and work only with the TDataSet. If possible, of course. ---------------------------------------------------------------------- Gizmo · 2021-12-17 22:50 Well I've just spent the last couple of hours playing around with a direct SQL statement instead, as below. The problem is, though, it makes my function "specific" to a certain table (though I guess I could change the function to just accept a 2nd parameter of table name throughout the program) and more critically it adjusts the visible DBGrid to contain the return value, so then the rest of the code after it all breaks. I'd much rather keep it simple and just use RecordCount but the calls it makes to Last are messing it up because, by the looks of how 16Gb RAM get gobbled up in about 10 seconds by 400K rows, I am guessing there is a leak or an issue somewhere with DBGrid.DataSource.DataSet.Last (or more specifically, when Last calls  TCustomDBGrid.GetDataSource);        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     DBGrid.DataSource.Dataset.Close;     TSQLQuery(DBGrid.DataSource.Dataset).SQL.Text := 'SELECT COUNT(*) FROM TBL_FILES';  // Just use SQL itself to get the row count of the table    SQLite3Connection1.Connected := True;    SQLTransaction1.Active := True;    DBGrid.DataSource.Dataset.Open;    NoOfRows := DBGrid.DataSource.Dataset.Fields[0].AsInteger;  // Assign the count value to NoOfRows    If NoOfRows > 0 then result := NoOfRows;  except    on E: EDatabaseError do    begin      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);    end;  end; ---------------------------------------------------------------------- Sieben · 2021-12-17 23:05 Don't abuse the Grids DataSet for this, create another TQuery on the fly instead: 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   with TSQLQuery.Create(nil) do  try    Database := TSQLQuery(DBGrid.DataSource.DataSet).Database;    SQL.Add( //...    // etc...    NoOfRows := Fields[0].AsInteger;    // etc...  finally    Free;  end;  And yes, you can have the SQL accept the table name as a parameter. And I'd write a little function eg GetRowCount(AGrid: TDBGrid; ATableName: string): Integer; for this. ---------------------------------------------------------------------- Gizmo · 2021-12-18 15:05 I like your thinking, but I can't quite get it to work for the line  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 NoOfRows := Fields[0].AsInteger; Out of bounds. ?? If I can get it working, then I'll add the table name parameter throughout for the function. But at the moment I am just trying to get it to work with one table, TBL_FILES ---------------------------------------------------------------------- Sieben · 2021-12-18 15:31 Quick and dirty, works for me: 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 function GetRowCount(AGrid: TDBGrid; ATableName: string): Integer;begin  with TSQLQuery.Create(nil) do  try    Database := TSQLQuery(AGrid.DataSource.DataSet).Database;    SQL.Text := ('SELECT COUNT(*) FROM '+ATableName);    Open;    Result := Fields[0].AsInteger;    Close;  finally    Free;  end;end;  As far as I can see you didn't open the Query but only the Database (which I assume is already connected). ---------------------------------------------------------------------- Gizmo · 2021-12-18 15:51 Yes sir - that does indeed now work! Thanks a lot for your help here. I'll test it on my big data samples and see if it works better than my calls to Last etc. If it does, I'll go through and change my function calls accordingly. I'll update ASAP and mark as solved if all is well. ---------------------------------------------------------------------- af0815 · 2021-12-18 17:34 The problem with recordset is well known since Delphi and the BDE. It can working, but must not. For some Desktopdatabases  it works in the past, but not for SQL-based. If you work with SQL, the always working method is 'count', because you get often only the fetched rows at recordcount. This works only if the driver fetch all. If you use fist/next on a small Table it can be working, but nobody can say it is rigth. Count have also a caveeat in a multuser enviroment. If the table is changed, the result is not exact for the next operation. It depend on the transaction and the defaults of the DB. Keep this in mind if you work in a multiuser enviroment. ---------------------------------------------------------------------- Gizmo · 2021-12-20 10:42 OK, so, the whole count issue seems to be resolved. But, the problem I have is that First, Last, DisableControls and EnableControls are used again further down my main calling function. Example is as follows : 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 try DBGrid. DataSource.DataSet.DisableControls; DBGrid.DataSource.DataSet.First; w hile not DBGrid.DataSource.DataSet.EOF do   begin     // Output row to HTML filestream     fs.Write(strTABLEROWStart[1], 4);     FileNameCell := DBGrid.DataSource.DataSet.Fields[1].AsString;        // Write filename to new row     fs.Write(strTABLEDATAStart[1], 4);     fs.Write(FileNameCell[1], Length(FileNameCell));     fs.Write(strTABLEDataEnd[1], 5);     // ... and so on for 5 or 6 other cells of the row    DBGrid.DataSource.DataSet.Next;           end;  DBGrid.DataSource.DataSet.EnableControls;     finally  fs.free;     end;  So, I hit the same problem again (I cant be the first person to throw a few hundred thousand rows into a DBGrid though surely - havent other people come across this?). What I observed was it chugged along for a while, created a 26Mb HTML file, and then eventually crashed. So I'm not sure how far it got - perhaps it was the final call to EnAbleControls that killed it. Or something - very hard to debug with 407K rows! My question is : given I now have the number of rows stored in NoOfRowsInGrid from the new and improved CountGridRows(DBGrid, 'TBL_NAME'); how can I achieve the same iterative method but without using First, Next, etc? 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 for i := 0 to NoOfRowsInGrid -1 dobegin  Go to Row i  Output row to HTML Filestream  Go to next rowend; ---------------------------------------------------------------------- balazsszekely · 2021-12-20 11:13 @Gizmo I'm gonna tell you a secret , use a separate query for export(like you did with the row counting), set UniDirectional property to true and you are good to go. 400k is relatively small number. ---------------------------------------------------------------------- Zvoni · 2021-12-20 11:16 Quote from: Gizmo on December 20, 2021, 11:42:16 amMy question is : given I now have the number of rows stored in NoOfRowsInGrid from the new and improved CountGridRows(DBGrid, 'TBL_NAME'); how can I achieve the same iterative method but without using First, Next, etc? Don't use the Dataset of the DBGrid. Create a temp. copy of it, and use that one. (Copy, not Reference!) If i'm reading it correctly, the moment you enable the DBGrid again, you try to load all 400K rows into the DBGrid, which is plain .... errr.... [insert word] ---------------------------------------------------------------------- Sieben · 2021-12-20 11:50 Quote from: Gizmo on December 20, 2021, 11:42:16 amI cant be the first person to throw a few hundred thousand rows into a DBGrid though surely - havent other people come across this? Yes they have, and usually they're told that it's a bad idea to throw that number of records into a DBGrid (nobody really views 407k rows of data)... listen to the advice of GetMem and Zvoni - use an extra query for export, and retrieve only those fields you really need for that. Regarding your question: no. A while not eof-loop is the way to go and should be no problem once you get rid of the grid... ---------------------------------------------------------------------- Gizmo · 2021-12-20 12:55 Sieben - just spotted your reply. Thanks. "they're told that it's a bad idea to throw that number of records into a DBGrid (nobody really views 407k rows of data)" - this is increasingly making sense to me now. I assumed, wrongly, that the DBGrid was a useful GUI interface for rendering database content. In my case, they have been part of the tool for some years and it works very well for many users. Problem is it is all now rather part of the tool - you're right that folks dont view that many individual records, but they do expect to see "a table of data" and to be then able to export it\save it. I think it would be better to have it show ON SCREEN only X number of records, and then every time they scroll or move, it loads more. But...I don't know - I need to think of a better way to achive all this. GetMem - sound advice for sure. I'm trying to work out how I would do that, for this larger task, appreciating I can see how it worked just for getting a row count, but itterating through every row might be more challenging for me. Zvoni - "the moment you enable the DBGrid again, you try to load all 400K rows into the DBGrid, which is plain .... errr.... [insert word]" - I think by this you mean the "EnableControls" call? So I'm not massively good with the whole SQL thing to be honest, but from what I read, you have to (should) disablecontrols while you're doing lots with the data in the grid, so it doesnt refresh and try and updated itself. But you then have to enablecontrols afterwards (in a finally I read just now). So if you're saying that by doing that I am "loading all 400K rows into the DBGrid" AGAIN, then that is not what I intended to do. I assumed, perhaps wrongly, that when you disablecontrols, you freeze the grid, and when you enablecontrols, you unfreeze it. Not reload it all. But maybe that does reload it all. COnfusing. ---------------------------------------------------------------------- balazsszekely · 2021-12-20 13:05 @Gizmo The main problems is that Unidirectional property is false. Please see the following page for more details, especially the Out of Memory section: https://wiki.freepascal.org/Working_With_TSQLQuery#Out_of_memory_error s I assume that the grid is linked to a TSQLQuery(TDBGrid->TDataSource->TSQLQuery). All you have to do is to set SQLQuery's Unidirectional property to true. Now the count/export should work flawlessly. PS: Of course is always better to use a separate query to get the row count via "select count(*) from Table", and a separate query for the export. ---------------------------------------------------------------------- Zvoni · 2021-12-20 13:17 Quote from: Gizmo on December 20, 2021, 01:55:24 pmZvoni - "the moment you enable the DBGrid again, you try to load all 400K rows into the DBGrid, which is plain .... errr.... [insert word]" - I think by this you mean the "EnableControls" call? So I'm not massively good with the whole SQL thing to be honest, but from what I read, you have to (should) disablecontrols while you're doing lots with the data in the grid, so it doesnt refresh and try and updated itself. But you then have to enablecontrols afterwards (in a finally I read just now). So if you're saying that by doing that I am "loading all 400K rows into the DBGrid" AGAIN, then that is not what I intended to do. I assumed, perhaps wrongly, that when you disablecontrols, you freeze the grid, and when you enablecontrols, you unfreeze it. Not reload it all. But maybe that does reload it all. COnfusing. What i meant is: You DisableControls of the DBGrid, you move to the First Record, you iterate through all Records until the Last one, you EnableControls the DBGrid again, but the DataSource/DataSet contains now all 400K Records and is currently pointing to the last one, which DBGrid tries to load into its Grid and show them. IIRC, the Disable/EnableControls is mainly used to avoid flickering of the Grid while it loads Records EDIT: Just noticed something in your code in Post #12: Move the DisableControls before the "try", and move EnableControls into the "Finally"-Block (as recommended by documentation) ---------------------------------------------------------------------- balazsszekely · 2021-12-20 13:30 Something like this: 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 TForm1.ExportToHTML(const AFileName: String);var  SQLQuery: TSQLQuery;  FS: TFileStream;begin  FS := TFileStream.Create(AFileName, fmCreate or \\...);  try    SQLQuery := TSQLQuery.Create(nil);    try      SQLQuery.SQL.Text := 'select F1, F2, F3,...Fn from TableName';    SQLQuery.Database := SQLite3Connection1;      //set other stuff if needed      SQLQuery.UniDirectional := True; //<- this is the important part      SQLQuery.Open;      SQLQuery.First;      while not SQLQuery.EOF do      begin        FS.Write(strTABLEROWStart[1], 4);      FileNameCell := SQLQuery.FieldByName('F1').AsString;         // Write filename to new row         FS.Write(strTABLEDATAStart[1], 4);       FS.Write(FileNameCell[1], Length(FileNameCell));         FS.Write(strTABLEDataEnd[1], 5);         // ... and so on for 5 or 6 other cells of the row        SQLQuery.Next;      end;    finally    SQLQuery.Free;    end;  finally    FS.Free;  end;end; ---------------------------------------------------------------------- Gizmo · 2021-12-20 18:22 @GetMem - you are a legend in every way! It always amazes me how you just throw something out in seconds that always seems to work straight away! Yes, that has worked perfectly. I tested it on my set of 407,000 rows, and it wrote it to a whopping 56Mb HTML file in about 10 seconds. It didn't use more than 50Mb RAM throughout throughout. Firefox, on the other hand, not unexpectedly, struggled to open it and used a far whack of resources to do so - but it did do so after several minutes. So now I need to apply the same thinking to my other grids which also have a "Save to HTML" option, which hopefully I can now do based on this. Just for reference, before trying your SQLQuery example, I did try just setting the UniDirectional setting in the Object Inspector for the DBGrid. That also did kind of work, in as much as I could get a saved HTML output with no crashing, but the problem was, as per the documentation, it then rendered the Grid itself fairly useless, in as much as soon as I tried to scroll the grid afterwards, the program would crash as a result of the non UniDirectional behaviour. Not unexpected. Anyway, your SQLQuery method is much better To the others - thanks to you also for the education piece. This bug, and this thread, has helped me learn quite a bit. Still more to go of course. SQL is quite a beast in itself. I will mark this as SOLVED for now, because I think it is now solved. ---------------------------------------------------------------------- Zvoni · 2021-12-21 13:32 Gizmo, just thought about another approach: Have you thought about exporting your 400K of Rows into a CSV? Since it's SQLite probably TCSVExporter from lazdbexport (since SQLite doesn't support SELECT INTO OUTFILE)? https://wiki.lazarus.freepascal.org/lazdbexport https://www.freepascal.org/daily/packages/fcl- db/fpcsvexport/tcsvexporter.html That way it would be simple String-processing to create your HTML from the CSV, and you wouldn't have to move Records from your Dataset around No idea about performance ---------------------------------------------------------------------- Gizmo · 2021-12-21 16:27 Well interestingly enough, the tool does have two options already. Save to CSV, or Save to HTML. Most users use CSV anyway. HTML is less commonly used. But of course the first time someone used it anger for nearly half a million files, and who took the time to report it, this issue arose. So I could do as you suggest - export to CSV, then create HTML from the CSV. But having now got this great solution from GetMem and others, I'll stick with this. And on that note, anyone seeking to have a look at the finished article (like GetMem or the other members that have contributed) can do so over on Github here which shows it all in context : https://github.com/tedsmith/quickhash/blob/v3.3.1/dbases_sql ite.pas#L714