To understand the optimization of a query, you need to understand how the query accesses database objects, the sizes of the objects, and the indexes on the tables to determine whether it is possible to improve the query’s performance.
Some symptoms of optimization problems are:
A query runs more slowly than you expect, based on indexes and table size.
A query runs more slowly than similar queries.
A query suddenly starts running more slowly than usual.
A query processed within a stored procedure takes longer than when it is processed as an ad hoc statement.
The query plan shows the use of a table scan when you expect it to use an index.
Some sources of optimization problems are:
Statistics have not been updated recently, so the actual data distribution does not match the values used by Adaptive Server to optimize queries.
The rows to be referenced by a given transaction do not fit the pattern reflected by the index statistics.
An index is being used to access a large portion of the table.
where clauses are written in a form that cannot be optimized.
No appropriate index exists for a critical query.
A stored procedure was compiled before significant changes to the underlying tables were performed.
