How indexes affect performance

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:

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