SORT Operator Performance Improvement

SAP ASE version 16.0 and later improves the performance on certain parallel queries that include SORT operators.

To benefit from the changes, the parallel query must include:

A SORT operator that appears on top of the Exchange operator
Exchange operator must be a Replicated Exchange with a single producer and multiple consumers.

If the query does not meet this criteria, the query processor uses the standard SORT operator included with SAP ASE versions earlier than 16.0 (also called the DEFAULT sort).

This example is from a version of SAP ASE earlier than 16.0, and does not include the SORT operator changes:

|   |   |   |   |MERGE JOIN Operator (Join Type: Inner Join) (VA = 5)
|   |   |   |   | Using Worktable2 for internal storage.
|   |   |   |   |  Key Count: 1
|   |   |   |   |  Key Ordering: ASC
|   |   |   |   |
|   |   |   |   |   |SORT  Operator (VA = 3)
|   |   |   |   |   | Using Worktable1 for internal storage.
|   |   |   |   |   |
|   |   |   |   |   |   |EXCHANGE Operator (VA = 2) (Replicated)
|   |   |   |   |   |   |Executed in parallel by 1 Producer and 8 Consumer processes.
|   |   |   |   |   |   |
|   |   |   |   |   |   |   |EXCHANGE:EMIT Operator (VA = 1)
|   |   |   |   |   |   |   |
|   |   |   |   |   |   |   |   |SCAN Operator (VA = 0)
|   |   |   |   |   |   |   |   |  FROM TABLE
|   |   |   |   |   |   |   |   |  t2
|   |   |   |   |   |   |   |   |  Table Scan.
|   |   |   |   |   |   |   |   |  Forward Scan.
|   |   |   |   |   |   |   |   |  Positioning at start of table.
|   |   |   |   |   |   |   |   |  Using I/O Size 16 Kbytes for data pages.
|   |   |   |   |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

However, SAP ASE version 16.0 and later moves the build part of the SORT operator below the Exchange Operator and keeps the sort-table reading part above the Exchange, which results in this query execution plan for the same query

|   |   |   |   |MERGE JOIN Operator (Join Type: Inner Join) (VA = 6)
|   |   |   |   | Using Worktable3 for internal storage.
|   |   |   |   |  Key Count: 1
|   |   |   |   |  Key Ordering: ASC
|   |   |   |   |
|   |   |   |   |   |SORT (GETSORTED) Operator (VA = 4)
|   |   |   |   |   | Using Worktable2 for internal storage.
|   |   |   |   |   |
|   |   |   |   |   |   |EXCHANGE Operator (VA = 3) (Replicated)
|   |   |   |   |   |   |Executed in parallel by 1 Producer and 8 Consumer processes.
|   |   |   |   |   |   |
|   |   |   |   |   |   |   |EXCHANGE:EMIT Operator (VA = 2)
|   |   |   |   |   |   |   |
|   |   |   |   |   |   |   |   |SORT (SORTBUILD) Operator (VA = 1)
|   |   |   |   |   |   |   |   | Using Worktable1 for internal storage.
|   |   |   |   |   |   |   |   |
|   |   |   |   |   |   |   |   |   |SCAN Operator (VA = 0)
|   |   |   |   |   |   |   |   |   |  FROM TABLE
|   |   |   |   |   |   |   |   |   |  t2
|   |   |   |   |   |   |   |   |   |  Table Scan.
|   |   |   |   |   |   |   |   |   |  Forward Scan.
|   |   |   |   |   |   |   |   |   |  Positioning at start of table.
|   |   |   |   |   |   |   |   |   |  Using I/O Size 16 Kbytes for data pages.
|   |   |   |   |   |   |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

The benefits of the new parallel SORT operator are that it:

Splits the SORT operator into two operators: SORTBUILD and GETSORTED. A single sort table built by the SORTBUILD is used by multiple instances of GETSORTED for a parallel reading of sorted rows, reducing the I/Os. The DEFAULT SORT operator plan creates multiple copies of SortTables (8 Consumer processes in the first example).
Sends a single row containing meta data from the SORTBUILD operator to multiple instances of GETSORTED operator, providing a reference for accessing the sort-table directly, which avoids copying and propagating many rows via Exchange.

The parallel SORT operator includes these restrictions:

Does not support Repartitioned Exchange. However, SORT operators that occur above Repartitioned Exchange use the DEFAULT sort in the query execution plan.
Although abstract plans do not explicitly support forcing GETSORTED and SORTBUILD, you can force these plans by making sure the SORT operator appears above the Replicated Exchange.
SAP ASE implicitly disables dynamic thread assignment (DTA) for the query execution plan that contains GETSORTED and SORTBUILD operators.