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

Answer 1

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;