Mega Search
23.2 Million


Sign Up

Make a donation  
TFDQuery.Execute seems slow [Edit]  
News Group: embarcadero.public.delphi.database.firedac

HI,

In converting an old BDE-based application to use FireDAC, I have to deal with a method in which several tens of thousands of records are appended to a table. The BDE-based method used a loop based on TTable.Append / Post. I have replaced this with a loop based on TFDQuery.Execute calls using a parameterised SQL

With default TFDQuery settings the method is three times slower than the BDE-based method. I am using XE5 and the TFDConnection uses the DriverName 'MSAcc'

Are there TFDQuery settings I can use to improve its performance?

Thanks for any advice.

Trevor Cooper

Edited by: Trevor Cooper on Jan 19, 2015 9:36 AM

Vote for best question.
Score: 0  # Vote:  0
Date Posted: 19-Jan-2015, at 9:36 AM EST
From: Trevor Cooper
 
Re: TFDQuery.Execute seems slow  
News Group: embarcadero.public.delphi.database.firedac
> Would the speed improvement be so great as to justify the effort?

With Access not so greet as with other DB's. But still +50% speed improvement you will get.
 
> So it seems that FireDAC and Access is not a good combination. 

It is more slow than DAO in basic data access operations. In other it is better.

> If we choose to migrate to SQL Server would we expect to see a big improvement in mass append operations performed with FireDAC?

Yes, it is.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect

Vote for best answer.
Score: 0  # Vote:  0
Date Posted: 20-Jan-2015, at 6:39 AM EST
From: Dmitry Arefiev
 
Re: TFDQuery.Execute seems slow  
News Group: embarcadero.public.delphi.database.firedac
Dmitry,

Thanks for your advice. I am already using transactions, but my concern with array DML is the possibility of running out of memory when there are large number of records involved, and whether that would force me to carve the process up into a number of batches. Would the speed improvement be so great as to justify the effort?

So it seems that FireDAC and Access is not a good combination. If we choose to migrate to SQL Server would we expect to see a big improvement in mass append operations performed with FireDAC?

Regards

Trevor Cooper

Vote for best answer.
Score: 0  # Vote:  0
Date Posted: 20-Jan-2015, at 4:14 AM EST
From: Trevor Cooper
 
Re: TFDQuery.Execute seems slow  
News Group: embarcadero.public.delphi.database.firedac
FireDAC uses Jet ODBC driver to work with Access. But BDE uses
DAO. It is more fast than Jet ODBC. Still you can do:
- surround your ExecSQL calls into a transaction;
- use Array DML.

-- 
With best regards,
Dmitry Arefiev / FireDAC Architect

Vote for best answer.
Score: 0  # Vote:  0
Date Posted: 19-Jan-2015, at 9:14 PM EST
From: Dmitry Arefiev