Parameterized Query Plan Recompilation for Dynamic Parameters
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Query optimization for parameterized queries in database management systems is inefficient due to the unpredictability of parameter values at query execution time, leading to sub-optimal performance when cached plans are reused with different input parameters.
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 changes in parameter values and data distribution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a cached query plan is reused for parameterized queries, then query execution time is reduced and productivity is improved, but the query plan may become sub-optimal when parameter values change, degrading performance
Solution Approach 1:
The system dynamically decides whether to reuse a cached query plan or recompile it based on runtime analysis of parameter changes and execution history. The query plan manager monitors parameter values and determines optimality at execution time, making the system adaptable to changing conditions rather than static
Solution Approach 2:
The system uses execution history and parameter analysis as feedback mechanisms to evaluate whether a cached query plan remains optimal. By analyzing past execution data and current parameter values, the system can determine when recompilation is necessary to maintain performance
2Reliability
If query plans are recompiled for every parameterized query, then query plan optimality is maintained, but compilation time increases and productivity decreases
Solution Approach 1:
The system performs preliminary analysis of parameter changes and execution history before compilation to predict whether recompilation will improve performance. This preliminary evaluation prevents unnecessary compilations while ensuring optimal plans are generated when needed
3Loss of time
If query plans are cached without recompilation, then compilation time is reduced, but the system cannot adapt to changes in parameter values and data distribution
Solution Approach 1:
The system creates a dynamic caching mechanism that can adaptively decide between reuse and recompilation based on runtime conditions. The query plan manager continuously monitors parameter values and execution patterns to determine when the cached plan remains valid and when adaptation through recompilation is necessary
Data Source
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.