SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

13 Enhancing SQL/MX 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” (page 293)
• “Understanding the Implications of Concurrency” (page 293)
• “Keeping Statistics Current” (page 297)
• “Managing SQL/MX Buffer Space” (page 297)
• “Managing DP2 Data Cache Memory Size” (page 303)
• “Maximizing Disk Process Prefetch Capabilities” (page 304)
• “Optimizing SQL/MX Memory Management” (page 304)
• “Optimizing Index Use” (page 305)
• “Performing FUP RELOADs to Generate More Accurate Query Plans” (page 306)
• “Checking Data Integrity” (page 306)
• “Creating Logical Views of Data” (page 307)
• “Adding and Dropping Partitions” (page 307)
• “Avoiding Automatic Recompilations” (page 307)
• “Matching Block Split Operation to Table Usage” (page 308)
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.
The number and type of queries used in an SQL/MX environment influence the performance of
the database. For a detailed discussion of how to formulate queries to improve query performance
while retrieving the desired output, see the SQL/MX Query Guide.
For more information on enhancing SQL/MP database performance, see the SQL/MP Installation
and Management Guide. For more information on how to formulate queries to improve query
performance in an SQL/MP environment, see the SQL/MP Query Guide.
Understanding the Implications of Concurrency
Concurrency is defined as access to the same data by two or more processes at the same time.
The degree of concurrency available—whether a process that requests access to data that is already
being accessed is given access or placed in a wait queue—depends on the purpose of the access
mode (read or update) and the isolation level.
NonStop SQL/MX provides concurrent database access for most operations. Control of concurrent
access is obtained by using access options, locking options, and the WITH SHARED ACCESS
option of MODIFY.
Using Queries in an SQL/MX Database 293