Fast SQL query but slow result retrieval

Question

I am using the Advantage Database Server from Sybase and have for the moment a nice fast left join query, that runs really fast. The problem is that after running the query I would like to put the results into a string. I retrieved a dataset of 55000 entries. Now It takes up to 16 sec. to put it into the string. My query only took 8 ms to run. My first atempt was this:

    aADSQuery.Open
    aADSQuery.First
    WHILE not aADSQuery.eof do
    begin
       s := s + aADSQuery.FieldbyName('Name').asString+',';
       aADSQuery.Next;
    end;

After, I tried this to avoid the aADSQuery.next, but the aADSQuery.RecordCount took me 9 sec.

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

The database is indexed, with primary key for the Entry ID and indizes for the other columns. I thought about creating a view to count my entries to avoid the recordcount, that might exactly do the same than the sql count. But the count of the entries from the view took the same time as before. If I use the sql count on my base table with 130000 entries it takes only 200 ms. But if I am doing a count on my resulting table, without using a view it takes me 9 s. I quess it is, because there are no indizes for the new temporary result table. Does anyone know how to handle this kind of problem in a proper way or how to get a faster result count?

Thank you very much

Answer 1

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.