Recent

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

Gizmo

  • Hero Member
  • *****
  • Posts: 796
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 »
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

wp

  • Hero Member
  • *****
  • Posts: 8374
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).
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Zvoni

  • Hero Member
  • *****
  • Posts: 597
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 IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircraft

engkin

  • Hero Member
  • *****
  • Posts: 2818
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: 796
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.
Lazarus 2.0.12 and fpc 3.2.0 - Linux Mint 19 LTS, Windows 10 64 and Mac OSX Big Sur
Useful Page to remember : http://wiki.freepascal.org/Cross_compiling#From_Linux_x64_to_Linux_i386

 

TinyPortal © 2005-2018