Fast SQL query but slow result retrieval

Use some buffer based class such as TStringStream to populate the string. this will avoid slow reallocation of String concatenation (s := s + foo).

Don't use aADSQuery.FieldbyName('Name').AsString in the loop. It's slow. Instead create a local variable F like this:

var
  F: TField;

F := aADSQuery.FieldbyName('Name');
for i:=0 to count-1 do
begin
  aADSQuery.RecNo := i;
  F.AsString; 
end;

I believe using aADSQuery.Next is faster than using RecNo

procedure Test;
var
  F: TField;
  Buf: TStringStream;
  S: string;
begin
  aADSQuery.DisableControls;
  try
    aADSQuery.Open;
    F := aADSQuery.FieldbyName('Name');
    Buf := TStringStream.Create('');
    try
      while not aADSQuery.Eof do
      begin
        Buf.WriteString(F.AsString + ',');
        aADSQuery.Next;
      end;
      S := Buf.DataString;
    finally
      Buf.Free;
    end;
  finally
    aADSQuery.EnableControls;
  end;
end;

---

You can generate that string on the server side and return it to the client side without the need to construct any strings on the client side:

DECLARE @Names NVARCHAR(max)
SELECT @Names = ''
SELECT @Names = @Names + ',' + ISNULL([Name], '') FROM MyTable
SELECT @Names

---

Also you could optimize performance by setting TAdsQuery.AdsTableOptions. Make sure AdsFilterOptions is set to IGNORE_WHEN_COUNTING and AdsFreshRecordCount is set to False.

From the OP, it is not entirely clear to me if the goal is to find the total number of records or to display the data to a user. If it is to display the data, then appending all the data for 55,000 records into a single string is probably not the best approach. If you are happy with the performance of running the query in Advantage Data Architect, then it probably makes sense to use the similar approach and store the data in some kind of grid.

For example, associate a TDataSource with a TDBGrid and bind the query to the data source:

AdsQuery1.Open;
DataSource1.DataSet:=AdsQuery1;
DBGrid1.DataSource:=DataSource1;

The data aware grid will only fetch as much data as is needed to fill the grid and will request data on demand as the user pages through it.

Edit When you request the record count, the entire result set has to be resolved by the server. If you are using Advantage Local Server and if the data resides on a network server, then there will be extra cost involved in reading all of the data across the network. If you are using Advantage Database Server (the client/server version), then the processing will take place at the server and may be much faster.

It of course depends on the query, but 9 seconds to resolve the result set might be overly long. Within Advantage Data Architect, you can check the optimization of the query. There is a "Show Plan" option under the SQL menu as well as a button on the tool bar in the SQL utility for showing the query plan. It may be that you are missing a necessary index.