SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-62
Online Transaction Processing Considerations
•
Column selectivity. See Combinations of Predicates on page 5-4.
•
Query Rewrite. See Transformations Related to Joins on page 3-8.
•
Reduction of sorts. See Sort Operations on page 3-55.
•
In-memory sorts. See Sort Operations on page 3-55.
•
Hashed groupings. See Hashed Aggregation and Grouping on page 3-50.
•
CASE expressions. See Using String Functions on page 1-15.
•
String functions. See Using String Functions on page 1-15.
•
Effective optimizer plan selection. See How the Optimizer Chooses an Execution
Plan on page 2-3.
Online Transaction Processing Considerations
These guidelines apply to interactive queries:
•
Avoid processing large numbers of rows.
•
For small result sets, use CONTROL QUERY INTERACTIVE ACCESS ON,
described in Section 4, Improving Query Performance With Environmental Options.
This directive requests the optimizer to use an optimal index whenever possible
because the query requests only a few rows.
•
Specify SEQUENTIAL INSERT/UPDATE OFF. Sequential operations are usually
reserved for batch processing.
•
Avoid the use of REPEATABLE ACCESS because of potential contention and
delays. For more information, see Section 4, Improving Query Performance With
Environmental Options.
•
Consider specifying SEQUENTIAL READ OFF. For more information, see
Section 4, Improving Query Performance With Environmental Options
.
•
Do not use sequential cache unless tables are relatively small.
•
Depending on your concurrency requirements, consider using a CONTROL TABLE
LOCK directive. For more information, see Section 4, Improving Query
Performance With Environmental Options.