Parameterized Query Plan Recompilation for Changing Data Distributions
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Query optimization for parameterized queries in database management systems is hindered by the unpredictability of parameter values at query execution time, leading to sub-optimal performance due to the dynamic nature of databases and changes in data distribution.
Innovation Solution
A runtime parameterized query management system that automatically determines whether to reuse or recompile query plans based on compilation and execution histories, using similarity analysis to estimate performance and adjust to changing parameter values and data distribution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If query plans are cached to avoid repetitive compilations, then compilation time is reduced, but query optimization accuracy deteriorates due to parameter value unpredictability
Solution Approach 1:
The system performs preliminary compilation of query plans and stores them in a cache before actual query execution. When a query is executed, the system checks the cache for existing query plans and reuses them if applicable, avoiding the need for repetitive compilation. This preliminary action resolves the time loss issue while the system separately handles optimization accuracy through parameter substitution and plan validation mechanisms.
2Adaptability or versatility
If parameterized queries are used to handle dynamic parameters, then query reusability is improved, but query plan performance deteriorates due to changes in data distribution
Solution Approach 1:
The system dynamically adjusts query plan selection based on runtime parameter values and changing data distribution characteristics. Instead of using a static cached plan for all parameterized queries, the system evaluates the specific parameter values and data state at execution time to determine the optimal query plan, thereby maintaining high performance while preserving query reusability through parameterized templates.
3Productivity
If query plans are reused from cache, then execution efficiency is improved, but adaptability to changing data distribution deteriorates
Solution Approach 1:
The system incorporates feedback mechanisms that monitor query execution performance and data distribution changes. When performance degradation is detected or data distribution changes are identified, the system uses this feedback to invalidate or update cached query plans, ensuring that execution efficiency is maintained while adapting to changing data distribution characteristics through continuous monitoring and plan adjustment.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A computer-implemented method can receive a parameterized query with an input parameter set, wherein the parameterized query has a query plan stored in a plan cache, determine an estimated compilation-plus-execution time for compiling and executing the parameterized query with the input parameter set based on a compilation history associated with the parameterized query, determine an estimated execution time for executing the parameterized query with the input parameter set by using the query plan based on an execution history associated with the query plan, and determine a cache gain based at least in part on the estimated compilation-plus-execution time and the estimated execution time. Responsive to finding that the cache gain is positive, the method can execute the parameterized query with the input parameter set by using the query plan. Otherwise, the method can compile and execute the parameterized query with the input parameter set.