[SOLVED] How to speed up writing of DBGrid DataSource data to a File
lazarus / 16-Databases

----------------------------------------------------------------------
Gizmo · 2021-04-13 21:59

Hi

I have a procedure in my program that saves the data that is in DBGrid
cells for 4 columns to a CSV file. The datasource for the DBGrid is
SQLite database.

The save to CSV works well. But, it is not as fast as I would like.
For example, a friend recently used it to save the results of about
500K rows of data. The process took a couple of hours. By contrast,
when he used SQLite Explorer and did the export directly from that
using the SQLite database from my program, it took about 4 seconds.

So I am wanting to try and work out how I might make this process of
writing the data faster? And insights gratefully received.

Here is the existing procedure :

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
TfrmSQLiteDBases.SaveC2FDBToCSV(DBGrid : TDBGrid; Filename :
string);var  linetowrite : ansistring;  n : integer;  CSVFileToWrite :
TFilestreamUTF8; begin  Mainform.StatusBar2.SimpleText := 'Writing
results to file...please wait';  Application.ProcessMessages; 
linetowrite := '';  n := 0;   try    CSVFileToWrite :=
TFileStreamUTF8.Create(Filename, fmCreate);    
DBGrid.DataSource.DataSet.First;     while not
DBGrid.DataSource.DataSet.EOF do    begin      // Include all columns 
    linetowrite := (DBGrid.DataSource.DataSet.Fields[1].Text) + ',' + 
                   (DBGrid.DataSource.DataSet.Fields[2].Text) + ',' + 
                   (DBGrid.DataSource.DataSet.Fields[3].Text) + ',' + 
                   (DBGrid.DataSource.DataSet.Fields[4].Text) +
#13#10;      n := 0;     n := Length(linetowrite);     try     
 CSVFileToWrite.Write(linetowrite[1], n);     finally     
 DBGrid.DataSource.DataSet.Next;     end;    end;  finally   
CSVFileToWrite.Free;  end;  Mainform.StatusBar2.SimpleText := 'DONE'; 
ShowMessage('Grid data now in ' + Filename);end;

----------------------------------------------------------------------
wp · 2021-04-13 22:22

In your code the grid is scrolling and repainting while you are moving
from record to record. Use Dataset.DisableControl before and
.EnableControls after your export to inhibit 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   ... 
DBGrid.DataSource.Dataset.DisableControls;        // <------ ADDED 
try    DBGrid.DataSource.Dataset.First;    while not
DBGrid.DataSource.Dataset.EoF do    begin      ...     
DBGrid.DataSource.Dataset.Next;    end;  finally   
DBGrid.DataSource.Dataset.EnableControls;       // <----- ADDED  end;
Another option would be to use the database exporter components, there
is also a CSV exporter. They come with Lazarus, but I am not sure
whether they are installed by default; they are on palette "Data
Export" (https://wiki.freepascal.org/lazdbexport).

----------------------------------------------------------------------
Zvoni · 2021-04-14 06:37

Why not just use the CLI-Tool within a TProcess?
Quotesqlite3 -header -csv db.sqlite 'select * from tbl1;' > test.csv

EDIT: Researching this, i found out that sqlite doesn't support
"SELECT INTO FileName"?!?!?!?

----------------------------------------------------------------------
engkin · 2021-04-14 13:01

Beside wp's remark you can gain a little speed if you use
TWriteBufStream. You pass CSVFileToWrite to the constructor and use
the buffered one instead.

----------------------------------------------------------------------
Gizmo · 2021-04-14 23:16

Thanks guys for both of these suggestions. I didn't realise about the
repainting issue for sure! Both suggestions look easy to implement
(the buffered stream too, WP) so I will do that and report back
success findings.