Hash Join Operator Performance Improvement

SAP ASE versions 16.0 and later implement performance improvements and a reduction in resource usage for certain parallel query plans involving HASH JOIN operators.

A HASH JOIN operation includes two steps:

Building a hash table containing the joining columns of the outer stream of rows to be joined, and
Probing the hash table with the joining column values of each of the rows in the inner stream to be joined

SAP ASE 16.0 replaces the HASH JOIN operator with the HASH PROBE and the HASH BUILD operators, and includes the replicated EXCHANGE operator between these operators. The HASH BUILD operator builds the hash table and the HASH PROBE operator reads the inner stream and probes the hash table to find matching rows (in earlier releases of SAP ASE, the HASH JOIN operator performed both these steps).

A single worker thread executes the HASH BUILD operator, building a single hash table. The query engine passes this hash table through memory pipes to all producers executing the HASH PROBE operator. These producers share this hash table and probe it for matches to the joining columns in their inner streams. Earlier releases of SAP ASE required multiple producers to execute the HASH JOIN operator.

This is an example of showplan output for a query plan using the HASH JOIN operator:

|
|   |EXCHANGE Operator (VA = 6) (Merged)
|   |Executed in parallel by 4 Producer and 1 Consumer processes.

|   |   
|   |   |EXCHANGE:EMIT Operator (VA = 5)
|   |   |   
|   |   |   |HASH JOIN Operator (VA = 4) (Join Type: Inner Join)
|   |   |   | Using Worktable1 for internal storage.
|   |   |   |  Key Count: 1
|   |   |   |   
|   |   |   |   |EXCHANGE Operator (VA = 2) (Replicated)
|   |   |   |   |Executed in parallel by 1 Producer and 4 Consumer processes.

|   |   |   |   |   
|   |   |   |   |   |EXCHANGE:EMIT Operator (VA = 1)
|   |   |   |   |   |

A query plan must include these attributes for the query processor to use the HASH JOIN improvements:

The query processor executes the HASH JOIN operator (VA = 4) in parallel (the example above includes four Producers in the EXCHANGE Operator (VA = 6).
The left child Operator of the HASH JOIN Operator is a Replicated EXCHANGE Operator (VA = 2) with a single Producer

If the query does not meet this criteria, the query processor uses the single HASH JOIN operator.

The benefits of the SAP ASE 16.0 query plan are that it:

Avoids the performance degradation caused by copying all of the rows through the memory pipes to the producers executing the HASH JOIN operator. Instead, the query engine passes a single row containing a reference to the hash table built by the HASH PROBE operator through the pipes.
Does not require the extra memory and disk resources needed by each producer to build its own hash table. Instead, it shares a single hash table.

This is an example of showplan output for query plans using the improved hash join processing:

|
|   |EXCHANGE Operator (VA = 7) (Merged)
|   |Executed in parallel by 4 Producer and 1 Consumer processes.

|   |   
|   |   |EXCHANGE:EMIT Operator (VA = 6)
|   |   |   
|   |   |   |HASH JOIN PROBE Operator (VA = 5) (Join Type: Inner Join)
|   |   |   | Using Worktable2 for internal storage.
|   |   |   |  Key Count: 1
|   |   |   |   
|   |   |   |   |EXCHANGE Operator (VA = 3) (Replicated)
|   |   |   |   |Executed in parallel by 1 Producer and 4 Consumer processes.

|   |   |   |   |   
|   |   |   |   |   |EXCHANGE:EMIT Operator (VA = 2)
|   |   |   |   |   |   
|   |   |   |   |   |   |HASH JOIN BUILD Operator (VA = 1) (Join Type: Inner Join)
|   |   |   |   |   |   | Using Worktable1 for internal storage.
|   |   |   |   |   |   |  Key Count: 1
|   |   |   |   |   |   |