Filter
Top Products
Improving your queries
458
• 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.