Database Query Fusion for Complex Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database engines face challenges in optimizing complex queries, leading to varying execution times for similar queries, and struggle with generating effective data visualizations from multiple fact tables, especially when users specify insufficient Levels of Detail for aggregate calculations.
Innovation Solution
The implementation of an object model that stores relationships between data elements, allowing the database engine to optimize complex queries through query fusion by identifying and combining dimension and aggregated measure subqueries, and recommending additional data fields for data visualizations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the database engine executes multiple separate subqueries to retrieve data from multiple fact tables, then the query can be executed with simple individual processing, but the total execution time increases significantly due to repeated operations and lack of optimization
Solution Approach 1:
The patent combines multiple separate subqueries into a single fused query by integrating the query logic and execution plans. The system identifies common operations across multiple subqueries (such as joins with the same dimension tables) and merges them into one optimized query execution, eliminating redundant operations and reducing total execution time while managing complexity through systematic query fusion algorithms
2Adaptability or versatility
If the database engine uses a single Level of Detail for aggregate calculations, then the aggregation process is simplified, but it becomes insufficient for building certain calculations that require multiple Levels of Detail from different data sources
Solution Approach 1:
The patent segments the aggregation process into multiple Levels of Detail, each handling specific aggregation requirements. Instead of using a single aggregation level, the system divides the data processing into hierarchical segments (e.g., granular level, intermediate level, aggregated level), allowing different aggregation operations to be performed at appropriate levels and then combined to produce comprehensive results
3Loss of information
If the database engine retrieves detailed row-level data from multiple tables, then complete data is obtained for visualization, but the data transfer and processing overhead increases significantly
Solution Approach 1:
The patent applies preliminary aggregation and filtering operations directly in the database engine before data is transferred to the client application. By performing data summarization, grouping, and filtering at the source (in the database), the system reduces the volume of data that needs to be transferred over the network, thereby reducing data transfer overhead and energy consumption while still providing complete and accurate data for visualization purposes
Data Source
AI summary
A database engine receives a query batch of database queries from a client. The database engine identifies one or more object model queries from the query batch. Each object model query includes an outer-most outer-join that joins a respective dimension subquery and respective aggregated measure subqueries. The database engine forms a plurality of candidate subqueries by peeling off the respective outer-most outer-join for each of the object model queries. The database engine then fuses at least some of the plurality of candidate subqueries to form a set of optimized subqueries. The set of optimized subqueries has fewer subqueries than the plurality of candidate queries. The database engine also forms an optimized execution plan based on the set of one or more optimized subqueries. The database engine subsequently obtains a result set from the database based on the optimized execution plan, and returns the result set to the client.