ADOQuery.Locate Slow, Create Index

Question

I have following line to Locate a row in a Query .

if Query.Locate('Line;Hour;Minute',VarArrayOf([Line-400,AHour,minuteof(Start)]),[]) = true then

This is slow, now I remember that one can add Indexes to the Query so that the Locate itself is multiple times faster. Unfortunately I cannot seem to find the example.

Can someone please help me out? Regards Robert

What db backend are you using? – MartynA Apr 16 '20 at 08:53 — MartynA, Apr 16 '20 at 08:53

MartynA · Accepted Answer · 2020-04-17T17:24:57.890

Interesting q.

Update please see update below.

I set up some test data on my SS2014 Sql Server to run some tests using code like this:

ID := 1;
for Line := 1 to 1000 do begin
  for AHour := 1 to 24 do begin
    for AMinute := 1 to 60 do begin
      AdoQuery1.InsertRecord([ID, Line, AHour, AMinute]);
      Inc(ID);
      end;
    end;
  end;
end;

Then, I ran some tests like this one

procedure TForm1.LocateTest1(DisableControls, UseSort : Boolean);
var
  T1 : Integer;
  Line,
  AHour,
  AMinute : Integer;
begin

  AdoQuery1.Sql.Text := 'select * from linetest order by line, ahour, aminute';
  AdoQuery1.CursorLocation := clUseClient;
  AdoQuery1.Open;
  T1 := GettickCount;
  if DisableControls then
    AdoQuery1.DisableControls;

  if UseSort then
    AdoQuery1.Recordset.Sort := 'Line,AHour,AMinute';
  Line := 1000;
  AHour := 23;
      for AMinute := 60 downto 1 do begin
        if not AdoQuery1.Locate('Line;AHour;AMinute', VarArrayOf([Line, AHour, AMinute]), []) then
          Caption := Format('Locate failed %d %d %d', [Line, AHour, AMinute]);
      end;
  Memo1.Lines.Add('Test1 : ' + IntToStr(GetTickCount - T1));
  if DisableControls then
    AdoQuery1.EnableControls;
  AdoQuery1.Close;
end;

The reason for involving Disable/EnableControls was because of the results I reported here Why does scrolling through ADOTable get slower and slower? , that calling DisableControls has a huge impact on scrolling speed even if there are no db-aware controls involved.

However, it seems that scrolling does not make a major impact on executing Locate() on a TAdoQuery, because calling DisableControls only took about 1.5 seconds of the recorded time of around 26 seconds. Obviously, TAdoQuery.Locate doesn't perform at all well with a large number of rows.

The idea of the UseSort parameter was to see if sorting the RecordSet behind the AdoQuery made any difference to the speed, but it didn't, the reason being that Locate calls TCustomAdoDataSet.LocateRecord which uses Sort anyway.

You mentioned adding indexes. Unfortunately, TAdoQuery only supports use of server-side indexes in executing thq SQL query, not locating records in the retrieved result set. You can add client-side indexes to TAdoTable, but according to a test similar to the one above, rather to my surprise, they make virtually no difference to the speed of Locate().

So, given my results so far, it would seem very possibly quicker to use a parameterised SELECT to retrieve only the row currently of interest, rather than trying to Locate it in a large tesult set. Alterantaivel, you could retrieve the result set into a ClientDataSer via a DatasetProvider or into a FireDAC FDMemTable, etc. Ymmv, it depends on what you're doing exactly ...

Update Since posting my original answer, I have a couple of further updates that it might be useful to include.

One concerns a way of mimicking Locate using calls to the AdoQuery's RecordSet's Find and Filter methods that is significantly faster (around 15 secs) than doing AdoQuery1.Locate repeatedly. I am still probling this and will post another update in a day or two.
The other is to briefly mention doing the Locates doing a FireDAC FDQuery instead of an AdoQuery. This seems to do the same set of Locates as takes around 25 seconds with the AdoQuery in under 9 seconds, using the following code:

Using FDQuery.Locate

procedure TForm2.LocateTest;
var
  T1 : Integer;
  Line,
  AHour,
  AMinute : Integer;
begin

  FDQuery1.Sql.Text := 'select * from linetest order by line, ahour desc, aminute desc';
  //FDQuery1.CursorLocation := clUseClient;
  FDQuery1.CursorKind := ckForwardOnly;
  FDQuery1.Open;

  T1 := GettickCount;
  Line := 1000;
  AHour := 1;

  for AMinute := 1 to 60 do begin
    if not FDQuery1.Locate('Line;AHour;AMinute', VarArrayOf([Line, AHour, AMinute]), []) then
      Caption := Format('Locate failed %d %d %d', [Line, AHour, AMinute]);
  end;

  Memo1.Lines.Add('Test1 : ' + IntToStr(GetTickCount - T1));
  FDQuery1.Close;
end;

thank you, then I might have imagined it. I could have sworn that it is possible to index the Recordset once received , but it might be that my imagination was playing with me. Anyway what I did , because I was experimenting parallel to this. I open the Tables and insert all the data into dynamic arrays . And I manipulate the dynamic array after that . I wanted to do only one select because I am creating lots of charts from the same table . And I have noticed that doing multiple selects is slower then one big select . 10 Seconds down to 3 seconds . — user1937012, Apr 16 '20 at 15:27
I was looking for this BQuery.Fields['Line'].Properties.Item['Optimize'].Value:=true; But it did not help. In the end I needed to rethink this entire thing. And I realized that I do not need the locate . Since I have already ordered the Items . Instead I have a IF ELSE , where I check if the Row matches current period if yes I get data and do a BQuery.Next else I go forward withouth next. Same result better performance. — user1937012, Apr 17 '20 at 08:43

score 1 · Answer 2 · answered Apr 16 '20 at 13:43

The documentation says that Locate move the cursor to the first row matching a specific search criteria.
If your table have many records, Locate is slow.
Locate is usually used in local databases, but in client-server RDBMs is better thar you use a SQL with WHERE for minimize the search time and minimize the data traffic.

ADOQuery.Locate Slow, Create Index

2 Answers2