SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-2
Selecting Columns for Faster Data Access
For information about database management options and directives that can influence
query performance, see Section 4, Improving Query Performance With Environmental
Options. Several management options can be used interactively from SQLCI as well as
from a program.
Selecting Columns for Faster Data Access
When requesting specific columns within a row, these guidelines can help you write
queries that can be processed efficiently with minimal message overhead:
•
Specify only columns that you really need. Avoid the use of SELECT *, which can
disable efficient access mechanisms such as virtual sequential block buffering
(VSBB, described in Section 4, Improving Query Performance With Environmental
Options) and index-only access.
•
Avoid selecting columns you already have values for (such as those having equal
predicates to host variables). Column selection can influence message size; it can
also influence whether the optimizer chooses sequential block buffering.
•
If you specify most of a leftmost group of contiguous columns in your query,
consider adding remaining columns so that the SELECT specifies contiguous
columns. This can allow efficient bulk move operations, but might, however,
disable index-only access; check EXPLAIN output to compare resulting plans.
•
If you retrieve a set of column values to accumulate a total, and could perform the
operation with aggregate functions, change the queries so that aggregates perform
the operation. As of SQL/MP 2.0, this change reduces message cost because the
evaluation is performed at the disk process level when possible. For more
information, see How the Optimizer Processes Aggregates and Group-By
Operations on page 3-46.
•
For complex queries or difficult problems, consider using multiple step queries, left
outer join, and UNION as possible alternatives. Try to break up the query into
smaller steps that use keys and combine smaller result sets.
•
To avoid sorts, specify DISTINCT only for matching leftmost columns of an index.
Column definition is also important for good performance. To learn how to define
columns for maximum performance, see the SQL/MP Installation and Management
Guide.
When writing queries, a smaller number of selected columns generally results in a
smaller message size—or fewer blocks moved—between the file system and the disk
process. For example, this query retrieves a list of employees from the EMPLOYEE
table in the sample database:
SELECT * FROM EMPLOYEE;
Note. This manual supports NonStop SQL/MP D30.02 and D30.03. Information that describes
how the NonStop SQL/MP optimizer chooses a query execution plan can change from release
to release.