SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-51
Sorted GROUP BY Operation
For parallel plans, SQL always uses hashed aggregation unless a previous version of
software is running that does not support hashed aggregations. Each ESP process
does its own aggregation and sends its results to the master ESP. This strategy
reduces network traffic by applying grouping and aggregation locally.
SQL does not use hashing for columns that use collations.
Sorted GROUP BY Operation
A sort is usually the least efficient method for processing aggregates and GROUP BY
operations; when using a sort, SQL sorts the entire table on the grouping columns, and
then evaluates the aggregate on the result of the sorted rows.
Optimizing Subqueries
In general, use joins instead of subqueries, as discussed in the preceding subsection.
When you do write subqueries, however, you should understand how the optimizer
evaluates subqueries, and use noncorrelated subqueries instead of correlated
subqueries whenever possible.
Correlated Subquery
Subqueries are usually evaluated before an outer query is executed. If, however, a
subquery cannot be executed independently of the outer query, as when the subquery
references values from the outer query, the subquery is executed for every qualifying
row of the outer query. This type of subquery is called a correlated subquery.
Consider this query, which attempts to select the names of all employees who are
designated as managers:
SELECT EMP_ID, FIRST_NAME, LAST_NAME, MGR_ID
FROM EMPLOYEE EMP1
WHERE EXISTS (SELECT MGR_ID
FROM EMPLOYEE EMP2
WHERE EMP2.MGR_ID = EMP1.EMP_ID) ;
The query returns this result: