Dynamic Intermediate Result Set Caching for Database Systems
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems lack an efficient method to reuse knowledge gained during access plan execution, leading to resource-intensive storage and performance issues due to the stateless nature of query optimization techniques, which fail to leverage intermediate result sets for subsequent query executions.
Innovation Solution
A method for storing intermediate result sets in a database system, where an access plan generates an intermediate result set, and its value is rated in real-time based on predefined criteria, allowing it to be stored for longer than the query runtime, enabling reuse in future access plans and reducing resource consumption.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If intermediate result sets are stored for future reuse, then query execution time and resource consumption are reduced, but storage requirements and system complexity increase
Solution Approach 1:
The patent applies parameter changes by dynamically adjusting the storage duration and scope of intermediate result sets based on query characteristics, data freshness requirements, and resource availability. The system transforms static caching into a dynamic parameter-driven approach where intermediate results are retained for varying periods depending on their potential reuse value and the specific query workload patterns.
Solution Approach 2:
The patent implements local quality by selectively caching intermediate result sets based on their specific properties rather than uniformly caching all results. The system evaluates each intermediate result set's reuse potential, data volatility, and query importance to determine which ones warrant storage, thereby optimizing storage resource allocation to only the most valuable intermediate results.
2Loss of information
If all intermediate result sets are stored, then knowledge reuse is maximized, but resource consumption and system performance deteriorate
Solution Approach 1:
The patent applies partial action by selectively storing only those intermediate result sets that meet specific reuse criteria rather than caching everything. The system performs partial caching based on evaluation metrics such as query similarity, intermediate result size, and expected reuse frequency, thereby avoiding the resource overhead of storing unnecessary intermediate results while still capturing the most valuable ones.
Solution Approach 2:
The patent implements feedback mechanisms where the system monitors query execution patterns, intermediate result set usage, and resource consumption to dynamically adjust caching decisions. The feedback loop allows the system to learn from actual reuse patterns and optimize which intermediate results are stored, balancing knowledge reuse benefits against resource consumption in real-time.
3Speed
If intermediate result sets are cached, then query processing speed improves, but data accuracy may be compromised in OLTP environments
Solution Approach 1:
The patent applies dynamics by making the caching behavior adaptive rather than static. The system dynamically determines whether to cache intermediate result sets based on the transaction type (OLTP vs OLAP), data modification frequency, and query characteristics. In OLTP environments with high data volatility, the system reduces or disables caching to maintain data accuracy, while in OLAP environments with stable data, it enables aggressive caching for speed improvement.
Data Source
AI summary
A method for storing intermediate result sets in a database is provided. An access plan for a single query for accessing data stored in the database system is received. An intermediate result set is generated during execution of the access plan. A value of an intermediate result set rating of the intermediate result set is determined during execution of the access plan at runtime of the query for determining if the intermediate result set, generated during the execution of the access plan, is to be stored. Based on determining that the value of the intermediate result set rating meets a predefined criterion, the determined intermediate result set is stored for a longer period of time than the runtime of the query as a stored intermediate result set.