P A R A L L E L U N I V E R S E Technology White Paper - Parallel Network Query (Distributed Query) 1. Network Query Network Query joins local tables of the server with remote tables of other servers in the network. (remote tables represented as Federated tables) select * from , # 1st group of tables , # 2nd group of tables . . . < local tables @ base server > # Last group of tables where query_condition; 2. Current Technology Server Diagram --------- --------- --------- | | | | | | | Storage | | Storage | | Storage | | | | | | | --------- --------- --------- | | | | 1st group | 2nd group | Last group | of tables | of tables | of tables v v v --------- --------- --------- |Auxiliary| |Auxiliary| | Base | | Server | | Server | . . . | Server | | 1 | | 2 | | | --------- --------- --------- | | ^ ^ | | | | | | | | | | | Query ------------------------------------- -------- | | Result Remote Tables | | | v | Query 1. Base server receives a query. 2. Base server reads remote tables via auxiliary servers and processes them, base server read its own local tables and processes them. 3. Base server combines all tables to produce the query result. 3. New Technology Network Query is executed as a Parallel Query AND remote table processing is delegated to host auxiliary servers. Tables in a group are consecutive in the table join order and other tables of the query are created as Federated tables at the auxiliary servers. Server Diagram --------- --------- --------- | | | | | | | Storage | | Storage | | Storage | | | | | | | --------- --------- --------- | | | | 1st group | 2nd group | Last group | of tables | of tables | of tables v v v --------- --------- --------- |Auxiliary| |Auxiliary| | Base | | Server | --------> | Server | --------> . . . ----> | Server | | 1 | Partial | 2 | Partial | | --------- Query --------- Query --------- ^ Result ^ Result | ^ | | | | | | Full | | | | | Query ------------------------------------- -------- | | Result Same Query | | | v | Query 1. Base server receives a query. 2. Base server reissues the same query to auxiliary servers. 3. Each auxiliary server reads its own group of tables, processes and appends them to partial query result. 4. Base server reads the last group of tables, processes and appends them to partial query result to generate the full query result. 4. Network Query Example 6 tables processed in parallel (WITHOUT delegating remote table processing to host auxiliary servers), table join order is t1,t2,t3,t4,t5,t6. Tables Aux1 Aux2 Base t1 (local) t1 -> Aux1:t1 t2 (local) t2 -> Aux1:t2 t3 (local) t3 -> Aux2:t3 t4 (local) t4 -> Aux2:t4 t5 (local) t6 (local) Server Diagram --------- --------- --------- | | | | | | | Storage | | Storage | | Storage | | | | | | | --------- --------- --------- | | | | t1, t2 | t3, t4 | t5, t6 | | | v v v --------- --------- --------- |Auxiliary| |Auxiliary| | Base | | Server | | Server | | Server | | 1 | | 2 | | | --------- --------- --------- | | ^ ^ | | | | | | | | | | | Query ---------------------------------------- | | Result t1, t2, t3, t4 | | | v | Query Thread Diagram Base Server --------- ---- |thread 1 | | | |processes| <--- | t1 | |t1 |remote| | --------- ---- | v --- Inter | | Table --- Buffer | v --------- ---- | | | | |thread 2 | <--- | t2 | | |remote| | --------- ---- | v --- | | --- | v --------- ---- | | | | |thread 3 | <--- | t3 | | |remote| | --------- ---- | v --- | | --- | v --------- ---- | | | | |thread 4 | <--- | t4 | | |remote| | --------- ---- | v --- | | --- | v --------- ---- | | | | |thread 5 | <--- | t5 | | | | | --------- ---- | v --- | | --- | v --------- ---- | | | | |thread 6 | <--- | t6 | | | | | --------- ---- 5. Parallel Network Query Example Same Example, 6 tables processed in parallel (WITH delegation of remote table processing to host auxiliary servers) Tables Aux1 Aux2 Base t1 (local) t1 -> Aux1:t1 t1 -> Aux1:t1 t2 (local) t2 -> Aux1:t2 t2 -> Aux1:t2 t3 -> Aux2:t3 t3 (local) t3 -> Aux2:t3 t4 -> Aux2:t4 t4 (local) t4 -> Aux2:t4 t5 -> Base:t5 t5 -> Base:t5 t5 (local) t6 -> Base:t6 t6 -> Base:t6 t6 (local) Server Diagram --------- --------- --------- | | | | | | | Storage | | Storage | | Storage | | | | | | | --------- --------- --------- | | | | t1, t2 | t3, t4 | t5, t6 | | | v v v --------- --------- --------- |Auxiliary| |Auxiliary| | Base | | Server | --------> | Server | --------> | Server | | 1 | Partial | 2 | Partial | | --------- Query --------- Query --------- ^ Result ^ Result | ^ | | | | | | Full | | | | | Query ---------------------------------------- | | Result Same Query | | | v | Query Thread Diagram Auxiliary Server 1 Auxiliary Server 2 Base Server --------- ---- |thread 1 | | | |processes| <--- | t1 | |t1 | | | --------- ---- | v --- Inter | | Table --- Buffer | v --------- ---- | | | | |thread 2 | <--- | t2 | | | | | --------- ---- | v --- --- | | --------------------> | | --- Inter Server Socket --- | v --------- ---- | | | | |thread 3 | <--- | t3 | | | | | --------- ---- | v --- | | --- | v --------- ---- | | | | |thread 4 | <--- | t4 | | | | | --------- ---- | v --- --- | | --------------------> | | --- --- | v --------- ---- | | | | |thread 5 | <--- | t5 | | | | | --------- ---- | v --- | | --- | v --------- ---- | | | | |thread 6 | <--- | t6 | | | | | --------- ---- All servers execute the same query however, thread 2 of aux. server 1 redirects output to thread 3 of aux. server 2. thread 4 of aux. server 2 redirects output to thread 5 of base server. Threads 3,4,5 and 6 of aux. server 1 are not used. Threads 1,2,5 and 6 of aux. server 2 are not used. Threads 1,2,3 and 4 of base server are not used.