External Offline Query Optimization for SQL Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems (DBMS) face challenges in optimizing complex SQL queries within time limits due to resource constraints, leading to inefficient resource usage and prolonged query execution times, especially in cloud-based environments.
Innovation Solution
Implementing an external offline query optimization process that generates an optimized execution plan for SQL queries, utilizing additional resources and advanced optimization techniques not feasible in adhoc optimization, and importing the optimized plan back into the DBMS for caching and future use.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If local/online optimization is used during query execution, then query execution time is reduced, but resource usage increases and optimization quality deteriorates for complex queries
Solution Approach 1:
The patent applies preliminary action by pre-generating and caching execution plans for frequently executed SQL queries before they are actually needed. The system identifies high-frequency queries, generates their execution plans in advance using available resources, and stores them in a plan cache. When these queries are executed, the pre-generated plans are retrieved and used, eliminating the need for real-time optimization during query execution and reducing both time loss and resource contention.
2Productivity
If adhoc optimization is used during query execution, then resource usage is reduced, but query execution time increases for complex queries
Solution Approach 1:
The system performs optimization work in advance during periods when system resources are available and contention is low. By pre-generating execution plans for complex queries and caching them, the system shifts the computational burden from high-contention execution periods to lower-contention off-peak periods, thereby reducing query execution time without causing resource contention during critical operations.
Solution Approach 2:
The patent creates copies of execution plans that can be reused across multiple query executions. Instead of performing full optimization for each query execution, the system generates one execution plan and creates/retrieves copies from the cache for subsequent executions of the same or similar queries. This copying mechanism dramatically reduces both optimization time and resource usage during actual query execution.
3Productivity
If more resources are allocated to optimization, then optimization quality improves, but system resource availability for query execution decreases
Solution Approach 1:
The system performs resource-intensive optimization work in advance when system resources are more abundant and contention is lower. By pre-generating execution plans during off-peak periods or when resources are available, the system achieves high optimization quality without depriving query execution of necessary resources during critical operations. The pre-generated plans are then reused, eliminating the need for continuous resource allocation to optimization.
Solution Approach 2:
The optimization process is implemented as periodic batch processing rather than continuous real-time operation. The system periodically identifies candidates for pre-optimization, generates their execution plans in batches during periods of lower system load, and updates the plan cache. This periodic approach allows the system to allocate resources to optimization in controlled intervals without permanently reducing resource availability for query execution.
Data Source
AI summary
A determination is made that an execution plan for a received structure query language (SQL) database query to execute on a database is not present in a database plan cache. An external offline optimization process is triggered to generate an externally optimized execution plan for the SQL database query. The generation of the externally optimized execution plan includes: receiving all or part of the received SQL database query as SQL statements and metadata associated with the database, looping over and analyzing all the received SQL statements, selecting at least one an optimization strategy for the SQL statements; and executing the selected at least one optimization strategy to generate the externally optimized execution plan. The externally optimized execution plan is then pinned into the database plan cache.