Database Query Plan Caching Through Dynamic Filter Validation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database systems often fail to support caching or abstract query plans that include dynamic conditions, leading to inefficient query performance due to repeated compilation, which affects stability and efficiency.
Innovation Solution
Implement caching and persistency solutions to manage query plans with dynamic conditions by collecting and normalizing dynamic conditions as unified filter strings, and persisting them in a serialized format for reuse, allowing query plans to be cached and retrieved efficiently.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If query plans with dynamic conditions are not cached, then query security and adaptability are maintained, but query performance deteriorates due to repeated compilation
Solution Approach 1:
The system performs preliminary validation of dynamic conditions against cached query plans before execution. By checking whether current dynamic conditions match stored plan conditions in advance, the system determines whether to reuse cached plans or compile new ones, preventing unnecessary compilation while ensuring security
Solution Approach 2:
The system changes the parameter of query plan storage from static to dynamic by incorporating dynamic condition information into cached query plans. This allows the same cached plan to be valid under different dynamic conditions by storing and validating the condition parameters, thereby enabling caching without sacrificing security
2Productivity
If query plans are cached without dynamic condition validation, then query performance improves, but query security deteriorates due to potential unauthorized data access
Solution Approach 1:
The system implements feedback by validating dynamic conditions against cached query plans before reuse. The validation process provides feedback on whether the current user's dynamic conditions match the stored plan conditions, allowing the system to securely determine whether cached plans can be reused without compromising authorization
Solution Approach 2:
The system performs preliminary validation of dynamic conditions against cached query plans before execution. By checking whether current dynamic conditions match stored plan conditions in advance, the system determines whether to reuse cached plans or compile new ones, preventing unauthorized data access while maintaining performance
3Productivity
If dynamic conditions are not normalized and stored, then query plan simplicity is maintained, but query performance deteriorates due to repeated compilation
Solution Approach 1:
The system changes the representation of dynamic conditions by normalizing them into a unified filter string format. This parameter transformation allows complex dynamic conditions to be stored in a standardized manner that enables efficient caching and validation while maintaining plan simplicity
Solution Approach 2:
The system applies local quality by normalizing dynamic conditions specifically at the filter string level while maintaining the overall query plan structure. This localized normalization allows complex conditions to be handled uniformly without affecting the simplicity of the rest of the query plan, enabling effective caching
Data Source
AI summary
The present disclosure involves systems, software, and computer implemented methods for managing database query plans with dynamic conditions. One example method includes receiving a request to process a query based on a dynamic condition. In response to determining that a plan cache does not include a cached query plan for the query, query plan compilation is triggered to generate a query plan. In response to determining that a cached query plan exists, for each query view, a condition provider procedure is invoked to generate a first filter string. First filter strings are compared to corresponding second filter strings in the cached plan. In response to determining that a first filter string does not match the corresponding second filter string, query plan compilation is triggered. In response to determining that each filter string matches each corresponding second filter string, the database query is executed using the cached query plan.