SQL/MP Query Guide
Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-54
Avoiding Full Table Scans
Avoiding Full Table Scans
Scans of an entire table can be quite costly in terms of performance. Response time is
directly proportional to the number of rows or blocks processed. In general, you should
avoid online transaction processing queries that invoke full table scans unless the table
is quite small, consisting of only a few blocks.
To avoid table scans, do the following:
•
Provide a starting position by using a >= predicate on a key column, as described
in Positioning With Key Predicates on page 3-16.
•
Include in the select list only those columns that appear in an index and primary
key.
An alternate option that requires system management knowledge is to define a new
index that includes necessary columns.
You can check for full table scans using DISPLAY STATISTICS or the EXPLAIN utility.
For details, see Section 6, Analyzing Query Performance.
Minimizing Sort Costs for Ordering and
Grouping Operations
A sort might be needed when a query specifies ordering or grouping options. Because
sorting can require significant overhead, the optimizer attempts to select strategies that
minimize the number of sorts.
You cannot control the initiation of the sort process; however, by understanding how to
formulate queries that can minimize sorts, you can improve the performance of the
queries. To eliminate sorts or minimize sort requests, use these guidelines:
•
Specify these clauses in your queries only when you really need them:
°
The ORDER BY clause (to present the result in a certain order)
°
The GROUP BY clause (to group rows)
°
The DISTINCT clause (to eliminate duplicate rows)
•
Use keys or index columns in your queries where possible.
Use them in the order defined in the database. If you are not sure which columns
are keys or indexes, check with your database administrator. SQL does not use
keys or indexes if the columns are not in the same order as defined in the
database.
If you are not sure which columns are key or index columns, you can ask your
database administrator. Alternatively, you can get this information by using the FUP
INFO tablename, DETAIL command, which returns the column numbers used in