Recent

Author Topic: [SOLVED] How to speed up writing of DBGrid DataSource data to a File  (Read 1769 times)

Gizmo

  • Hero Member
  • *****
  • Posts: 831
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.  
« Last Edit: April 15, 2021, 01:15:28 am by Gizmo »

wp

  • Hero Member
  • *****
  • Posts: 11854
Re: How to speed up writing of DBGrid DataSource data to a File
« Reply #1 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).

Zvoni

  • Hero Member
  • *****
  • Posts: 2317
Re: How to speed up writing of DBGrid DataSource data to a File
« Reply #2 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"?!?!?!?
« Last Edit: April 14, 2021, 09:38:40 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

engkin

  • Hero Member
  • *****
  • Posts: 3112
Re: How to speed up writing of DBGrid DataSource data to a File
« Reply #3 on: April 14, 2021, 03:01:26 pm »
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

  • Hero Member
  • *****
  • Posts: 831
Re: [SOLVED] How to speed up writing of DBGrid DataSource data to a File
« Reply #4 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.

 

TinyPortal © 2005-2018