SQL/MP Query Guide

HP NonStop SQL/MP Query Guide—524488-003
3-1
3
Improving Query Performance
Through Query Design
You can formulate the same NonStop SQL/MP query in a number of different ways.
Some formulations perform better than others because SQL requires less work to
return the same result.
This section describes how to write queries so that they capitalize on SQL performance
features.
The guidelines in this section focus on individual SQL statements. Topics include:
•
Selecting Columns for Faster Data Access on page 3-2
•
Preparing Queries on page 3-3
•
How the Optimizer Processes Predicates on page 3-4
•
Writing Efficient Predicates on page 3-15
•
How the Optimizer Processes Join Operations on page 3-24
•
Writing Efficient Joins on page 3-39
•
How the Optimizer Processes Aggregates and Group-By Operations on page 3-46
•
Optimizing Subqueries on page 3-51
•
Avoiding Full Table Scans on page 3-54
•
Minimizing Sort Costs for Ordering and Grouping Operations on page 3-54
•
Writing Efficient Programmatic Statements on page 3-60
•
Decision Support Considerations on page 3-61
•
Online Transaction Processing Considerations on page 3-62
•
Batch Considerations on page 3-63
Although the focus of this section is on practical, useful guidelines, the predicate-
optimization information describes how the optimizer performs its underlying
operations. Therefore, portions of this section require a basic understanding of the
optimizer, executor, file system, and disk processes as presented in Section 2, The
Optimizer.
The premise of this section is that the physical database design process produced an
efficient access path to the data; if such a path does not exist, the design cycle should
be revisited. For more information, see the SQL/MP Installation and Management
Guide.