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.

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.