Query Plan Sharing for Intermediate Result Reuse
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Repetition of queries on similar data consumes significant compute resources, memory, and time, highlighting the need for a method to optimize query execution by reusing query execution plans and intermediate/results.
Innovation Solution
Implementing a system that allows queries to share query execution plans and intermediate/results based on parameters, operators, and data sought, with mechanisms to determine result reuseability and update intermediate results for accurate re-use.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If queries are executed repeatedly on similar data, then query results can be obtained, but computational overhead and execution time increase significantly
Solution Approach 1:
The system pre-computes and stores intermediate query results when data changes occur, so that when a query is executed, it can reuse these pre-computed results instead of calculating them again. This preliminary action eliminates redundant computation and reduces query execution time.
Solution Approach 2:
The system creates and stores copies of intermediate query results in a result cache. When subsequent queries require similar data processing, the system retrieves these copied results from the cache rather than re-computing them, significantly reducing computational overhead and execution time.
2Use of energy by moving object
If query execution plans are reused, then resource usage is optimized, but the system complexity increases due to result reuseability determination
Solution Approach 1:
The system replaces complex logical analysis with a simpler mechanism by using hashes of data change events as keys to directly retrieve intermediate results. Instead of analyzing whether queries are similar and whether results can be reused, the system simply hashes the change event and looks up the result, dramatically reducing system complexity while maintaining resource optimization.
Solution Approach 2:
The system transforms the complex multi-parameter problem of determining result reuseability into a simple parameter-based lookup by hashing the data change event. This parameter transformation converts a complex decision-making process into a straightforward key-value retrieval operation, reducing system complexity while preserving resource efficiency.
3Productivity
If intermediate results are cached for reuse, then query performance improves, but memory consumption increases
Solution Approach 1:
The system extracts and caches only the specific intermediate results that are actually needed for future query executions, rather than caching all possible query results. By selectively storing only relevant intermediate results based on actual data changes and query patterns, the system improves query performance while minimizing unnecessary memory consumption.
Data Source
AI summary
A method, a system and a computer program product for execution of queries. A first query is executed using a first query execution plan to generate a first result data by applying a first query operator to a first data stored in a first storage location. The first result data is provided to a second query execution plan for execution of a second query. The second query is executed on a second data stored in a second storage location in accordance with the second query execution plan. The second query execution plan includes a second query operator. Based on the execution of the second query, a second result data is generated in accordance with the second query execution plan by at least applying the second query operator to at least the provided first result data.