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;

Please correctly format your code. For that, [edit](https://stackoverflow.com/posts/68070236/edit) your question. — fpiette, Jun 21 '21 at 15:06
Avoid calling `sqlq.FieldByName('stregkode')` inside the loop. Call it before the loop and save the value to a variable. In the loop, reuse the variable. — fpiette, Jun 21 '21 at 15:08
By default FDQuery is bi-directional datasets, set sqlq.FetchOptions.Unidirectional = True — Branko, Jun 21 '21 at 17:57
Fpiette: I do not understand your second remark. Can you give a more detailed explanation of how to do that. Note that sqlq contains a different value for each iteration in the loop — Ib Elfving, Jun 22 '21 at 02:14

score 2 · Answer 1 · edited Jun 22 '21 at 07:32

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;

Thank you for editing my question. Writing in a foreign language is always a challenge. — Ib Elfving, Jun 22 '21 at 09:04
Hi Ken, I changed my test program as you suggested. This does not increase the performance. Tested it on 10.000 record, several times, with and without your suggestion, and ended on approx. 15 secs processing time on both editions. The unidirection suggestion improved the performance with approx. 15% — Ib Elfving, Jun 22 '21 at 09:18
@lb Elfving, you can incrase performance also with `RowsetSize`. For examle set sqlq.FetchOptions.RowsetSize = 5000 — Branko, Jun 22 '21 at 09:59

Delphi: is DBexpress faster than Firedac

1 Answers1