Fastest way to locate record in TQuery resultset

问题:

I am wondering what the best (read: fastest) way is to locate a record in a resultset of a Tquery SQL statement.

Until now i'm using TQuery.Locate and if i'm right that is the only statement that can be used to search in the resultset. So how can we optimize this?

I have a few ideas, but haven't had the time yet to compare them all on large datasets:

Let say we have a table with the following fields:

Create Table aTable (
 ID int, 
 Name1 varchar(50), 
 Name2 varchar(50));

And the following query:

SELECT ID, Name1, Name2 from aTable

We want to locate a record by its ID in the resulting set

• Will a locate be faster if aTable has an index on ID?
• Will a locate be faster if I add "Order By ID" to the SQL statement?

Any ideas on this?

[Edit] To clarify the use of this: The query is executed by a Reportbuilder Dataview, and then made available through a datapipeline (which is the TQuery.Dataset). In the custom report I need to travel the pipeline based on some higher level ID. So NOT using a query is not applicable here. I'm just wondering if any of my suggestions above would speed things up.

回答1:

Here are some tips that you might find helpful

• Use Locate() method only on indexed columns
• Use "order by" ONLY on indexed columns
• Use prepare before opening query
• Use DisableControls / EnableControls
• Use Ascending / Descending index (or both) depending on your needs
• Give a try to FastReports

If you have master / detail on large datasets DO NOT fetch all details record - in other words - don't use local master detail - let the DB engine gives you only the desired records.

回答2:

If you need to do this very often on a large dataset you will be better of converting the dataset to an record array and implement some custom search routines on the index field.

回答3:

You should add an ID to search for, in order to retrieve only the expected row.

In the ReportBuilder Data view, go to the Query Designer, then to the Search tab, then add a field setting with the "Auto search" option.

Then go to the ReportBuilder Calc view, select "View / Events" from the menu. Click on the Report root item in the "Report object" tree view, then double-click on "OnGetAutoSearchValues" and retrieve the expected value:

var
    Fld : TppAutoSearchField;
    i   : integer;     
begin
  for i:=0 to Report.AutoSearchFieldCount-1 do
  begin
   Fld := Report.AutoSearchFields[i];

    if Fld<>nil then
    begin    
      if (Fld.FieldName='FIRST_PARAM') then 
      begin
        Fld.SearchExpression := .....;
      end
      else if (uppercase(Fld.FieldName)='2ND_PARAM') then
      begin 
        Fld.SearchExpression := intToStr(...);
      end;
    end;
  end;  
end;