Structuring the Indexes

Structuring the Indexes
=======================

Often, there is a beneficial impact on performance by restructuring indexes. This can
involve the following:
■ Remove nonselective indexes to speed the DML.
■ Index performance-critical access paths.
■ Consider reordering columns in existing concatenated indexes.
■ Add columns to the index to improve selectivity.

Do not use indexes as a panacea. Application developers sometimes think that
performance will improve if they create more indexes. If a single programmer creates
an appropriate index, then this might indeed improve the application's performance.
However, if 50 programmers each create an index, then application performance will
probably be hampered.


Modifying or Disabling Triggers and Constraints
=============================================

Using triggers consumes system resources. If you use too many triggers, then you can
find that performance is adversely affected and you might need to modify or disable
them.


Restructuring the Data
=======================
After restructuring the indexes and the statement, you can consider restructuring the
data.
■ Introduce derived values. Avoid GROUP BY in response-critical code.
■ Review your data design. Change the design of your system if it can improve performance.
■ Consider partitioning, if appropriate.


Maintaining Execution Plans Over Time
====================================
You can maintain the existing execution plan of SQL statements over time either using
stored statistics or stored SQL execution plans. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan (that is, plan stability) maintains the plan for a single SQL statement. If both statistics and a stored plan are available for a SQL statement, then the optimizer uses the stored plan.