SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-10
Optimizing Index Use
kernel-managed swap file use, how to monitor swap file usage by process, and how to
change your KMSF configuration.
To identify and avoid possible memory contention between either the application or the
master executor and other system components in process space such as QIO, see the
discussion on configuring the QIO subsystem in the QIO Configuration and
Management Manual.
Optimizing Index Use
An index on a table provides an alternate access path that differs from the inherent
access path (clustering key). Indexes improve application performance for data
retrieval operations. When compiling a statement, the SQL/MX optimizer selects the
execution plan for a statement by choosing the best access path to the data. If an
index exists, the SQL/MX optimizer evaluates using the index.
Indexes can also improve performance by eliminating the need for the disk process to
access the underlying table. If the query can be satisfied by the columns contained in
the index, the underlying table will probably not be accessed.
When evaluating whether to use an index or a table scan, NonStop SQL/MX compares
the number of base table scan I/Os and the I/Os for index access. The use of
sequential cache for a scan increases the performance of the scan and increases the
likelihood of its use.
Index-only access is faster than a table scan. A sort prevented by index access must
be looked at closely, however, because the cost of a scan plus a sort might be less
than the cost of index and base table access. For more information about selectivity
and cost, see the SQL/MX Query Guide.
For more information about determining when to use indexes, see Section 4,
Understanding and Planning SQL/MX Tables.
Maximizing Parallel Index Maintenance
Indexes are automatically updated whenever you insert a row into the underlying table
or whenever you change a column of the index. You can update multiple indexes in
parallel.
NonStop SQL/MX performs index maintenance on multiple rows (not individual rows,
as in NonStop SQL/MP), making the process faster but also allowing indexes and their
tables to temporarily diverge by multiple rows. The consequences are:
•
READ UNCOMMITTED ACCESS queries might obtain different data, depending
on the access path.
•
Errors that occur during index maintenance might require a more complicated
cleanup of the index.