FireDAC data access components slower than DBExpress?

Question

I have migrated the database access components in our Delphi XE5 application from DBExpress to FireDAC.

So where I was using a TSQLConnection/TSQLDataSet, I am now using TFDConnection/TFDQuery.

My observations so far are that returning 125 000 rows of data (via a simple SELECT * query) from our SQL Server 2014 database in the cloud to the client application, takes about twice as long using the FireDAC components vs using the DBExpress components.

When I run the same test on the same table, with both the application and SQL server on the same machine, the FireDAC components are slightly quicker.

Is this normal, or is there something that I might be doing incorrectly? I am not very familiar with the FireDAC components, so any pointers would be very helpful.

The DBExpress code:

SQL_dataset:=TSQLDataSet;

....

SQL_dataset.CommandType:=ctQuery; 
SQL_dataset.CommandText:='SELECT * FROM TABLE';

SQL_dataset.Open;

If SQL_dataset.IsEmpty=False then 
begin 
  SQL_dataset.First;

  While not SQL_dataset.Eof do 
  begin
    { RETURN THE RECORDS }
    SQL_dataset.Next;  
  end;
end;

The FireDAC code:

SQL_query:=TFDQuery;

...

SQL_query.SQL.Text:='SELECT * FROM TABLE';
SQL_query.Open;

... then as per the DBExpress code.

Based on online research, I have tried variations of the following, but nothing seems to be making much of a difference:

SQL_query.ResourceOptions.ParamCreate  :=False;
SQL_query.ResourceOptions.ParamExpand  :=False;
SQL_query.ResourceOptions.MacroCreate  :=False;
SQL_query.ResourceOptions.EscapeExpand :=False;
SQL_query.ResourceOptions.DirectExecute:=True;
SQL_query.FetchOptions.CursorKind      :=ckDefault;
SQL_query.FetchOptions.Mode            :=fmOnDemand;
SQL_query.FetchOptions.RowsetSize      :=1000;
SQL_query.FetchOptions.Unidirectional  :=True;
SQL_query.FetchOptions.Items           :=
                                      SQL_query.FetchOptions.Items-[fiMeta];
SQL_query.UpdateOptions.ReadOnly       :=True;
SQL_query.DisableControls;

Any advice or comments would be much appreciated.

Upgrade

You are not fetching 125k rows with FireDAC, but only 1k (FetchOptions.RowsetSize because of the fmOnDemand mode). Rows are fetched as you are scrolling through the dataset. I don't know how about DBX, but are you sure there is no similar mode possibly fetching on demand less rows than FireDAC ? – Eldredge

What are results with CursorKind=ckAutomatic ? – Rosecan

TLama:I tried setting the RowsetSize to 125000, it actually was slower than with the setting at 1000; SQL_dataset.Open took much longer. Setting the mode to fmAll had the same effect. There was no difference between having RowsetSize at 50 (the default) or 1000, timing was exactly the same. da-soft: there was no change with CursorKind=ckAutomatic, exactly the same time taken to return the results. – Upgrade

As I mentioned, the FireDAC components return the results more quickly when the application and the SQL server database are on the same machine, and also when the SQL server database is on a server on the local network. It's when the accessing via the internet that it seems to be the other way around. – Upgrade