Database Query Multiplexing for Join Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In database operations, particularly in SQL queries, join performance is often suboptimal when a table is referenced multiple times, leading to inefficient processing and poor performance due to factors like high Filter Factor and frequent table access, which existing solutions like Materialized Query Tables struggle to adequately address.
Innovation Solution
The method involves combining multiple data operations into a target data operation, such as table-scan, index-scan, sort, or join operations, to generate an intermediate result by performing a single multiplexed operation on the data records, reducing the need for repeated access and processing of the same table or tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple data operations are performed separately on the same table, then each operation can be optimized individually, but the overall processing time increases and performance deteriorates
Solution Approach 1:
The patent combines multiple data operations (scan, sort, join) that were previously executed separately into a single multiplexed operation. The system identifies operations on the same table and merges them into one unified execution plan, reducing the number of times the table is accessed and processed, thereby improving query performance and reducing processing time.
Solution Approach 2:
The system performs preliminary analysis of the query plan to identify multiple operations targeting the same table before execution. By detecting these operations in advance, the system can pre-combine them into a single multiplexed operation, avoiding repeated table accesses and optimizing the execution path before the actual query processing begins.
2Productivity
If a table is accessed multiple times for different operations, then comprehensive data processing is achieved, but I/O operations increase and efficiency decreases
Solution Approach 1:
The patent merges multiple I/O-intensive operations (table scans, index scans) into a single multiplexed operation. By combining these operations, the system reduces the total number of I/O operations required to process the same table, thereby decreasing energy consumption and improving processing efficiency.
3Productivity
If multiple join operations are performed on the same tables, then complex query requirements are met, but join performance deteriorates due to repeated processing
Solution Approach 1:
The patent combines multiple join operations that involve the same tables into a single multiplexed join operation. This reduces the complexity of executing multiple separate joins and improves performance by avoiding repeated processing of the same data sets, while still satisfying complex query requirements through the unified operation.
Data Source
AI summary
Embodiments of the present invention relate to a method, system, and computer program product for multiplexing data operation. In some embodiments, a method is disclosed. A query for at least one table comprising a plurality of data records is received. The query indicating a plurality of data operations to be performed on the plurality of data records. The plurality of data operations are combined into a target data operation. An intermediate result of the query is generated by performing the target data operation on the plurality of data records. A final result of the query is determined based on the intermediate result. In other embodiments, a system and a computer program product are disclosed.