A: Set ADQuery.FetchOptions:

• CursorKind = ckDefault or ckForwardOnly
• Mode = fmOnDemand
• RowsetSize = 1000
• Unidirectional = True
• optionally exclude fiMeta from Items, if you does not need to edit dataset.

A: The following approach with the ClientDataset to hold multiple result sets: 

  begin loop
    // run SqlQuery with new params
    ...
   ClientDataset.AppendData(SqlQuery.Data)
    ...
  end loop

may be replace with the single TADQuery. Kind of that: 

  // initial open and fetch
  ADQuery1.Params[0].AsInteger := 1;
  ADQuery1.Open;

  // reexecute command, fetch rows again and append them
  // to the existing rows
  ADQuery1.Command.Close;
  ADQuery1.Params[0].AsInteger := 2;
  ADQuery1.Command.Open;
  ADQuery1.FetchAgain;
  ADQuery1.FetchAll;

  // reexecute again with different parameter value
  ADQuery1.Command.Close;
  ADQuery1.Params[0].AsInteger := 3;
  ADQuery1.Command.Open;
  ADQuery1.FetchAgain;
  ADQuery1.FetchAll;

A: See the TADDataSet.FetchAgain method description. As other option, you can execute a SQL command, which will fetch the additional records into an existing dataset: 

ADCommand1.CommandText := 'select ... from ... where id= :id';
ADCommand1.Open;
ADCommand1.Fetch(ADQuery1.Table);
ADCommand1.Close;

A: The RecordCount by default shows the number of records in the dataset records cache. More about record counting modes you can read at TADFetchOptions.RecordCountMode Property. 

50 is the default rowset size. That is the number of records AnyDAC will fetch at single request. So, right after open the dataset will have <= 50 records, that is what RecordCount is showing. When you will navigate through dataset it will fetch additional rowsets and the number of records may grow. 

To show the total number of records which query returns, you may do one of the following:

• perform ADQuery1.FetchAll. So all records will be fetched and RecordCount will show their number;
• to set FetchOptions.RecordCountMode to cmTotal. Note, that may lead to performance degradation, as AnyDAC will perform additional SELECT COUNT(*) query for each SELECT query.