First-row, All-rows

All-rows

The optimization_goal option controls whether SQL Anywhere optimizes SQL data manipulation language (DML) statements for response time or total resource consumption.

If the option is set to All-rows (the default), then SQL Anywhere optimizes a query 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 Sybase PowerBuilder DataWindow applications. A setting of All-rows is also appropriate for insensitive (ODBC static) cursors since the entire result is materialized when the cursor is opened. It may also be appropriate for scroll (ODBC keyset-driven) cursors, since the intent of such a cursor is to permit scrolling through the result set.

If the option is set to First-row, SQL 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 SQL Anywhere optimizer will typically avoid, if possible, access plans that require the materialization of results to reduce the time to return the first row. With this setting, 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.

For more information about using the FASTFIRSTROW table hint, see FROM clause.

You can override any temporary or public settings for this option within individual INSERT, UPDATE, DELETE, SELECT, UNION, EXCEPT, and INTERSECT statements by including an OPTION clause in the statement.

• INSERT statement
• UPDATE statement
• DELETE statement
• SELECT statement
• UNION statement
• EXCEPT statement
• INTERSECT statement
• optimization_goal connection property