SQL/MP Installation and Management Guide

HP NonStop SQL/MP Installation and Management Guide—523353-004
14-1
14 Enhancing Performance
The initial step in achieving maximum performance is providing 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 SQL/MP environment after it is in use.
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. You specify queries implicitly by using UPDATE,
INSERT/SELECT, and DELETE statements.
The number and type of queries used in an SQL/MP 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/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 depends on the purpose of the
access, on the access modes in effect, and on whether virtual sequential block
buffering (VSBB) is used for the access.
SQL/MP provides concurrent database access for most operations. Control of
concurrent access is obtained by using access options, locking options, and (for some
DDL operations) the WITH SHARED ACCESS option. These operations can be long-
running and are thus subject to contention:
•
Creating an index
°
When using the WITH SHARED ACCESS option, CREATE INDEX allows
concurrent access by DML statements throughout the entire operation except
for the short commit phase of the operation. To maximize concurrent access
during index creation, specify the WITH SHARED ACCESS option in your
CREATE INDEX statement.
°
Without the WITH SHARED ACCESS option, CREATE INDEX 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. This is the preferred method if you wish
to complete the index creation operation as soon as possible and users do not
require concurrent access to the data.