Sybase 12.4.2 manual Setting query optimization options, 458

Improving your queries

•QUERY_TIMING This option controls the collection of timing statistics on subqueries and some other repetitive functions in the query engine. Normally it should be OFF because for very short correlated subqueries the cost of timing every subquery execution can be very expensive in terms of performance.

Setting query optimization options

By adjusting the following options you can influence the speed at which queries are processed.

• AGGREGATION_ALGORITHM_PREFERENCE Controls the choice of algorithms for processing an aggregate (GROUP BY, DISTINCT, SET functions). This option is designed primarily for internal use; do not use it unless you are an experienced database administrator. See the Adaptive Server IQ Reference Manual for details.

•AGGREGATION_CUTOFF Specifies at which precision level to use a more efficient internal storage type for SUM or AVG calculations. The default is 10. The internal storage type is slower, but avoids risking overflows.

•INDEX_PREFERENCE Sets the index to use for query processing. The Adaptive Server IQ optimizer normally chooses the best index available to process local WHERE clause predicates and other operations which can be done within an IQ index. This option is used to override the optimizer choice for testing purposes; under most circumstances it should not be changed.

• JOIN_ALGORITHM_PREFERENCE Controls the choice of algorithms when processing joins. This option is designed primarily for internal use; do not use it unless you are an experienced database administrator. See the Adaptive Server IQ Reference Manual for details.

•JOIN_OPTIMIZATION When this option is ON (the default), Adaptive Server IQ optimizes the join order to reduce the size of intermediate results and sorts and to balance the system load. When it is OFF, the join order is determined by the order of the tables in the FROM clause of the SELECT statement. (The left-most table becomes the outer table of the topmost join.) This option should be ON whenever queries are ad hoc and untried, when you don't know optimum join order for a multi-table join query, or when you cannot alter queries.

458