[SOLVED] DBGrid.DataSource.DataSet.Last crash when large number of rows in table

[Sieben]

I 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]

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]

@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_errors

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]

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.

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]

Something like this:

Code: Pascal  [Select][+][-]

1. procedure TForm1.ExportToHTML(const AFileName: String);
2. var
3.   SQLQuery: TSQLQuery;
4.   FS: TFileStream;
5. begin
6.   FS := TFileStream.Create(AFileName, fmCreate or \\...);
7.   try
8.     SQLQuery := TSQLQuery.Create(nil);
9.     try
10.       SQLQuery.SQL.Text := 'select F1, F2, F3,...Fn from TableName';
11.       SQLQuery.Database := SQLite3Connection1;
12.       //set other stuff if needed
13.       SQLQuery.UniDirectional := True; //<- this is the important part
14.       SQLQuery.Open;
15.       SQLQuery.First;
16.       while not SQLQuery.EOF do
17.       begin
18.         FS.Write(strTABLEROWStart[1], 4);
19.         FileNameCell := SQLQuery.FieldByName('F1').AsString;
20.          // Write filename to new row
21.          FS.Write(strTABLEDATAStart[1], 4);
22.          FS.Write(FileNameCell[1], Length(FileNameCell));
23.          FS.Write(strTABLEDataEnd[1], 5);
24.          // ... and so on for 5 or 6 other cells of the row
25.         SQLQuery.Next;
26.       end;
27.     finally
28.       SQLQuery.Free;
29.     end;
30.   finally
31.     FS.Free;
32.   end;
33. end;

[Gizmo]

@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]

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]

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_sqlite.pas#L714