Multi-system Query Execution Plan Offloading
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database management systems (DBMS) architectures either prioritize speed at the cost of capacity or vice versa, leading to suboptimal performance for complex queries due to limited volatile memory and optimized execution for specific operations.
Innovation Solution
A query optimizer that determines and optimizes query execution plans by offloading database operations from a primary DBMS to a secondary DBMS based on operation cost estimates, considering hardware architecture, software, and data storage differences between the two systems.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If data is stored in volatile memory to achieve fast access, then query execution speed is improved, but storage capacity is reduced due to limited volatile memory size
Solution Approach 1:
The patent segments the database storage across multiple DBMS instances with different architectures. Some DBMS instances use volatile memory for fast access to frequently queried data, while others use non-volatile storage for capacity. The query optimizer divides complex queries and routes different portions to appropriate DBMS instances based on data location and access patterns, resolving the contradiction between speed and capacity.
Solution Approach 2:
The patent introduces a new dimension by deploying multiple DBMS instances with different architectural characteristics (in-memory vs. traditional disk-based) rather than relying on a single system. This multi-dimensional approach allows the system to simultaneously achieve fast access for hot data and large capacity for cold data, overcoming the single-dimension limitation of choosing between speed and capacity.
2Productivity
If DBMS architecture is optimized for specific operations, then performance on those operations is improved, but performance on other operations deteriorates
Solution Approach 1:
The patent creates a universal database system by deploying multiple DBMS instances with different architectural optimizations. One instance may be optimized for analytical queries while another handles transactional workloads. The query optimizer acts as a universal interface that automatically routes different types of operations to the most suitable DBMS instance, providing both specialized performance and broad versatility.
Solution Approach 2:
The system dynamically adapts to different query types by evaluating query characteristics and routing them to appropriate DBMS instances in real-time. The query optimizer dynamically determines the execution plan and selects which DBMS instance should handle each operation based on current workload and data location, allowing the system to maintain high performance across diverse operation types rather than being static and specialized.
3Reliability
If complex queries are executed on traditional DBMS, then query completeness is ensured, but execution time increases due to data access bottlenecks
Solution Approach 1:
The patent segments complex queries into multiple sub-queries that can be executed in parallel across different DBMS instances. The query optimizer identifies independent operations within a complex query and distributes them to appropriate instances (in-memory for fast aggregations, disk-based for large table scans). This segmentation maintains query completeness while reducing overall execution time through parallel processing.
Solution Approach 2:
The system performs preliminary actions by pre-loading frequently accessed data into in-memory DBMS instances and maintaining materialized views of commonly queried datasets. When complex queries are received, the query optimizer can leverage these pre-positioned data copies to avoid time-consuming disk I/O operations, ensuring complete query results are returned faster by using preliminary data preparation.
Data Source
AI summary
Techniques are described to evaluate an operation from an execution plan of a query to offload the operation to another database management system for less costly execution. In an embodiment, the execution plan is determined based on characteristics of the database management system that received the query for execution. One or more operations in the execution plan are then evaluated for offloading to another heterogeneous database management system. In a related embodiment, the offloading cost for each operation may also include communication cost between the database management systems. The operations that are estimated to be less costly to execute on the other database management system are then identified for offloading to the other database management system. In an alternative embodiment, the database management system generates permutations of execution plans for the same query, and similarly evaluates each permutation of the execution plans for offloading its one or more operations. Based on the total cost of each permutation, which may include offloading cost for one or more operations to another database management system, the least costly plan is selected for the query execution.