Multi-Aggregate User-Defined Function Reducing Database Query Overhead
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems are limited by the single return value policy of user-defined functions (UDFs), which requires multiple UDFs to be written and executed separately for each aggregate value, leading to inefficiencies and resource overhead, especially when executed outside the data store system.
Innovation Solution
Implementing a data store system with a processor array that can perform a single read of rows associated with multiple aggregates using a multi-aggregate UDF, allowing each aggregate to be generated from these rows, thereby reducing the need for multiple UDF calls and improving performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If multiple separate aggregate functions are written and executed one-by-one to obtain multiple aggregate values, then each aggregate value can be calculated accurately, but the number of UDF calls increases significantly and system performance degrades
Solution Approach 1:
The patent combines multiple separate aggregate function calls into a single UDF invocation that returns multiple aggregate values simultaneously. Instead of executing max(), min(), and avg() as separate functions requiring three distinct UDF calls, the system implements a single UDF that computes all three aggregates in one execution pass, merging what were previously separate operations into one unified operation.
Solution Approach 2:
The patent creates a universal UDF framework that can handle multiple aggregate functions through a single call. The enhanced UDF is designed to be multi-functional, capable of computing different aggregate values (maximum, minimum, average, etc.) within a single execution context, eliminating the need for multiple specialized function calls.
2Reliability
If UDFs are executed in protected mode outside the data store system to protect from errors, then data store system reliability is improved, but resource overhead increases due to repeated transmission of qualified rows
Solution Approach 1:
The patent merges multiple separate UDF executions into a single execution that processes all aggregate computations together. By combining what would have been three separate protected-mode executions into one, the system transmits each qualified row outside the data store system only once instead of multiple times, reducing the cumulative resource overhead while maintaining the protective isolation.
Solution Approach 2:
The patent enables continuous processing of multiple aggregates within a single UDF execution context. Rather than interrupting the processing flow to transmit and re-transmit rows for each separate aggregate function, the system maintains continuous action by computing all aggregates in one uninterrupted pass through the data, minimizing I/O operations.
3Device complexity
If a single return value policy is enforced for UDFs, then the system maintains simplicity in function design, but multiple aggregates require multiple separate functions increasing development complexity
Solution Approach 1:
The patent implements a universal UDF design that breaks the single-return-value limitation by enabling functions to return multiple aggregate values simultaneously. This multi-functional approach allows a single UDF to handle multiple aggregate computations, reducing the number of functions developers must write and maintain while preserving design simplicity through a unified interface.
Solution Approach 2:
The patent transitions from a single-dimension return value model to a multi-dimension output model. Instead of forcing developers to create separate functions for each aggregate (one dimension per function), the system allows a single function to return multiple dimensions of aggregate data, changing the dimensional structure of function outputs and simplifying the development landscape.
Data Source
AI summary
A data store system may include a persistent storage device that may store a plurality of data tables. The data store system may further include a processor array in communication with the persistent storage device. The processor array may receive a query comprising a call to a user-defined function (UDF) that may determine a plurality of aggregates for at least one data table from the plurality of data tables. The processor array may determine a number of the plurality of aggregates in the UDF. The processor array may perform a single read of rows associated with the plurality aggregates from the at least one data table. At least one row of the rows may be commonly associated with more than one of the plurality of aggregates. The processor array may generate each aggregate of the plurality of aggregates using the rows according to the UDF. A method and computer-readable medium may also be implemented.