Improving query performance
Go Up to Working with Queries
Following are steps you can take to improve query execution speed:
- Set the
TIBQuerycomponent’sUniDirectionalproperty toTrueif you do not need to navigate backward through a result set (SQL-92 does not, itself, permit backward navigation through a result set). - Prepare the query before execution. This is especially helpful when you plan to execute a single query several times. You need only prepare the query once, before its first use. For more information about query preparation, see Preparing a query.
Disabling bi-directional cursors
The UniDirectional property determines whether or not bi-directional cursors are enabled for a TIBQuery component. When a query returns a result set, it also receives a cursor, or pointer to the first record in that result set. The record pointed to by the cursor is the currently active record. The current record is the one whose field values are displayed in data-aware components associated with the result set’s data source.
UniDirectional is False by default, meaning that the cursor for a result set can navigate both forward and backward through its records. Bi-directional cursor support requires some additional processing overhead, and can slow some queries. To improve query performance, you may be able to set UniDirectional to True, restricting a cursor to forward movement through a result set.
If you do not need to be able to navigate backward through a result set, you can set UniDirectional to True for a query. Set UniDirectional before preparing and executing a query. The following code illustrates setting UniDirectional prior to preparing and executing a query:
if not (CustomerQuery.Prepared) then begin
CustomerQuery.UniDirectional := True;
CustomerQuery.Prepare;
end;
CustomerQuery.Open; { returns a result set with a one-way cursor }