Doubt with Locate method (TmyQuery)

[amorancho84]

Hello!
I'm trying out the "Locate" feature and I don't quite understand how it works.
I have a table with 20,000 records with the column "Id" as PK.
I have a TMYQuery (Qy_Risk) with "select * from tas_riesgo" in the "SQL.Text" property
I am testing the time difference using Locate:

Case 1:
I don't report the IndexFieldNames property
Case 2:
I report the IndexFieldNames property with value "Id"

I execute the following instruction for both cases:

Code: Select all

procedure TMain.Bt_SearchIdCLick(Sender: TObject);
var
  Ini: TDateTime;
begin

  Ini := Now();
  
  if Qy_Riesgos.Locate('Id', StrToInt(Ed_Id.Text), []) then
    ShowMessage(IntToStr(DateUtils.MilliSecondsBetween(now(), Ini)));

end;

In both cases the time is the same (240ms). I thought that in case 2, reporting the IndexFieldNames property would be faster. Or maybe the case 1 be slower (it depends if we consider that 20,000 records are many or few for the component).

The idea is to position myself in a TmyQuery record through its PK (Id) using the Locate method

Best Regads

[ViktorV]

The Locate method is intended to search for a record; the IndexFieldNames property is intended to sort records on the client. These operations are only performed after fetching all data matching the query -- this amount of time has been most likely spent on fetching the data as the data searching itself is performed by the Locate method in memory on the client side and doesn't require much time.

You can check it by performing the steps:

- set the TMyQuery.FetchAll property to True;
- run the TMyQuery.Open method to fetch all records;
- run the TMyQuery.Locate method and measure that time it takes to find a record.

You can use the TMyQuery.FilterSQL property to filter data on the client side, in which case the clause to filter the data will be added to the generated query when opening the dataset and only the necessary number of records will be returned. To perform sorting on the server before opening the dataset, set the list of sorting fields in the TMyQuery.OrderFields property. See our documentation for more information on OrderFields: https://www.devart.com/unidac/docs/?dev ... fields.htm

[amorancho84]

Good morning Victor.

Thanks for the answer but I think you didn't just answer me.

The option FetchAll was already to true, I know it, and I execute the Open to recover all the records. Ok.
But once I have in client all the loaded records I want to access a record through your PK. My question is, IndexFliedNames, apart from order, index my dataset on the column selected in this property? A kind of TDictionary or value key.
When I make a .Locate I want the access to be instantaneous

Thanks!

[ViktorV]

When using the IndexFliedNames property, records are sorted but not indexed. But in the situation you specified, the execution of the Locate method should be almost lightning fast with FetchAll = True and the previously executed Open method, regardless of whether the IndexFliedNames property is used or not. Therefore, to reproduce the issue with the slow execution of the Locate method please create a sample project demonstrating the behavior you specified and send it to us through the contact form https://devart.com/company/contactform.html, along with the script for creating database objects. Please also specify the exact version of your MySQL server.

[amorancho84]

Hello Viktor.
I know what's going on.
I see that 240ms is very slow and the reason is that the TMyQuery component was linked to a datasource and a grid (cxGrid - DevExpress component-). When you run the Locate method the cursor must be placed on the line of the corresponding grid and that makes it slower than expected.
I have tried 2 things:
1. TMyQuery (without datasource or grid) with the property FetchAll = true at design time
2. TMyQuery (without datasource or grid) with the property FetchAll = false. I have executed Open and then FetchAll

In both cases the Locate took 3ms (very fast).

Thanks for your help

[ViktorV]

Glad to hear that you've been able to pinpoint the cause of that behavior!
Feel free to contact us if you have any further questions about our products.