Asynchronous processing of federated queries - examples

Examples of queries with a union and with a merge join illustrate the difference between query operations with and without asynchronous processing.

Example: Query with a union operation

A simple query performs a union operation on data from three different data sources. The computation required to generate the data on each data source is time-consuming. The access plan looks like this:

               RETURN   
                 |
               UNION
             /   |   \
          SHIP SHIP SHIP    
           |     |    |
         Site1 Site2 Site3

Without asynchrony, the union operation reads data from the query branches one branch at a time, from left to right. When the data from the Site1 server is read, Site2 server and Site3 server are idle. For this example, each branch of the union takes about two hours to return the result rows from one site. The total execution time of the three branches is approximately the sum of the time it takes to process each branch, in this example, about six hours.

With asynchrony, each branch of the union starts to process at the same time, and the three remote servers are active concurrently. The run time of the query is roughly equivalent to the run time of the slowest branch of the union. In this example, the run time is reduced to approximately two hours (about 66% faster) in comparison to six hours without asynchrony.

Example: Query with a merge join operation

A query that joins data from two different data sources uses a merge join operation (MSJOIN). The optimizer access plan looks like this:

              RETURN   
                 |
              MSJOIN
              /    \
            SCAN  FILTER
             |      |
            SORT   SCAN
             |      |
            SHIP   SORT   
             |      |           
           Site1   SHIP
                    |
                   Site2

Without asynchrony, the merge join operator first processes the outer (left) branch and does not process the inner (right) branch until the left branch starts to return rows. For this example, each branch executes a complex query and therefore takes a long time to execute. The approximate total time to execute the merge join is the sum of the time it takes to execute each branch.

With asynchrony, both branches of the merge join start at the same time, thus reducing the overall execution time of the query.