Sometimes the SQL retrieval performance can be improved by obtaining the retrieval results of only the first n rows. The performance improvement can be expected to increase as the number of retrieval result rows decreases.

When the retrieve first n records facility is used, only the first n rows from the beginning of the SQL retrieval results (or after the specified offset of the first row to return has been skipped) are accepted. In this case, the access path selected by the SQL optimization method changes. Consequently, the SQL retrieval performance may improve as described as follows:

• Fewer rows may need to be sorted because sort processing that targets all rows that satisfy the search conditions becomes unnecessary.
• The work tables that HiRDB creates exclusively for the ORDER BY clause may become unnecessary.
• The amount of communication between server processes can sometimes be reduced by having the server processes not read the rows that do not fall in the first n rows of the retrieval results.

To use the retrieve first n records facility, specify LIMIT. For details about LIMIT, see the manual HiRDB Version 8 SQL Reference.