User-Defined Function Execution Distribution in Relational Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data search technologies face inefficiencies in querying large databases, particularly when performing complex analytics, due to the impracticality of using user-defined functions and transforms, which can lead to slower query performance and increased resource utilization.
Innovation Solution
The system distributes the execution of user-defined functions into multiple phases, allowing for separate execution on a relational database with an aggregate projection, which pre-calculates data values, thereby reducing the need for re-aggregation and improving query efficiency by staging data processing across data load, query, and maintenance phases.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If user-defined functions are executed directly on large databases, then complex analytics can be performed, but query performance deteriorates and resource utilization increases
Solution Approach 1:
The execution of user-defined functions is segmented into multiple phases (data load phase, query phase, maintenance phase) that can be executed separately and in parallel. This segmentation allows complex analytics to be broken down into manageable stages, improving query performance while maintaining the capability to perform complex analytics operations.
Solution Approach 2:
Data is pre-aggregated during the data load phase and stored in an aggregate projection before queries are executed. This preliminary action eliminates the need to perform aggregation operations during query execution, significantly improving query performance while preserving the ability to execute complex analytics through user-defined functions.
2Adaptability or versatility
If user-defined functions are executed on large databases, then analytics operations can be performed, but resource utilization increases
Solution Approach 1:
The analytics operations are segmented into distinct phases that can be executed independently and in parallel across different computational resources. This reduces peak resource utilization by distributing the computational load over time and across multiple processors, while maintaining full analytics capability.
Solution Approach 2:
Aggregation operations are performed in advance during the data load phase and stored in aggregate projections. This preliminary computation reduces the computational resources required during query execution, as the expensive aggregation operations are avoided during actual queries.
3Measurement precision
If data is re-aggregated during queries, then accurate results are obtained, but query efficiency decreases
Solution Approach 1:
Data is pre-aggregated during the data load phase and stored in aggregate projections with pre-computed aggregate values. During query execution, these pre-computed values are directly retrieved without re-aggregation, maintaining result accuracy while dramatically improving query efficiency by eliminating redundant computational operations.
Data Source
AI summary
A user-defined function (UDF) is received. Then, execution of the UDF is distributed into a plurality of phases. Lastly, each of the phases is executed separately on a relational database including an aggregate projection that stores an aggregate value calculated from a table column of a table using an aggregate function.