SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

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 290)
• “Understanding the Implications of Concurrency” (page 290)
• “Keeping Statistics Current” (page 294)
• “Managing SQL/MX Buffer Space” (page 294)
• “Managing DP2 Data Cache Memory Size” (page 300)
• “Maximizing Disk Process Prefetch Capabilities” (page 301)
• “Optimizing SQL/MX Memory Management” (page 301)
• “Optimizing Index Use” (page 302)
• “Performing FUP RELOADs to Generate More Accurate Query Plans” (page 303)
• “Checking Data Integrity” (page 303)
• “Creating Logical Views of Data” (page 304)
• “Adding and Dropping Partitions” (page 304)
• “Avoiding Automatic Recompilations” (page 304)
• “Matching Block Split Operation to Table Usage” (page 305)
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.
290 Enhancing SQL/MX Database Performance