Scalable Multistage Query Processing with Percentile Functions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for processing queries with percentile functions are limited by the number of sorted groups, leading to underutilization of computing resources and longer query processing times, as they cannot take advantage of parallelization beyond the number of groups.
Innovation Solution
The approach involves distributing work using range distribution based on a composite key, allowing for a degree of parallelism greater than the number of groups by subdividing the composite key-value range into sub-ranges that can be assigned to multiple processes, enabling efficient use of available computing resources.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If conventional techniques are used to process queries with percentile functions, then the degree of parallelization is limited to the number of sorted groups, but this leads to underutilization of computing resources and longer query processing times
Solution Approach 1:
The patent segments the input data into multiple ranges based on a composite key (group-by key combined with sort-by key). Each range is further divided into sub-ranges that can be distributed to multiple processes. This segmentation allows the number of parallel processes to exceed the number of groups, as each process works on a subset of ranges rather than being constrained by group count. The segmentation principle directly resolves the contradiction by enabling finer-grained parallelization while maintaining correct percentile computation across all groups.
2Adaptability or versatility
If the number of processes is increased beyond the number of groups, then computing resources are better utilized, but the complexity of distributing and coordinating work increases
Solution Approach 1:
The patent introduces a query coordinator as an intermediary component that manages the distribution of work to multiple processes. The coordinator receives the query, determines the appropriate ranges and sub-ranges based on the composite key, and distributes them to available processes. This intermediary simplifies the overall system architecture by centralizing the coordination logic, allowing multiple processes to work in parallel without complex peer-to-peer coordination. The coordinator handles the complexity of range assignment and result aggregation, enabling efficient resource utilization while maintaining manageable system complexity.
3Productivity
If range distribution based on composite key is used, then the degree of parallelism can exceed the number of groups, but the complexity of determining ranges and assigning them to processes increases
Solution Approach 1:
The patent performs preliminary actions by pre-determining the ranges and sub-ranges of the composite key before executing the percentile calculation. The query coordinator calculates the composite key ranges based on the group-by and sort-by keys in advance, creating a structured distribution plan that can be efficiently implemented during query execution. This preliminary range determination simplifies the actual processing by avoiding complex dynamic calculations during execution, enabling high parallelism while reducing runtime complexity.
Data Source
AI summary
A method and system for processing database queries containing aggregate functions. The query may specify fewer groups than there are processes available to process the queries. Further, the queries may target a set of rows and specify a sort-by key and a group-by key. The method and system further includes determining that the queries specify application of the aggregate function to each of a plurality of groups that may correspond to a plurality of distinct values of the group-by key and determining that plurality of processes are available to process the queries. The method and system also includes determining the plurality of ranges of a composite key that may be formed by combining the group-by key and the sort-by key and assigning each range of the plurality ranges to a corresponding process to calculate the aggregate function.