ASA SQL User's Guide
Monitoring and Improving Performance
Top performance tips
The OPTIMIZATION_GOAL option controls whether Adaptive Server Anywhere optimizes SQL statements for response time (first-row) or for total resource consumption (all-rows). In simpler terms, you can pick whether to optimize query processing towards returning the first row quickly, or towards minimizing the cost of returning the complete result set.
If the option is set to first-row, Adaptive Server Anywhere chooses an access plan that is intended to reduce the time to fetch the first row of the query's result, possibly at the expense of total retrieval time. In particular, the Adaptive Server Anywhere optimizer will typically avoid, if possible, access plans that require the materialization of results in order to reduce the time to return the first row. With this setting, for example, the optimizer favors access plans that utilize an index to satisfy a query's ORDER BY clause, rather than plans that require an explicit sorting operation.
You can use the FASTFIRSTROW table hint in a query's FROM clause to set the optimization goal for a specific query to first-row, without having to change the OPTIMIZATION_GOAL setting.
If the option is set to all-rows (the default), then Adaptive Server Anywhere optimizes a query so as to choose an access plan with the minimal estimated total retrieval time. Setting OPTIMIZATION_GOAL to all-rows may be appropriate for applications that intend to process the entire result set, such as PowerBuilder DataWindow applications.