Watch, Follow, &
Connect with Us

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


Welcome, Guest
Guest Settings
Help

Thread: ADO vs FireDAC performance


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


Permlink Replies: 6 - Last Post: Sep 8, 2017 2:25 AM Last Post By: Augusto Afonso ...
Augusto Afonso ...

Posts: 5
Registered: 8/8/09
ADO vs FireDAC performance  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 12:03 AM
Hi all.

For years we have been using ADO as the most adecuate way to connect SQL Server databases with Delphi.

Now, we are trying to migrate to FireDAC, considering this as the right strategy, according to Embaracadero suggestions.

As we have a Professional versión of Delphi Tokyo, we didn't had the FireDAC SQL Server driver available, so we decided to buy the FireDAC AddOn to complete our current Delphi license. Once reinstalled the software, we have a new TFDPhysMSSQLDriverLink available in the Tool Palette, and we are able to select MSSQL as the DriverName in a TFDConnection.

We have created a simple test program to compare the performance between TFDTable vs TADOTable. Also we compare the performance between a TFDTable connected to a MSSQL vs ODBC drivers. The test consists in a basic loop, iterating 100000 rows:

Table1.First;
while not Table1.EOF do begin
Table1.Next;
end;

The results:

- TADOTable (less than 1 second) is much faster than TFDTable (8 seconds).
- TFDTable connected to MSSQL has no advantages over direct connection to ODBC (using the standard driver that comes with FireDAC in the basic license). Both take the same 8 seconds.

Questions:
- Is FireDAC a substitute for ADO? It doesn't seem like that.
- What's the point for using TFDPhysMSSQLDriverLink instead TFDPhysODBCDriverLink? Why to pay for that?

Thanks in advance

A.Afonso

Robert Triest

Posts: 687
Registered: 3/24/05
Re: ADO vs FireDAC performance  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 12:29 AM   in response to: Augusto Afonso ... in response to: Augusto Afonso ...
Hello,

I have a problem with the FireDac under Tokio (compared with Delphi XE8) see:
https://forums.embarcadero.com/message.jspa?messageID=893284#893284

Are you able to do such test (also fetching the records in a loop from a large datatable with FDQuery)
It's difficult for me to trace this issue apart from sending the FireDac tracefiles. (what I did)

thanks.
Augusto Afonso ...

Posts: 5
Registered: 8/8/09
Re: ADO vs FireDAC performance  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 12:54 AM   in response to: Robert Triest in response to: Robert Triest
Hi Robert,

We went to Delphi 10.2 Tokyo from Delphi 7. We haven't "Delphi XE8" to do a comparison.

Regards,

A.Afonso

Robert Triest

Posts: 687
Registered: 3/24/05
Re: ADO vs FireDAC performance  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 6:06 AM   in response to: Augusto Afonso ... in response to: Augusto Afonso ...
We went to Delphi 10.2 Tokyo from Delphi 7. We haven't "Delphi XE8" to do a comparison.
I understand but only the check if such request (like only 50 fetches) takes too long for a server query to execute, will already be nice.
Augusto Afonso ...

Posts: 5
Registered: 8/8/09
Re: ADO vs FireDAC performance  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 9:19 AM   in response to: Robert Triest in response to: Robert Triest
Ok Robert.

What do you want I do exactly?

I've tested the same sample you can see in the first post of this thread. I've subsitute the TFDTable with a TFDQuery. I've fetched 100000 rows, and it took the same 8 seconds for a complete iteration.

Regards,

A.Afonso

Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: ADO vs FireDAC performance  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 11:14 PM   in response to: Augusto Afonso ... in response to: Augusto Afonso ...
- TADOTable (less than 1 second) is much faster than TFDTable (8 seconds).

TFDTable implements and uses by default Live Data Window (LDW). TFDTable in LDW mode issues additional queries to maintain LDW.
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode

TADOTable is basically TADOQuery with SELECT * FROM tab. If you will disable LDW or will use TFDQuery, then performance will be much better.

- TFDTable connected to MSSQL has no advantages over direct connection to ODBC (using the standard driver that comes with FireDAC in the basic license). Both take the same 8 seconds.

Yes, because there will be the same set of the queries issued by TFDTable.

- Is FireDAC a substitute for ADO? It doesn't seem like that.

FireDAC offers more features than ADO. And in general is more fast than ADO. But try to use it properly.

- What's the point for using TFDPhysMSSQLDriverLink instead TFDPhysODBCDriverLink? Why to pay for that?

FireDAC MSSQL driver has more MSSQL specific knowledge and offers more features than FireDAC ODBC bridge driver.

--
With best regards,
Dmitry
Augusto Afonso ...

Posts: 5
Registered: 8/8/09
Re: ADO vs FireDAC performance  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 8, 2017 2:25 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Many thanks Dmitry,

I've been figuring out a bit more. Removing fiMeta option from FetchOptions.Items improves the loop drastically, having the same performance tan ADO (less than 1 second vs 8 seconds). This explains one of the main problems.

You say "FireDAC offers more features than ADO. And in general is more fast than ADO. But try to use it properly". Where can I find good practices for that?

You say "FireDAC MSSQL driver has more MSSQL specific knowledge and offers more features than FireDAC ODBC bridge driver". Is there a way or document to compare both?.

Thanks again,

A.Afonso


Dmitry Arefiev wrote:
- TADOTable (less than 1 second) is much faster than TFDTable (8 seconds).

TFDTable implements and uses by default Live Data Window (LDW). TFDTable in LDW mode issues additional queries to maintain LDW.
http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Browsing_Tables_(FireDAC)#Live_Data_Window_Mode

TADOTable is basically TADOQuery with SELECT * FROM tab. If you will disable LDW or will use TFDQuery, then performance will be much better.

- TFDTable connected to MSSQL has no advantages over direct connection to ODBC (using the standard driver that comes with FireDAC in the basic license). Both take the same 8 seconds.

Yes, because there will be the same set of the queries issued by TFDTable.

- Is FireDAC a substitute for ADO? It doesn't seem like that.

FireDAC offers more features than ADO. And in general is more fast than ADO. But try to use it properly.

- What's the point for using TFDPhysMSSQLDriverLink instead TFDPhysODBCDriverLink? Why to pay for that?

FireDAC MSSQL driver has more MSSQL specific knowledge and offers more features than FireDAC ODBC bridge driver.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02