Lazarus

Programming => Databases => Topic started by: Gizmo on April 13, 2021, 11:59:54 pm

Title: [SOLVED] How to speed up writing of DBGrid DataSource data to a File
Post by: Gizmo on April 13, 2021, 11:59:54 pm

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][+][-]

1. procedure TfrmSQLiteDBases.SaveC2FDBToCSV(DBGrid : TDBGrid; Filename : string);
2. var
3.   linetowrite : ansistring;
4.   n : integer;
5.   CSVFileToWrite : TFilestreamUTF8;
6.  
7. begin
8.   Mainform.StatusBar2.SimpleText := 'Writing results to file...please wait';
9.   Application.ProcessMessages;
10.   linetowrite := '';
11.   n := 0;
12.  
13.   try
14.     CSVFileToWrite := TFileStreamUTF8.Create(Filename, fmCreate);
15.  
16.     DBGrid.DataSource.DataSet.First;
17.  
18.     while not DBGrid.DataSource.DataSet.EOF do
19.     begin
20.       // Include all columns
21.       linetowrite := (DBGrid.DataSource.DataSet.Fields[1].Text) + ',' +
22.                      (DBGrid.DataSource.DataSet.Fields[2].Text) + ',' +
23.                      (DBGrid.DataSource.DataSet.Fields[3].Text) + ',' +
24.                      (DBGrid.DataSource.DataSet.Fields[4].Text) + #13#10;
25.  
26.      n := 0;
27.      n := Length(linetowrite);
28.      try
29.        CSVFileToWrite.Write(linetowrite[1], n);
30.      finally
31.        DBGrid.DataSource.DataSet.Next;
32.      end;
33.     end;
34.   finally
35.     CSVFileToWrite.Free;
36.   end;
37.   Mainform.StatusBar2.SimpleText := 'DONE';
38.   ShowMessage('Grid data now in ' + Filename);
39. end;
40.  

Title: Re: How to speed up writing of DBGrid DataSource data to a File
Post by: wp on April 14, 2021, 12:22:25 am

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][+][-]

1.   ...
2.   DBGrid.DataSource.Dataset.DisableControls;        // <------ ADDED
3.   try
4.     DBGrid.DataSource.Dataset.First;
5.     while not DBGrid.DataSource.Dataset.EoF do
6.     begin
7.       ...
8.       DBGrid.DataSource.Dataset.Next;
9.     end;
10.   finally
11.     DBGrid.DataSource.Dataset.EnableControls;       // <----- ADDED
12.   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).

Title: Re: How to speed up writing of DBGrid DataSource data to a File
Post by: Zvoni on April 14, 2021, 08:37:46 am

Why not just use the CLI-Tool within a TProcess?

Quote

sqlite3 -header -csv db.sqlite 'select * from tbl1;' > test.csv

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

Title: Re: How to speed up writing of DBGrid DataSource data to a File
Post by: engkin on April 14, 2021, 03:01:26 pm

Beside wp's remark you can gain a little speed if you use TWriteBufStream (https://www.freepascal.org/docs-html/current/fcl/bufstream/twritebufstream.html). You pass CSVFileToWrite to the constructor and use the buffered one instead.

Title: Re: [SOLVED] How to speed up writing of DBGrid DataSource data to a File
Post by: Gizmo on April 15, 2021, 01:16:31 am

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.