Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Slow performance firedac with SQL Server under Delphi Tokyo update 1


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 8 - Last Post: Oct 19, 2017 6:24 AM Last Post By: Carlos Moreno Threads: [ Previous | Next ]
Carlos Moreno

Posts: 12
Registered: 1/30/05
Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2017 8:50 AM
Hello,

Using a simple query that returns 20.000 rows only takes 0.5 secs with ADO, but using firedac it takes 30 seconds.

This is the code used for FireDac and ADO test:

procedure TForm18.Button1Click(Sender: TObject);
var
  i: DWORD;
begin
  i := GetTickCount;
 
  Query.DisableControls;
  Query.Active := false; // simple SQL SELECT * FROM TABLE
  Query.Active := true;
  
  Caption :=  IntToStr(GetTickCount - i) + ' - ' + IntToStr(Query.RecordCount);
  Query.EnableControls;
end;


I have post it on stackoverflow.com but I don´t have any response that solve problem

[ https://stackoverflow.com/questions/46775383/slow-performance-firedac-with-sql-server-under-delphi-tokyo-update-1 https://stackoverflow.com/questions/46775383/slow-performance-firedac-with-sql-server-under-delphi-tokyo-update-1

Regards

Edited by: Carlos Moreno on Oct 18, 2017 5:51 PM
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2017 11:40 AM   in response to: Carlos Moreno in response to: Carlos Moreno
Which database are you using?
Carlos Moreno

Posts: 12
Registered: 1/30/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2017 11:52 AM   in response to: Robert Triest in response to: Robert Triest
Robert Triest wrote:
Which database are you using?

SQL Azure (V12)
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2017 12:35 PM   in response to: Carlos Moreno in response to: Carlos Moreno
Maybe we have a related problem..

https://forums.embarcadero.com/thread.jspa?messageID=894129

Report:

https://quality.embarcadero.com/browse/RSP-18699

Edited by: Robert Triest on Oct 19, 2017 9:02 AM
Carlos Moreno

Posts: 12
Registered: 1/30/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2017 2:12 AM   in response to: Robert Triest in response to: Robert Triest
After various test I detect the cause: MARS.

With MARS disabled the performance is similar in ADO and FireDac
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2017 4:03 AM   in response to: Carlos Moreno in response to: Carlos Moreno
With MARS disabled the performance is similar in ADO and FireDac

For my situation the cause can't be MARS because there is a difference between
the two FireDac versions (XE8 v.s. XE10.2 Tokio)
Can it still be that there is a MARS implementation in FireDac that is different
between the two Delphi versions?

Are you able to download other Delphi versions (wasn't that the case that we can
download all previous versions?!) The ultimate test for me would be that someone else
can verify if there indeed there is a difference between the two versions.
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2017 4:07 AM   in response to: Robert Triest in response to: Robert Triest
Ah...

MARS
Controls the MARS (multiple active result sets) support in a connection:
Yes -- MARS is enabled for a connection. This is the default value.
No -- MARS is disabled.
MARS is a feature supported by SQL 2005 and later, or by SQL Azure. Note that enabling the MARS feature might result in performance degradation.
For information about MARS, see: Using Multiple Active Result Sets (MARS)

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Connect_to_Microsoft_SQL_Server_(FireDAC)

Now I can investigate more on this issue..

AnyWay..
If I put MARS on NO in the connection config, my application is crashing big time..

Edited by: Robert Triest on Oct 19, 2017 2:44 PM
Robert Triest

Posts: 687
Registered: 3/24/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2017 5:15 AM   in response to: Carlos Moreno in response to: Carlos Moreno
I've tested a bit more and the weird thing is that the speed problem now is because the query is using
parameters. (like qry.ParamByName('MyFields').AsString := aValue;)
If I remove the Parameters it seems the problem is going away..

Long execute with parameters.
  SQLText := 'select * from "History" WITH(NOLOCK)';
  SQLText := SQLText + ' where KeyModule=:KeyModule' + ' and Key1=:Key1 and Key2=:Key2 and Key3=:Key3 order by DateTime DESC';
 
  qryHistory.Active := False;
  qryHistory.SQL.Clear;
  qryHistory.SQL.Add(SQLText);
 
  qryHistory.ParamByName('KeyModule').AsString := aKeyModule;
  qryHistory.ParamByName('Key1').AsString := aKey1;
  qryHistory.ParamByName('Key2').AsString := aKey2;
  qryHistory.ParamByName('Key3').AsString := aKey3;
 
  qryHistory.Active := True;


Fast execute without parameters.
  SQLText := 'select * from "History" WITH(NOLOCK)';
  SQLText := SQLText + ' where KeyModule='+QuotedStr(aKeyModule)+' and Key1='+QuotedStr(aKey1)+' and Key2='+QuotedStr(aKey2)+' and Key3='+QuotedStr(aKey3);
 
  qryHistory.Active := False;
  qryHistory.SQL.Clear;
  qryHistory.SQL.Add(SQLText);
 
  qryHistory.Active := True;
Carlos Moreno

Posts: 12
Registered: 1/30/05
Re: Slow performance firedac with SQL Server under Delphi Tokyo update 1  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 19, 2017 6:24 AM   in response to: Robert Triest in response to: Robert Triest
Hi Robert,

yes, my test without params is fastest than parametrized query

regards
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02