Temporal Database Query Plan Reuse for Near Real-Time Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational database management systems face challenges in achieving near real-time performance when handling large volumes of data, particularly in enterprise software systems, due to limitations in query plan optimization and execution, especially when dealing with temporal data.
Innovation Solution
The system implements a method for generating and executing query plans in a relational database management system with temporal-relational databases, where query plans are kept in memory and shared nodes are reused to support incremental execution, reducing the need for full re-execution and improving performance by focusing on delta updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If traditional query plans are executed on large volumes of data in relational database management systems, then complete query results are obtained, but query execution time increases significantly and near real-time performance cannot be achieved
Solution Approach 1:
The query execution is divided into multiple segments: full execution for initial results, and incremental execution for updates. The query plan is segmented into operators that can be independently executed and cached, allowing partial results to be reused and only the necessary portions to be re-executed when data changes occur.
Solution Approach 2:
Query plans and their results are pre-computed and cached in memory before actual querying needs arise. When data changes occur, the system performs preliminary identification of which cached query plans are affected and prepares incremental execution strategies, avoiding full re-execution and reducing actual query response time.
2Reliability
If query plans are fully re-executed whenever data changes occur, then query result accuracy is maintained, but compute resource usage and memory consumption increase
Solution Approach 1:
The system implements a feedback mechanism where data change events are monitored and tracked. When changes occur in temporal tables, the system provides feedback to identify which cached query plans are affected, enabling selective incremental re-execution rather than blanket full re-execution, thus maintaining accuracy while reducing compute resources.
Solution Approach 2:
The execution mode parameter is changed from full execution to incremental execution based on the nature of data changes. The system detects whether changes require complete query re-execution or can be handled through incremental updates to cached results, optimizing compute resource usage while maintaining result accuracy.
3Adaptability or versatility
If temporal tables store complete historical data records, then temporal query capabilities are enhanced, but storage requirements and data processing overhead increase
Solution Approach 1:
The patent extracts only the essential temporal information (validity time ranges) from complete historical data records and stores these extracted temporal parameters in temporal tables. This allows temporal query capabilities to function with reduced storage requirements, as the full historical records are not retained but rather their temporal metadata is preserved for query purposes.
Data Source
Figure 1
Figure 2
Figure 3A~3C
AI summary
According to one aspect, a query plan is generated for a newly submitted structured query language (SQL) query received by a relational database management system by reusing at least part of an existing query plan that is being kept in memory after execution.