Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 477 / 536

CHAPTER 12 Managing System Resources
457
• Join indexes typically cause join queries to execute faster than ad hoc
joins, at the expense of using more disk space. However, when a join query
does not reference the largest table in a multi-table join index, an ad hoc
join usually outperforms the join index.
• You can improve performance by using an additional column to store
frequently calculated results.
Planning queries
If you have created the right indexes, the Adaptive Server IQ query optimizer
can usually execute queries in the most efficient way—sometimes even if you
have not used the most effective syntax. Proper query design is still important,
however. When you plan your queries carefully, you can have a major impact
on the speed and appropriateness of results.
Before it executes any query, the Adaptive Server IQ query optimizer creates a
query plan. Adaptive Server IQ helps you evaluate queries by letting you
examine and influence the query plan, using the options described in the
sections that follow. For details of how to specify these options, see the
Adaptive Server IQ Reference Manual.
Query evaluation options
The following options can help you evaluate the query plan. All of these
options are
OFF
by default.
•
IQ_QUERY_PLAN_ONLY
When you set this option
ON
, the query
optimizer dumps the query plan into the log transaction file rather than
submitting it to the query engine.
•
QUERY_INFORMATION
When you set this option
ON
, Adaptive Server
IQ produces messages about queries. These include messages about using
join indexes, about the join order, and about join algorithms for the
queries.
•
QUERY_DETAIL
When you set this option
ON
, Adaptive Server IQ
displays additional information (as part of the
QUERY_INFORMATION
option) about the query when producing its query plan. When
QUERY_INFORMATION
is
OFF
(the default), this option is ignored.