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

HP NonStop SQL/MX Installation and Management Guide—523723-004
16-1
16
Enhancing SQLMX Database
Performance
To achieve maximum performance, you must provide sufficient hardware to handle the
throughput and size of the application database.
In addition to hardware, many factors affect the performance of a database and
application. Some factors are system dependent, others are application dependent.
The factors discussed in this section are specific performance issues that can arise in
an installed and operating SQL/MX environment.
This section addresses these strategies for enhancing SQL/MX database performance:
•
Using Queries in an SQL/MX Database on page 16-1
•
Understanding the Implications of Concurrency on page 16-2
•
Keeping Statistics Current on page 16-7
•
Impact of Optimizer and UPDATE STATISTICS Enhancements on page 16-7
•
Using SQLMXBUFFER to Improve Database Performance on page 16-8
•
Optimizing SQL/MX Memory Management on page 16-9
•
Optimizing Index Use on page 16-10
•
Managing Cache Memory Size on page 16-11
•
Maximizing Disk Process Prefetch Capabilities on page 16-11
•
Performing FUP RELOADs to Generate More Accurate Query Plans on
page 16-12
•
Checking Data Integrity on page 16-13
•
Creating Logical Views of Data on page 16-14
•
Adding and Dropping Partitions on page 16-14
•
Avoiding Automatic Recompilations on page 16-14
•
Matching Block Split Operation to Table Usage on page 16-15
Using Queries in an SQL/MX Database
Queries are the basis of a relational database application. You specify queries
explicitly by using application-embedded SELECT and CURSOR statements, ad hoc
query requests, and report writer selections. Queries are implicitly specified through
UPDATE, INSERT SELECT, and DELETE statements.