The fastest way to find a record in a TQuery result set

Question

I am wondering what the best (read: fastest) way is to find the entry in the result set of the TQUery SQL statement.

So far, I am using TQuery.Locate, and if I'm right, this is the only statement that can be used to search in a result set. So how can we optimize this?

I have several ideas, but have not yet managed to compare them all on large data sets:

Let's 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 find the record by its identifier in the result set

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

Any ideas on this?

[Edit] To explain how to use it: the query is executed using the Dataview Reportbuilder, and then is accessible through the datapipeline (this is TQuery.Dataset). In a custom report, I need to go down the pipeline based on a higher level identifier. Therefore, DO NOT use the query here is not applicable. I'm just wondering if any of my suggestions will speed up above.

Answer 1

Here are some tips you might find helpful.

• Use Locate () method only for indexed columns
• Use "order" ONLY for indexed columns
• Use the provisioning command before opening a query.
• Use DisableControls / EnableControls
• Use an up / down index (or both) depending on your needs.
• Try FastReports

If you have a master / detailing of large data sets, DO NOT upload a record of all the details - in other words, do not use the local details of the wizard - let the database engine give you only the records you need.

Answer 2

If you need to do this very often in a large data set, you better convert the data set to an array of records and implement some custom search procedures in the index field.

Answer 3

You must add an identifier to search in order to get only the expected string.

In the ReportBuilder Data view, go to the query designer, then to the "Search" tab, then add a field parameter with the option "Auto search".

Then go to the CalcBuilder Calc view, select "View / Events" from the menu. Click on the root element "Report" in the tree "Report Object", then double-click "OnGetAutoSearchValues" and get 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;