TOP_NSORT_CUTOFF_PAGES option

Sets the result size threshold for TOP N algorithm selection.

1 – 1000

1

The TOP_NSORT_CUTOFF_PAGES option sets the threshold, measured in pages, where evaluation of a query that contains both a TOP clause and ORDER BY clause switches algorithms from ordered list-based processing to sort-based processing. Ordered list processing performs better in cases where the TOP N value is smaller than the number of result rows. Sort-based processing performs better for large TOP N values.

In some cases, increasing TOP_NSORT_CUTOFF_PAGES can improve performance by avoiding sort-based processing.