SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-61
Decision Support Considerations
•
Conversely, when selecting and updating multiple rows, choose multiple updatable
cursor UPDATES and DELETES WHERE CURRENT over multiple single-row
selects followed by exact updates or deletes. The EXCLUSIVE and REPEATABLE
lock mode and access options are recommended for these types of operations. For
more information, see Section 4, Improving Query Performance With
Environmental Options.
•
Choose an INSERT from a SELECT instead of a SELECT followed by one or more
INSERTS; for example:
INSERT into INVNTRY
(SELECT part_no, qty FROM WRKNPROC
WHERE part_status = "FINISHED") ;
(Note, however, that if you update large numbers of rows, lock escalations or
exceeded lock limits might cause aborts of transactions. In such a case, consider
committing a transaction after a certain number of updates or specify ranges of
rows in multiple set updates.)
Decision Support Considerations
Decision Support Systems (DSS) are systems that provide users a means to retrieve
information from a large database and perform information analysis. These systems
require processing, summarizing, and aggregation of data for quick business
decisions. Requirements include fast processing of large amounts of data.
A typical DSS database consists of a very large table, with the actual temporal data of
the database, and several small tables that contain static information about the data.
Typical queries are joins of these small, static tables to the large database table.
For join operations, these guidelines apply:
•
If the joining columns are part of the primary key of the joining tables, a nested join
is typically the most efficient join method.
•
If the joining columns are not part of the primary key and alternate indexes are not
available, a hash join is the preferable method.
•
If the joining columns are part of the primary key of the tables to be joined and the
tables are joined in the same order as the columns in the primary key, a key-
sequenced merge join is the preferable method.
Features that support DSS include:
•
Hash joins. See Hash Join on page 3-29.
•
Key-sequenced merge joins. See Key-Sequenced Merge Join on page 3-27.
•
MDAM. See Transformation of Predicates on page 3-4.
•
Aggregate processing. See How the Optimizer Processes Aggregates and Group-
By Operations on page 3-46.