Watch, Follow, &
Connect with Us

Please visit our new home
community.embarcadero.com.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC TFDTable Insert/Post Performance



Permlink Replies: 3 - Last Post: Nov 8, 2016 10:28 PM Last Post By: Dmitry Arefiev
Andy S.

Posts: 2
Registered: 6/25/06
FireDAC TFDTable Insert/Post Performance
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 3, 2016 12:18 AM
Hi,

the following circumstances are given:

- Firebird DB table with approximately 10.000 records. The records would be shown in a TDBGrid
- On the form i have a TTable and a TFDTable, one of them would be assigned to a TDataSource
- The TDataSource is assigend to a TDBGrid and TDBNavigator

When i add records in/to the TDBGrid (or rather to the DataSet) i have the following performance:

1.) TTable = ~ 15 ms
2.) TFDTable (with active Live Data Window [LDW]) = ~ 250 ms
3.) TFDTable (w/o LDW; ckStatic Cursor and w/o Cached Update) = < 15 ms
4.) TFDTable (w/o LDW; ckAutomatic Cursor and with Cached Updates) = < 15 ms

Will there be a chance to speed up the performance of the point no. 2 (TFDTable Insert with active LDW) ?
Michel BALLASSE

Posts: 6
Registered: 3/6/12
Re: FireDAC TFDTable Insert/Post Performance
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 7, 2016 11:15 PM   in response to: Andy S. in response to: Andy S.
What do you not use a TFDQuery with cached updates option and TFDupdateSQL to insert, modifiy, delete ?

Andy S. wrote:
Hi,

the following circumstances are given:

- Firebird DB table with approximately 10.000 records. The records would be shown in a TDBGrid
- On the form i have a TTable and a TFDTable, one of them would be assigned to a TDataSource
- The TDataSource is assigend to a TDBGrid and TDBNavigator

When i add records in/to the TDBGrid (or rather to the DataSet) i have the following performance:

1.) TTable = ~ 15 ms
2.) TFDTable (with active Live Data Window [LDW]) = ~ 250 ms
3.) TFDTable (w/o LDW; ckStatic Cursor and w/o Cached Update) = < 15 ms
4.) TFDTable (w/o LDW; ckAutomatic Cursor and with Cached Updates) = < 15 ms

Will there be a chance to speed up the performance of the point no. 2 (TFDTable Insert with active LDW) ?
Andy S.

Posts: 2
Registered: 6/25/06
Re: FireDAC TFDTable Insert/Post Performance
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 8, 2016 1:03 AM   in response to: Michel BALLASSE in response to: Michel BALLASSE
Hi,
thanks for your reply and idea. I'm not sure but i guess that the TFDQuery didn't have an Live Data Window Mode.

The Emba Documentation says:
"In Live Data Window (LDV) mode FireDAC queries and keeps in memory only 2 * FetchOptions.RowsetSize of records - a window into the table data."

I tried the following in a simple test application:
- I've assigend a TFDQuery to a TDataSource and assigend the TDataSource to a TDBGird.
- Activated the FetchOptions.LiveWindowFastFirst and changed the FetchOptions.RowsetSize to 20.
- After i opend the TFDQuery, I scrolled down in the TDBGrid until i reached record no. 3.000. Parallel i've checked the memory usage, and it increased with every fetch of the next 20 records.

That is the reason why i think that the LDW Mode does not work with an TFDQuery. If the TFDQuery had the LDW Mode the memory usage shouldn't increase.

Furhter more the CachedUpdate would deactivate the LDW Mode. At least the Emba Documentation says that:
"The LDW is used when all the following conditions are met, otherwise the standard mode is used:
•CachedUpdates = False (default value)
..."

Michel BALLASSE wrote:
What do you not use a TFDQuery with cached updates option and TFDupdateSQL to insert, modifiy, delete ?
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: FireDAC TFDTable Insert/Post Performance
Click to report abuse...   Click to reply to this thread Reply
  Posted: Nov 8, 2016 10:28 PM   in response to: Andy S. in response to: Andy S.
Several aspects:

1) LDW mode is supported only by TFDTable.

2) If you care about performance, then consider to use Array DML:
http://docwiki.embarcadero.com/RADStudio/Berlin/en/Array_DML_(FireDAC)

3) Still if you need to perform inserts using TFDTable then create a minimized
test application, reproducing the performance issue, enable FireDAC trace
output, produce trace and analyze it - which SQL command leads to big delay.
http://docwiki.embarcadero.com/RADStudio/Berlin/en/Tracing_and_Monitoring_(FireDAC)

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

Server Response from: ETNAJIVE02