[Lazarus] Writing >1000 TBufDataset records to file is extremely slow

[Werner Pamler]

Am 27.03.2017 um 00:53 schrieb Howard Page-Clark via Lazarus:
> I can get small performance increases by
> - avoiding FieldByName() calls and using AppendRecord
No, at least not for the issue I am referring to. Like in the answer to 
Marc's comment: This happens while the table is populated, but the delay 
occurs when the populated table is written to stream/file.

> - using SaveToFile and avoiding an intermediate memory stream
Has no noticable effect. 1000 records really is not much, and flushing 
the memory stream to disk occurs without any delay (see my code haveing 
measurement points before and after writing the memory stream and after 
writing to file. In fact, when I noticed this effect I did not have any 
explicit writing code at all, I noticed an excessive delay while the 
dataset is closed -- this is when the BufDataset is saved automatically.

> - increasing the value of PacketRecords
Not knowing what this is I increased the value in multiples of 10 from 1 
to 1E9 and don't see any effect within the usual scatter.

> Clearly either the insertion algorithm should be improved, or the 
> buffering, or the way the buffered records are written to disk. Maybe 
> all three areas of TBufDataset can be optimised for better performance.
Thanks. When I have time I'll write a bug report. The current 
TBufDataset is usable only as a pure in-memory table which is never 
written to file. BTW, in the attached modified demo code the TBufDataset 
can be replaced by a TMemDataset (define "USE_MEM_DATASET"), and this 
one is written instantly.

------------------ snip -------------------

program project1;

{$mode objfpc}{$H+}

{$DEFINE USE_MEM_DATASET}

uses
   SysUtils, classes, db, memds, bufdataset;

const
   TABLENAME = 'people'; //name for the database table, extension will 
be added
   DATADIR = 'data'; //subdirectory where database is stored

const
   NUM_RECORDS = 5000;
   SECONDS_PER_DAY = 24 * 60 * 60;

var
   FExportDataset: TDataset;

procedure CreateDatabase;
var
   i: Integer;
   fn: String;
   stream: TMemoryStream;
   t: TDateTime;
begin
   ForceDirectories(DATADIR);

   fn := DATADIR + DirectorySeparator + TABLENAME + '.db';
   DeleteFile(fn);

   {$IFDEF USE_MEM_DATASET}
   FExportDataset := TMemDataset.Create(nil);
   {$ELSE}
   FExportDataset := TBufDataset.Create(nil);
   {$ENDIF}

   FExportDataset.FieldDefs.Add('Last name', ftString, 15);
   FExportDataset.FieldDefs.Add('First name', ftString, 10);
   FExportDataset.FieldDefs.Add('City', ftString, 15);
   FExportDataset.FieldDefs.Add('Birthday', ftDate);
   FExportDataset.FieldDefs.Add('Salary', ftCurrency);
   FExportDataset.FieldDefs.Add('Work begin', ftDateTime);
   FExportDataset.FieldDefs.Add('Work end', ftDateTime);
   FExportDataset.FieldDefs.Add('Size', ftFloat);
   {$IFNDEF USE_MEM_DATASET}
   TBufDataset(FExportDataset).CreateDataset;
   {$ENDIF}

   FExportDataset.Open;

   // Random data
   for i:=1 to NUM_RECORDS do begin
     if (i mod 100 = 0) then
       WriteLn(Format('Adding record %d...', [i]));
     FExportDataset.Insert;
     FExportDataset.FieldByName('Last name').AsString := 'A';
     FExportDataset.FieldByName('First name').AsString := 'B';
     FExportDataset.FieldByName('City').AsString := 'C';
     FExportDataset.FieldByName('Birthday').AsDateTime := 0;
     FExportDataset.FieldByName('Salary').AsFloat := 0;
     FExportDataset.FieldByName('Size').AsFloat := 0;
     FExportDataSet.FieldByName('Work begin').AsDateTime := 0;
     FExportDataSet.FieldByName('Work end').AsDateTime := 0;
     FExportDataset.Post;
   end;

   WriteLn('Saving...');
   t := now;
   stream := TMemoryStream.Create;
   try
     {$IFDEF USE_MEM_DATASET}
     TMemDataset(FExportDataset).SaveToStream(stream);
     {$ELSE}
     TBufDataset(FExportDataset).SaveToStream(stream);
     {$ENDIF}
     stream.Position := 0;
     WriteLn('Written to memory stream: ', FormatFloat('0.000 s', (now - 
t) * SECONDS_PER_DAY));
     stream.SaveToFile(fn);
   finally
     stream.Free;
   end;
   Writeln('Done. Total time needed for saving: ', FormatFloat('0.000 
s', (now - t) * SECONDS_PER_DAY));

   FExportDataset.Close;

   writeLn(Format('Created file "%s" in folder "data".', [
     ExtractFileName(fn), ExtractFileDir(fn)
   ]));
   FExportDataset.Free;
end;

begin
   CreateDatabase;

   WriteLn;
   WriteLn('Press ENTER to close.');
   ReadLn;
end.