SQL/MP Query Guide

HP NonStop SQL/MP Query Guide—524488-003
4-1
4
Improving Query Performance With
Environmental Options
The best way to control your NonStop SQL/MP processing environment is to design
and maintain your database so that the mix of queries executes efficiently. Beyond
structural design and ongoing maintenance, however, there are several environmental
factors, discussed in this section, that can influence query performance:
•
Keeping Statistics Current on page 4-2
•
Optimizing the Access Path on page 4-4
•
Requesting Parallel Processing on page 4-13
•
Specifying Access Option and Lock Characteristics on page 4-16
•
Reducing Messages With Buffering Options on page 4-21
•
Controlling the Opening of Tables, Views, and Indexes on page 4-29
•
Controlling the Number of Key Columns Used by MDAM on page 4-30
•
Controlling MDAM’s Use of DENSE or SPARSE Algorithms on page 4-30
•
Controlling the Creation of NonStop SQL/MP Processes on page 4-31
•
Enhancing Sort Performance on page 4-32
•
Understanding Concurrency on page 4-32
•
Minimizing Overhead of Query Programs on page 4-33
For information about how to optimize specific queries, see Section 3, Improving Query
Performance Through Query Design.
The SQL/MP Installation and Management Guide contains information about topics
related to the performance of the database as a whole. Topics include:
•
Defining columns for optimal access
•
Creating alternate indexes
•
Maximizing parallel index maintenance
•
Partitioning data
•
Specifying cache buffer size
•
Maximizing disk prefetch capabilities
Note. This manual supports NonStop SQL/MP D30.02 and D30.03. Information that describes
how the optimizer chooses a query execution plan can change from release to release.