ASA SQL User's Guide
Query Optimization and Execution
The Adaptive Server Anywhere optimizer must decide the order in which to access tables in a query, and whether or not to use an index for each table. The optimizer attempts to pick the best strategy.
The best strategy for executing each query is the one that gets the results in the shortest period of time, with the least cost. The optimizer determines the cost of each strategy by estimating the number of disk reads and writes required, and chooses the strategy with the lowest cost.
The optimizer uses a generic disk access cost model to differentiate the relative performance differences between random and sequential retrieval on the database file. It is possible to calibrate a database for a particular hardware configuration using an ALTER DATABASE statement. The particulars of a specific cost model can be determined with the sa_get_dtt() stored procedure.
By default, query processing is optimized towards returning the complete result set. You can change this, using the OPTIMIZATION_GOAL option, to minimize the cost of returning the first row quickly.
You can view the access plan for any query in Interactive SQL by opening the Plan tab in the Results pane. To change the degree of detail that is displayed, change the setting in the Plan tab of the Options dialog (available from the Tools menu).
For more information about optimization goals, see OPTIMIZATION_GOAL option [database].
For more information about reading access plans, see Reading access plans.
Optimizer estimates
Automatic performance tuning
Underlying assumptions
Access plan caching
Steps in optimization