Fastest way to locate record in TQuery resultset

Question

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.

Lectern

Locate is executed on the client side. So in the worst case the whole result set needs to be fetched from the database first. It is much faster if the client creates the SQL which includes the ID in the WHERE clause, this will only fetch one record. – Vercelli

It doesn't really matter if you add "order by ID" to your select because if ID is an PK then the DB Engine will get the resulting rows by the PK column's index. – Folkway

@Justme - So indexes in the original table will have their effect on the TQuery.Locate statement? – Lectern

@Lectern I did not say so. As you can see the select without an "order by" is the same as the "order by PK column". In other words the natural plan of the query in this case is as fast as indexed read. And Yes - you should have index on ID - it's created if you make ID the PK – Folkway

Answer 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.

Folkway

Where can i find any info on how and why TQuery.Locate() works better on indexed columns? – Lectern

I don't think Locate uses database index. In my case locating a record in database with 800k records take 5 - 10 seconds. If i locate record by using where clausule in SQL command, execution is very fast (under 1 second). Another effect of using Locate is that memory usage of my application rise over 1 GB. Maybe Locate uses index but in a very inefficient way - it seems to me it is creating index during first call (instead of using database one). Subsequent calls are much more faster. – Gutta

Answer 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.

Laylalayman

How custom search routines on the index field will be faster than db's? – Folkway

Inside ReportBuilder, you don't access to pure Delphi code like record arrays, but only to a sub-Delphi (and slow) scripting engine. – Haerle

Actually it is the other way round with us ;-) The reporttemplate is created designtime, everything else (Dataviews, pipelines, tables, ...) is created in runtime Delphi – Lectern

@Lectern Why do you create those on runtime? – Folkway

@Folkway It's not relavant to the question i asked here – Lectern

Answer 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;

Haerle

The reportbuilder report is 100% generated in Delphi code at this stage, using a design time created reporttemplate. The dataviews and pipelines used by the template are generated runtime. – Lectern