I am trying to configure a FireDAC TFDQuery component so it fetches records on demand in batches of no more than 500, but I need it to report back what is the total record count for the query not just the number of fetched records. The FetchOptions are configured as follows:
FetchOptions.AssignedValues = [evMode, evRowsetSize, evRecordCountMode, evCursorKind, evAutoFetchAll]
FetchOptions.CursorKind = ckForwardOnly
FetchOptions.AutoFetchAll = afTruncate
FetchOptions.RecordCountMode = cmTotal
FetchOptions.RowSetSize = 500
This immediately returns all records in the table not just 500. I have tried setting RecsMax to 500, which works in limiting the fetched records, but RecordCount for the query shows only 500 not the total.
The FireDAC help file states that setting RecordCountMode to `cmTotal' causes FireDAC to issue
SELECT COUNT(*) FROM (original SQL command text).
Either there is a bug or I am doing something wrong!
I cannot see what other properties I can change. I am confused as to the relationship between RowSetSize and RecsMax and din't find the help file clarified.
I have tried playing with the properties of AutoFetchAll (Again confused as to this properties' purpose), but noting that is was set to afAll I set it to afTruncate to see if that would make a difference, but it didn't.
I have tested FetchOptions' fmOnDemand Mode with a FDTable component and a FDQuery component. Both with identical settings for FetchOptions ie RowSetSize=50. 425,000 rows in the dataset fetched over a network server.
FDTable performs as expected. It loads just 50 tuples and does so almost instantly. When pressing Ctrl+End to get to the end of the DBGrid display, it loads just 100 tuples. Whilst scrolling it rarely loads more than 100 tuples. Impact on memory negligible. But it is slow in scrolling.
FDQuery loads 50 tuples, but takes around 35 seconds to do so and consumes over 0.5GB of memory in the process. If you press Ctrl+Home to move to the end of the connected DBGrid it does so virtually instantly and in the process loads the entire table and consumes a further 700MB of memory.
I also experimented with CachedUpdates. The results above where with CachedUpdates off. When on, there was no impact at all on the performance of FDQuery (still poor), but for FDTable it resulted in it loading the entire table at start up, taking over half a minute to do so and consuming 1.2GBs of memory.
It looks like fmOnDemand mode is only practically usable with FDTable with CachedUpdates off and is not suitable for use with FDQuery at all.