Federated Server Bulk Nested Loop Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing nested loop join (NLJN) processes in federated database systems are inefficient due to the need for multiple round-trips between remote data sources, consuming significant time and computing resources when joining tables from heterogeneous distributed databases.
Innovation Solution
The implementation of a bulk NLJN method that uses block fetch requests and array binds to reduce the number of round-trips required for data retrieval, allowing for faster access to outer tables and more efficient data joining operations without creating temporary merged tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If standard nested loop join (NLJN) is used to join tables from remote data sources, then the join operation can be performed, but multiple round-trips between federated server and remote data sources are required, consuming significant time and computing resources
Solution Approach 1:
The patent applies preliminary action by fetching blocks of rows from the outer table before initiating the inner table joins. Instead of processing one row at a time, the system pre-loads multiple rows into a buffer, allowing subsequent inner table queries to be batched together. This reduces the number of round-trips between the federated server and remote data sources, as multiple join operations can be executed in parallel using the pre-fetched outer table data.
2Measurement precision
If standard NLJN processes each row individually, then accurate join results are achieved, but the number of data fetching operations increases significantly
Solution Approach 1:
The patent merges multiple individual row processing operations into bulk block operations. By combining multiple outer table rows into a single buffer and executing corresponding inner table joins in batch, the system maintains join accuracy while significantly reducing the total number of data fetching operations. The merging of operations allows the federated server to process multiple rows simultaneously rather than sequentially.
Solution Approach 2:
The patent segments the data processing into distinct phases: outer table block fetching, buffer storage, and inner table batch joining. This segmentation allows each phase to be optimized independently - outer table data is fetched in manageable blocks, stored in a buffer for efficient access, and then used to drive batched inner table queries. The segmentation prevents the system from being bottlenecked by individual row processing overhead.
3Quantity of substance
If multiple round-trips are made to fetch data from remote data sources, then complete join data is retrieved, but network overhead and computing resource consumption increase
Solution Approach 1:
The patent implements continuity of useful action by maintaining a buffer of outer table data that persists throughout the join operation. Instead of fetching and processing rows in disconnected individual operations, the system continuously utilizes the buffered data to drive sequential inner table joins. This continuous utilization of pre-fetched data eliminates idle time between fetch operations and keeps the computing resources productively engaged throughout the entire join process.
Data Source
AI summary
A system and method of joining remote tables by a federated server is provided. A method includes receiving a data join request from a client device; sending a first block fetch request to a first data source based on the data join request; receiving a first set of block data from the first data source; sending a second block fetch request to a second data source based on the data join request and a bind array containing the data of join column in the first data source; receiving a second set of block data from the second data source; and sending an output to the client device in response to the data join request in the form of rows from an outer table and an inner table.