As an option to the SELECT statement, the TOP n option automatically restricts the output of queries to a certain number of rows. This option provides a fast way to get a small sample of the data from a table without having to scan the entire table. For example, a user may want to examine the data in an Orders table by browsing through only 10 rows from that table.
The value of n can be passed into the operator by means of a macro, stored procedure, or USING request modifier parameter.
Performance Optimizations
TOP n option is optimized for handling TOP n and ANY n requests. Optimizations include:
- Adding an AMP runtime optimization for TOP n PERCENT operations.
- Extending ANY n optimization to INSERT ... SELECT and CREATE TABLE AS requests, views, and derived tables for all values of n.
- Adding an optimization that avoids redistributing the rows for the hash partitioning case when the grouping columns of a window function contain the PI columns of the source relation.
- Adding a RankLimit optimization for a TOP n operation that does not specify the WITH TIES option.
- Adding runtime optimizations for TOP n in a request that specifies an ORDER BY specification.
TOP n Option Performance Considerations
For best performance, use the TOP n option instead of the QUALIFY clause with RANK or ROW_NUMBER.
- In best cases, the TOP n option provides better performance.
- In worse cases, the TOP n option provides equivalent performance.
If a SELECT statement using the TOP n option does not also specify an ORDER BY clause, the performance of the SELECT statement is better with BTEQ than with FastExport.