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

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-2
Understanding the Implications of Concurrency
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
remainder of the operation. Use this method if you want to complete the
partition move as soon as possible and if users do not require concurrent
access to the data.
•
Creating a constraint
°
When you create a CHECK or NOT NULL constraint, NonStop SQL/MX
confirms that the column data in all table rows complies with, and does not
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.