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

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-2
Using Queries in an SQL/MX Database
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 information on enhancing SQL/MP database performance, see the SQL/MP
Installation and Management Guide. For 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.
For more information about concurrency for NonStop SQL/MX, see the SQL/MX
Reference Manual.
These online operations can be long-running and so are subject to contention:
•
Moving a partition
°
When using the WITH SHARED ACCESS option, MODIFY TABLE and
MODIFY INDEX allow concurrent access by DML statements throughout the
entire operation except for the short commit phase of the operation. To
maximize concurrent access while moving a range partition, specify the WITH
SHARED ACCESS option in MODIFY.
°
Without the WITH SHARED ACCESS option, MODIFY allows concurrent
access by DML statements that use SELECT with BROWSE or SHARED
access during an initial scan phase, but locks out DML accesses during the
Note. The WITH SHARED ACCESS option of MODIFY is supported only for
range-partitioned objects and only when the partitioning key is a prefix of the clustering key.