SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-18
Access Option
When using BROWSE ACCESS in SQLCI for a query that might run for a long
time, disable transaction generation before starting the query. By doing so, you
prevent the lengthy execution of an automatically generated transaction.
To disable transaction generation for user-defined DDL or DML statements,
execute this statement:
SET AUTOWORK OFF ;
See the SQL/MP Reference Manual for information on this statement.
•
To encourage parallel execution of queries:
°
Use the BROWSE ACCESS or REPEATABLE ACCESS option instead of
STABLE ACCESS. The BROWSE and REPEATABLE options typically allow
parallel execution when the STABLE option does not (unless you specify a
table lock).
°
For nonaudited tables, use the BROWSE ACCESS option whenever possible.
°
For audited tables, use the BROWSE ACCESS, STABLE ACCESS with table
locking or REPEATABLE ACCESS option whenever possible.
•
For interactive queries that access large result sets, avoid the use of
REPEATABLE ACCESS because of potential contention and delays.
•
Avoid the use of REPEATABLE ACCESS when locking a range of rows. SQL locks
all rows in the entire range, and no other transaction can access, delete, or insert
rows within that range. Instead, specify shared access or specify only small ranges
of rows and use multiple open cursors.
•
Consider specifying REPEATABLE ACCESS for multistep updates or deletes.
•
For single SELECT statements followed by an update operation, request
REPEATABLE ACCESS IN EXCLUSIVE MODE. This strategy avoids lock
escalation from shared to exclusive and avoids possible deadlocks in those
situations.
This example retrieves information about employees from the EMPLOYEE table.
Because browse access is specified, no locks are held while the query is processed.
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY > 50000
BROWSE ACCESS ;