N-ary Join Query Processing via Graph Segmentation and Sort Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current query processing technologies face inefficiencies in handling complex queries with N-ary joins due to high operating costs and performance issues, especially when using CPU and GPU simultaneously without proper correlation between N-ary join operations and sort operations.
Innovation Solution
A method that generates a join graph from input queries, divides it into subgraphs, and creates an execution plan using N-ary join operators, optimizing based on a cost model and available memory, determining a sort order and selecting appropriate sort algorithms for CPU or GPU processing to improve query processing performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If hash join algorithm is used for N-ary join operations, then join operations can be performed, but a large amount of operating cost is required due to comparison of all overlapping keys in hash table with current join key
Solution Approach 1:
The patent segments the N-ary join operation into multiple binary join operations. By dividing the complex N-ary join into a sequence of simpler binary joins, the system avoids the high operating cost of hash table probing while maintaining query processing capability. This segmentation allows each binary join to operate efficiently with reduced comparison overhead.
Solution Approach 2:
The patent introduces an intermediary sorting operation between the N-ary join and the actual join execution. By pre-sorting the input data according to join keys, the system creates an ordered structure that eliminates the need for expensive hash table probing and key comparisons, thereby reducing operating cost while preserving productivity.
2Productivity
If sort operation is performed only with CPU for N-ary join operation, then sorting can be completed, but operation efficiency is greatly reduced
Solution Approach 1:
The patent merges CPU and GPU resources to perform sort operations. By combining the computational power of both processors, the system achieves higher sort operation efficiency than CPU alone while reducing overall processing time. The GPU handles parallel sorting tasks efficiently, complementing the CPU's coordination and data preparation functions.
Solution Approach 2:
The patent dynamically selects between CPU and GPU for sort operations based on available resources and query characteristics. This dynamic allocation allows the system to optimize sort operation efficiency by utilizing GPU acceleration when available, while falling back to CPU when necessary, thereby adapting to different operational contexts to minimize processing time.
3Productivity
If CPU and GPU are simultaneously utilized for N-ary join processing, then processing power is increased, but inefficiency occurs if correlation between N-ary join processing operation and sort operation is not taken into consideration
Solution Approach 1:
The patent performs sort operations as a preliminary action before executing N-ary join operations. By pre-sorting the data and establishing the correlation between sort and join operations in advance, the system simplifies the coordination between CPU and GPU. This preliminary organization of data eliminates the need for complex real-time synchronization during the join operation, reducing device complexity while maintaining high processing power.
4Adaptability or versatility
If complex queries with foreign-key-foreign-key joins are processed, then query capability is enhanced, but query processing performance becomes slow or fails due to generation of overly big intermediate data
Solution Approach 1:
The patent segments complex N-ary join queries into multiple binary join operations. By breaking down the complex query into smaller binary join steps, the system processes intermediate data in manageable portions rather than generating all intermediate data at once. This segmentation significantly reduces the peak memory requirement and prevents the generation of overly big intermediate data while maintaining the ability to handle complex foreign-key-foreign-key join queries.
Data Source
AI summary
A method of processing database queries according to an embodiment of the disclosure includes the steps of acquiring an input query of a database, generating a join graph consisting of vertexes corresponding to the tables of the database and edges corresponding to join information among the tables, dividing the join graph into a plurality of subgraphs, and for the input query, generating an execution plan of a target query including N-ary join operators by using the plurality of subgraphs, optimizing the execution plan of the target query based on a cost model, acquiring a table sort algorithm of the N-ary join operators included in the target query based on an available size of a GPU or CPU memory, determining a sort order of one or more join columns included in the tables of the N-ary join operators, and based on the sort order of the one or more join columns, performing an operation for the plurality of subgraphs.