Automatic Denormalization for In-Memory Join Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Large table joins in in-memory database query processing do not scale with the size of the cluster, limiting the performance of analytic query processing in data warehouses.
Innovation Solution
An automatic denormalization method is implemented in three phases: discovery, optimization, and deployment, which constructs a representation of tables and join conditions, selects optimal denormalizations based on memory usage, and integrates them into the system to reduce memory needs and eliminate unnecessary joins.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the number of servers is increased to improve query processing performance, then query processing speed is improved, but power consumption and infrastructure costs increase
Solution Approach 1:
The patent combines multiple tables into denormalized table structures that can be loaded into memory once, eliminating the need for multiple servers to perform repeated join operations. This merging approach allows a single server to handle queries that would otherwise require multiple servers, thereby reducing power consumption while maintaining query processing performance.
Solution Approach 2:
The patent performs preliminary denormalization and loads the combined table data into memory before query execution. This preliminary action prepares the data in advance, eliminating the need for runtime join operations and reducing the computational power required during query processing, thus allowing fewer servers to handle the same workload.
2Productivity
If in-memory processing is used to improve query performance, then query processing speed is improved, but memory requirements increase
Solution Approach 1:
The patent merges multiple tables into a single denormalized table structure, which can then be loaded into memory as one unified data set. This approach reduces the total memory footprint compared to storing multiple separate tables and their associated indexes, while still enabling fast in-memory query processing.
Solution Approach 2:
The patent discards the need for complex join operations and temporary result sets by pre-computing and storing denormalized data. This eliminates the need to allocate memory for intermediate computational structures during query execution, reducing peak memory requirements while maintaining processing speed.
3Adaptability or versatility
If large table joins are performed to enable comprehensive query analysis, then query comprehensiveness is improved, but processing time increases
Solution Approach 1:
The patent performs the complex join operations in advance during data loading, creating denormalized tables that already contain the results of what would otherwise be expensive runtime joins. This preliminary action enables comprehensive queries to be executed quickly by simply scanning pre-computed data, eliminating the time-consuming join operations from query execution time.
Solution Approach 2:
The patent combines multiple related tables into denormalized structures that contain all necessary data in a single queryable format. This merging allows comprehensive queries to be executed against a unified data structure without requiring multiple table accesses and joins, significantly reducing processing time while maintaining query comprehensiveness.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A system and method for improving performance of join operations is disclosed. The method in one embodiment includes building a graph of vertices and edges, which represent the tables and join conditions for a particular database schema. The method then traverses the graph to find a set of candidate join operations. The method further includes selecting from the set of subset of join operations that makes the best use of a given amount of memory, which means finding a collection of denormalizations which when residing in memory provide the most benefit and the least cost for the given amount of memory. The method then deploys the denormalizations so that current queries in a query processing system can use them instead of actually performing the joins.