Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: Firedac (XE6) performance VS IBX/MDO/DBX


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


Permlink Replies: 5 - Last Post: Aug 22, 2016 12:43 PM Last Post By: Rex Bloom
Mário Reis

Posts: 26
Registered: 9/16/06
Firedac (XE6) performance VS IBX/MDO/DBX  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 11, 2014 8:19 PM
Dmitry,

I've bean looking around but i was not able to find any answers.
How come the use of Firedac (XE6) + Firebird + Zebedee on Win7 pro with a remote connection is very much slower than IBX/MDO/DBX connections using the very same files and specifications?
Opening a master file and all the suport tables in a forrm takes much more time then the other connections!

Can you help?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firedac (XE6) performance VS IBX/MDO/DBX  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 11, 2014 9:19 PM   in response to: Mário Reis in response to: Mário Reis
Try to exclude fiMeta from FetchOptions.Items and reduce FetchOptions.RowsetSize to 1-10.

--
With best regards,
Dmitry Arefiev / FireDAC Architect
Rex Bloom

Posts: 5
Registered: 8/13/98
Re: Firedac (XE6) performance VS IBX/MDO/DBX  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 3, 2016 8:24 AM   in response to: Mário Reis in response to: Mário Reis
Dmitry,

I am also having some performance issues. Here are my settings that I have:

FDManager()->Active = true;
FDManager()->ResourceOptions->DirectExecute = true;
FDManager()->FetchOptions->Unidirectional = true;
FDManager()->FetchOptions->RowsetSize = 1000;
FDManager()->FetchOptions->Mode = fmOnDemand;
FDManager()->FetchOptions->Items = TFDFetchItems() << fiBlobs << fiDetails;
FDManager()->FetchOptions->AutoFetchAll = afAll;
FDManager()->FetchOptions->Mode = fmAll;

FDManager()->UpdateOptions->ReadOnly = true;
FDManager()->UpdateOptions->RequestLive = false;

// make new TFDConnection and connect

TFDConnection *C = new TFDConnection(0);

C->ConnectionName = "";
C->DriverName = "MSSQL";

C->LoginPrompt = false;
C->Params->Values["DriverID"] = "MSSQL";
C->Params->Values["SchemaOverride"] = "sa.dbo";
C->Params->Values["Server"] = Server;
C->Params->Values["Database"] = Database;
C->Params->Values["OSAuthent"] = "Yes";
C->Params->Values["ApplicationName"] = "ProPharmOne Client";
C->Params->Values["Workstation"] = G.Local->ComputerName;
C->Params->Values["MARS"] = "Yes";

C->Open();

I have customers that were using DBX on Windows 7 with sqlncli10, XE6. Now they are using FireDAC, XE8 in this environment and the performance is very slow. It is not on all customers but more than just a few.

The actual queries I am running have not changed much with this tech upgrade. I really want to use/keep FireDAC and really need help pinpointing why it is so much slower.

one client had ODBC tracing turned on which was a culprit. Nothing has jumped out as a common denominator.

Thanks.

Rex

Edited by: Rex Bloom on Aug 3, 2016 8:24 AM

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Firedac (XE6) performance VS IBX/MDO/DBX  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 4, 2016 10:47 PM   in response to: Rex Bloom in response to: Rex Bloom
FDManager()->FetchOptions->Unidirectional = true;
FDManager()->FetchOptions->RowsetSize = 1000;
FDManager()->FetchOptions->Mode = fmAll;

These settings contradict each other and may lead to a bad performance:
* fmAll means to fetch all records on dataset open
* Unidirectional=True means to discard previously fetched rowsets (packet of RowsetSize records)
* RowsetSize=1000 means that FireDAC will fetch rows by packets of 1000 rows size

Use either fmAll, either Unidirectional=True, otherwise after open your dataset
will have only last fetched packet. I will suggest to remove fmAll.

DBX MSSQL uses RowsetSize=1, so when you will open a dataset, then
DBX will fetch just one record. But with your FireDAC settings it will fetch
all records. This may be one of the reasons why performance is different.
BTW, you does not provide a test case comparing performance ....

Other options, which will affect FireDAC MSSQL driver performance is
FetchOptions.CursorKind - try different values. Also, if your table has blob
fields, then try to exclude fiBlobs from FetchOptions.Items. Also try to
set MARS=No in connection parameters (does it affect ?).

It is not on all customers but more than just a few.

Start from getting FireDAC environment reports for fast and slow machines,
then compare client software versions:
http://docwiki.embarcadero.com/RADStudio/Berlin/en/DBMS_Environment_Reports_(FireDAC)

--
With best regards,
Dmitry
Rex Bloom

Posts: 5
Registered: 8/13/98
Re: Firedac (XE6) performance VS IBX/MDO/DBX  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 8, 2016 1:49 PM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry,

Thank you for the response. Mostly what I am doing is selecting many records from my database by Primary Key so I get back one and only one record.

It is plenty fast in my dev environment and most deployments, I just have a few clients where things really slow down. So my settings work most of the time and seem acceptable for the application (although I am making the changes you recommend). There must be something (odbc, antivirus, older version of windows, 32/64 bit issue, network congestion, network speed, etc) that causes some clients to work great and others to choke.

Rex
Rex Bloom

Posts: 5
Registered: 8/13/98
Re: Firedac (XE6) performance VS IBX/MDO/DBX  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Aug 22, 2016 12:43 PM   in response to: Rex Bloom in response to: Rex Bloom
Rex Bloom wrote:
Dmitry,

Thank you for the response. Mostly what I am doing is selecting many records from my database by Primary Key so I get back one and only one record.

It is plenty fast in my dev environment and most deployments, I just have a few clients where things really slow down. So my settings work most of the time and seem acceptable for the application (although I am making the changes you recommend). There must be something (odbc, antivirus, older version of windows, 32/64 bit issue, network congestion, network speed, etc) that causes some clients to work great and others to choke.

Rex

Thanks again for all the help. It looks like this was an issue of Antivirus scanning the SQL Server data and log files. Nothing to do with FireDAC or my application.

Hope your day is great!!
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02