Access plans optimized for asynchrony

The optimizer can make the remote operations that the SHIP or RPD operator defines execute asynchronously.

In an asynchronous operation, a table queue (TQ) operator is inserted directly above the SHIP or RPD operator in the execution plan. The TQ operator defines a portion of the plan, called a subplan. A separate process or thread, with its own memory, runs the subplan. A subplan initiates immediately when the query starts.

You can think of the TQ operator as a pipe between the SHIP or RPD operator (producer of data) and the operator above it (consumer of data) in the plan. This pipe decouples the execution of the SHIP in the subplan below it from the main plan, and allows the asynchronous exchange of data between the two plan sections.

A TQ operator that appears directly above a SHIP or RPD operator in the plan enables the remote operations that the SHIP or RPD operator define to initiate at the beginning of the query and to deliver results to the federated server asynchronously. When asynchrony is beneficial for a given remote operation, the optimizer places a TQ operator directly above the corresponding SHIP or RPD operator in the plan.

TQ operators occur in execution plans for different purposes. A TQ operator usually denotes parallel operations in partitioned databases or in databases enabled for intrapartition parallelism. Another type of TQ operator, that enables asynchronous execution of a subplan, is called an asynchrony TQ (ATQ).

The optimizer makes a given SHIP or RPD operator asynchronous when:
  • Query performance will improve
  • The number of ATQs is below the per-server and per-query limits
  • The operator is not already asynchronous due to the use of another optimization technique
  • Restrictions on asynchrony are not violated.
  • The semantics of the query do not change