Fast SQL query but slow search for results

Question

I am using the Advantage database server from Sybase and at the moment I have in mind a quick fast connection request on the left, which is very fast. The problem is that after running the query, I would like to bring the results to a string. I got a dataset of 55,000 records. Now it takes up to 16 seconds. put it in a string. My request took only 8 ms. My first trick was as follows:

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

After that, I tried this to avoid aADSQuery.next, but aADSQuery.RecordCount took me 9 seconds.

  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 a primary key for the login ID and indexed for other columns. I was thinking of creating a view to count my records in order to avoid writing, which could exactly do the same as the sql number. But the number of records from the view took the same time as before. If I use sql, counting on my base table with 130,000 records takes only 200 ms. But if I do the counting on my summary table, without using the view, I will need 9 s. I do not know, because there are no indexes for the new temporary result table. Does anyone know how to handle a similar problem correctly or how to get a faster result?

Many thanks

Answer 1

Use some buffer class, for example TStringStream , to fill the string. this avoids the slow redistribution of String concatenation ( s := s + foo ).

Do not use aADSQuery.FieldbyName('Name').AsString in a loop. It is slow. Instead, create a local variable F as follows:

 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 create this line on the server side and return it to the client side without having to create any lines on the client side:

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

---

You can also optimize performance by setting TAdsQuery.AdsTableOptions . Make sure AdsFilterOptions set to IGNORE_WHEN_COUNTING and AdsFreshRecordCount set to False .

Answer 2

From OP, it’s not entirely clear to me if the goal is to find the total number of records or display data to the user. If it displays data, then adding all the data for 55,000 records to one row is probably not the best approach. If you are satisfied that you are executing a query in Advantage Data Architect, then it probably makes sense to use a similar approach and store the data in some kind of grid.

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

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

A data-enabled network will retrieve as much data as needed to fill the grid and will request data on request as user pages through it.

Change When you request a record counter, the entire result set must be resolved by the server. If you use the Advantage Local Server and if the data is on a network server, then reading all the data over the network will incur additional costs. If you use the Advantage Database Server (client / server version), processing will occur on the server and can be much faster.

Of course, this depends on the query, but 9 seconds to solve the result set may be too long. In Advantage Data Architect, you can check query optimization. The SQL menu has the Show Plan option, as well as a button on the SQL utility toolbar to display the query plan. Perhaps you are missing the required index.

Answer 3

Why don't you do what you need on the server side and just return the result?

In addition, in such a situation:

• You force Delphi to redistribute the line every time. If you know what the largest size it can reach, you must first distribute the size of the string before that, and then “fix” it when you're done.
• FieldByName () is slow - it must perform a search for each call. Define the fields that you intend to use for the dataset.
• Network performance may matter if the records are large. You can set the MTU to a higher value to make better use of gigabit networks today.

Answer 4

So finally, I found my mistakes, but some things I do not understand. First of all, I changed my left sql query inside the connection request. This accelerated my record rate, and also, if I use the following, it happens faster. So after that, I checked the table types for each column. And I found out that it’s not very convenient to use a fixed size for a column of characters unless it is necessary. In my case, I chose size 100 for 20 columns, but my columns increase in size from column 1 to 20 in increments of three. So the maximum size of column 20 is 60, and column 1 has 3 characters (these are my search columns). This made my while clause twice as fast. With these changes. I can get my 55,000 records in 5500 ms. Now I have redesigned the table. I put everything in one table and I no longer need to join. At least for now. I used the usual Select .. From .. Where clause. My time for these 55,000 input results was again reduced to 2500 ms. This is more than good for me. Thus, the only question that remains is why it makes a big difference in retrieving data after running ADSQuery.open if I use a different sql query. I thought this could affect the execution time of the SQL query, but it also affects the selection result.