FireDAC Query RecordCountMode

Question

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.

Answer 1

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.

Answer 2

The results of my tests using fmOnDemand with postgreSQL and MySQL are basically the same. With FDTable fmOnDemand only downloads what it needs limited to the RowSetSize. With a RowSetSize of 50 it initially downloads 50 tuples and no matter where you scroll to it never downloads more than 111 tuples (though doubtless that is dependent on the size of the connected DBGrid. If you disconnect the FDTable from a data source it initially downloads 50 tuples and if you then navigate to any record in the underlying table it downloads one tuple only and discards all other data.

FDQuery in fmOnDemand downloads only the initial 50 tuples when opened, but if you navigate by RecNo it downloads every tuple in between. I had rather hoped it would use LIMIT and OFFSET commands to get only records that were being requested.

To recreate the test for PostGre you need the following FireDAC components:

object FDConnectionPG: TFDConnection
Params.Strings = (      
  'Password='
  'Server='
  'Port='
  'DriverID=PG')
ResourceOptions.AssignedValues = [rvAutoReconnect]
ResourceOptions.AutoReconnect = True    
end

object FDQueryPG: TFDQuery
Connection = FDConnectionPG
FetchOptions.AssignedValues = [evMode, evRowsetSize]    
end

object FDTable1: TFDTable
CachedUpdates = True
Connection = FDConnectionPG
FetchOptions.AssignedValues = [evMode, evRowsetSize, evRecordCountMode]
FetchOptions.RecordCountMode = cmFetched
end

If you wish to recreate it with MYSQL, you will basically need the same FireDAC components, but the FDConnectionneeds to be set as follows:

object FDConnectionMySql: TFDConnection
Params.Strings = (
  'DriverID=MySQL'
  'ResultMode=Use')
ResourceOptions.AssignedValues = [rvAutoReconnect]
ResourceOptions.AutoReconnect = True   
end

You'll need an edit box, two buttons, a checkbox, a timer and a label and the following code:

procedure TfrmMain.Button1Click(Sender: TObject);
begin
if not FDQueryPG.IsEmpty then
  begin
    FDQueryPG.EmptyDataSet;
    FDQueryPG.ClearDetails;
    FDQueryPG.Close;
  end;

if not FDTable1.IsEmpty then
  begin
    FDTAble1.EmptyDataSet;
    FDTable1.ClearDetails;
    FDTable1.Close;
  end;


lFetched.Caption := 'Fetched 0';
lFetched.Update;

if cbTable.checked then
  begin
    FDTable1.TableName := '[TABLENAME]';
    FDTable1.Open();
    lFetched.Caption := 'Fetched '+ FDTable1.Table.Rows.Count.ToString;
  end
else
  begin
    FDQueryPG.SQL.Text := 'Select * from [TABLENAME]';        
    FDQueryPG.open;
    lFetched.Caption := 'Fetched '+ FDQueryPG.Table.Rows.Count.ToString;
  end;
timer1.Enabled:=true;    
end;

procedure TfrmMain.Button2Click(Sender: TObject);
begin
  if cbTable.Checked then
   FDTable1.RecNo := strToInt(Edit1.Text)
  else
   FDQueryPG.RecNo := strToInt(Edit1.Text);
end;

procedure TfrmMain.cbTableClick(Sender: TObject);
begin
  timer1.Enabled := False;
end;

procedure TfrmMain.Timer1Timer(Sender: TObject);
begin
  if cbTable.checked then
    lFetched.Caption := 'Fetched '+ FDTable1.Table.Rows.Count.ToString
  else        
    lFetched.Caption:='Fetched '+FDQueryPG.Table.Rows.Count.ToString;
  lFetched.Update;
end;