Delphi: is DBexpress faster than Firedac

Question

I'm running a Mysql server on my network (Mariadb 10.3.24), and have made a performance test with dbexpress and firedac on the same data, same machine and with no other users on the database. I'm using Delphi 10.1 and made no changes to the connection or query components setup.

My findings were (total number of records is 261.000):

Reading 100.000 records without a "where-clause"
Firedac : 184 sec
DBexpress: 93 sec

Reading 100.000 records with a where clause (indexed)
Firedac: 160 sec
DBexpress: 86 sec

All my programs are programmed with Firedac, is there a simple way to speed up Firedac, or do i need to switch to dbexpress to get a decent performance ?

My test (identical for dxexpress and firedac):

var start, slut : tdatetime;
    n : integer;
begin
  start := now;
  listbox1.Items.Clear;
  sqlq.Close;
  sqlq.SQLConnection:=sqlcon;
  sqlq.SQL.Clear;
  sqlq.SQL.Add('select * from forsendelser where kundenummer="test" limit '+spinedit1.Text);
  sqlq.Open;

  while not sqlq.Eof do begin
    listbox1.Items.Add(sqlq.FieldByName('stregkode').AsString );
    sqlq.Next;
  end;

  sqlq.Close;

  n :=SecondsBetween(Now, start);
  edit2.Text:=n.ToString;
end;

Answer 1

There are several things that can be done with your code to improve the performance.

Start with not updating the ListBox.Items during the loop, as each time an item is added or deleted the screen has to update. This isn't needed while the loop is running.

Second, stop using FieldByName inside the loop. It forces a search through the table's fields to find that field each time the loop is executed, which isn't needed. You can get the field one time before the loop runs, store it in a variable, and access it through that variable in the loop.

This should improve performance considerably for you.

var 
  start: TDateTime;
  n: Integer;
  Fld: TField;
begin
  start := now;
  ListBox1.Items.BeginUpdate;
  try
    listbox1.Items.Clear;
    sqlq.Close;
    sqlq.SQLConnection := sqlcon;
    sqlq.SQL.Text := 'select * from forsendelser where kundenummer="test" limit ' + spinedit1.Text;
    sqlq.Open;

    Fld := sqlq.FieldByName('stregkode');
    while not sqlq.Eof do 
    begin
      listbox1.Items.Add(Fld.AsString);
      sqlq.Next;
    end;
    sqlq.Close;
  finally
    ListBox1.Items.EndUpdate;
  end;

  n :=SecondsBetween(Now, start);
  edit2.Text:=n.ToString;
end;