Carefully considered indexes, built on top of a good database design, are the foundation of a high-performance Adaptive Server installation. However, adding indexes without proper analysis can reduce the overall performance of your system. Insert, update, and delete operations can take longer when a large number of indexes must be updated.
Analyze your application workload and create indexes as necessary to improve the performance of the most critical processes.
The Adaptive Server query optimizer analyzes the costs of possible query plans and chooses the plan that has the lowest estimated cost. Since much of the cost of executing a query consists of disk I/O, creating the correct indexes for your applications means that the optimizer can use indexes to:
Avoid table scans when accessing data
Target specific data pages that contain specific values in a point query
Establish upper and lower bounds for reading data in a range query
Avoid data page access completely, when an index covers a query
Use ordered data to avoid sorting data or to favor
the less costly ordered-input based JOIN, UNION, GROUP,
or DISTINCT operators over
other more expensive algorithms (for example, using merge joins
instead of nested-loop joins and so on).
For example, to select the best index for a join clause:
r.c1=s.c1 and ... r.cn=s.cn
Indexes on r or s that
have any subset of c1 ... cn as
a prefix avoid the sort on the side of the merge
join with the prefix.
You can use indexes on both sides of the and clause if they are compatible (that is, they have a nonempty common prefix covered by the equijoin clause. This common prefix determines the part of the equijoin clause used as a merge clause (the longer the merge clause, the more effective it is).
The query processor enumerates plans with an index on one side and a sort on the other. In the example above, the index prefix covered by the equijoin clause determines the part of the equijoin clause used as a merge clause (again, the longer the merge clause, the more effective it is).
You can use similar steps to identify the best index for union, distinct, and group clauses.
You can create indexes to enforce the uniqueness of data and to randomize the storage location of inserts.
You can set sp_chgattribute 'concurrency_opt_threshold' parameter to avoid table scans for increased concurrency. The syntax is:
sp_chgattribute table_name, "concurrency_opt_threshold", min_page_count
For example, this sets the concurrency optimization threshold for a table to 30 pages:
sp_chgattribute lookup_table, "concurrency_opt_threshold", 30
