Dynamic Thread Assignment

SAP ASE uses dynamic and static thread assignments.

Dynamic thread assignment allows SAP ASE to execute parallel query plans faster and with fewer resources than using static threads. SAP ASE applies dynamic thread assignment to parallel lava query plans that are generated for select queries. However, these commands use static thread assignment:

select into
reorg commands that include a data copy
alter table commands that include a data copy
create index

Dynamic thread assignment improves performance by:

Executing query plans in parallel with fewer threads in situations where static thread assignment requires recompilation into a serial query plan.
Executing dynamic load balancing between worker threads: When there are fewer threads executing the query plan than there are work units, the threads that execute the smallest work units complete their task more quickly, freeing themselves to execute the remaining available work units while the threads executing larger work units complete their tasks.
Using existing semantic partitioning in joins more effectively. Dynamic thread assignment allows a partition-to-partition join when joining two tables that are already partitioned on the joining column, as opposed to static thread assignment, which joins all the partitions of the outer table to all of the partitions of the inner table in one operation. Dynamic thread assignment allows a single worker thread to join the first partition of the outer table to the first partition of the inner table, then reexecutes the query plan fragment to join the second partition, and so on.

Dynamic thread assignment is enabled when you configure SAP ASE for parallelism with the number of worker processes and max parallel degree parameters. See System Administration Guide, Volume 1 > Setting Configuration Parameters.