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

Ahead

Have you tried to fetch all records in a ADS tool to compare the total fetch times? – Marquess

MySQL has a GROUP_CONCAT function that does exactly what you want, maybe Advantage DB includes a similar function... – Pismire

@Marquess The Avantage Data Architect of ADS gives me the complete result table (55000 rows) within 7 ms. That's actually nearly the time of the execution of my sql query. My question is, what is ADS doin so different to show their results. Could it be that there is some kind of caching problem? – Adherence

@André ADS really fetches and shows (scrolls) all 55K rows within 7 milliseconds on your screen? I guess this time is only for the first result set records displayed. – Marquess

@Marquess Yes that might be. I guess these are maybe the first 300 entries or something. Okay, maybe I just want to much from it – Adherence

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.

Polynesian

+1 for using a local field variable! But is string allocation slow in Delphi? In an article about TStringBuilder I read it is actually quite fast (faster than TStringBuilder.Add) – Marquess

@mjn, TStringBuilder was introduced in Delphi 2009 AFAIK. I'm using D5/7 so I'm not familiar with it. I guess it's a buffered base class like TStringStream. from my experience concatenation of strings like s := s + foo is verrrrrry slow. – Polynesian

@Polynesian Thank you for your very good answer. I changed my source code now. Still it is really slow compared to the ADS Architect. I get 6 seconds where the architect needs 7 ms. (maybe a little bit more to show the result, but it felts so much faster) – Adherence

@André Dziurla, ADS Architect might be fetching the results asynchronously. so you see the first result set after 7 ms. it might be using a server side cursor, so it's done after 7 ms and the rest of the records are fetched as you scroll down the grid. you on the other hand are using a synchronic query, getting all 55000 to the client side. and plus contracting the string. – Polynesian

Beware that before Delphi 2009, TStringStream is not faster than a string concatenation. And since Delphi 2009, it will use internaly an encoding pattern... So TStringStream is not so fast! – Cassilda

Answer 2

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.

Mitis

What I would like to do is to get the number of my results and move from one entry to the next in the fastest way, maybe in a for loop. I would like to avoid the step by going over a DBGrid if possible. My problem is, how I said in the OP that the ADSQuery.RecCount is very slow. – Adherence

To execute the query it just takes 8 ms. My Connection and everything else is directly on the server. A sql count through one of my tables with 130 k entries takes 200 ms. The query plan is okay, no optimization problems or anything else. – Adherence

It just have problems with the result dataset. I am using a left outer join along two tables. – Adherence

@AndréDziurla: The initial execution cost is just the parse, semantic check, etc. and then it returns the first row (or maybe first few ... I forget). So it typically is very fast. Getting the record count means it has to resolve the entire result set (and possibly write 55,000 records to disk if it does not fit in cache). But 9 seconds does seem slow. You might do better to start a new question asking specifically about the query (show the query, the table definitions, etc.). Maybe an improvement can be made. – Mitis

Answer 3

Why dont't you perform what you need server side and just return the result?

Moreover in situation like this:

• You force Delphi to reallocate the string each time. If you know the largest size it could reach you should preallocate the string size before, and then "correct" it when finished.
• FieldByName() is slow - it must perform a lookup for every invocation. Define the fields you're going to use for the dataset.
• Network performance can matter, if the records are "large". You may set the MTU to a larger value to take better advantage of today gigabit networks.

Altimeter

I am just using a local server; – Adherence

Answer 4

So finally, I've found my mistakes, but some things i don't understand. First of all I changed my left join sql query inside a join query. That made my recordcount faster and also if I am using next,it is faster now. So after that I've checked my table types for each column. And I found out,that it's not very performant to use a fixed size for a character column if it is not necessary. In my case I've choosen a size of 100 for 20 columns, but my columns are increasing in size from column 1 to 20 in steps of three. So the maximum size of column 20 is 60 and column 1 has 3 characters.(These are my search columns) That made my while do clause already two times faster. With these changes. I could get my 55000 entries in 5500 ms. Now I changed the table design. I've put everything in one table, that I don't need a join anymore. At least not for the moment. I used a normal Select .. From .. Where clause. My time for these 55000 entry results was reduced again to 2500 ms. That's more than fine for me. So the only question is still, why it makes a big difference in fetching the data after performing the ADSQuery.open ,if I am using a different sql query. I thought it might affect the time of the execution of the sql query, but it is also affecting the result fetching.

Ahead