Query Expression Repository for Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current relational database management systems (RDBMS) lack effective optimization techniques to minimize query execution time, as they do not adequately leverage historical query data for improving future query performance.
Innovation Solution
Implementing a query expression repository (QER) that stores planning and execution information for previous queries, allowing the optimizer to learn from past queries and reuse relevant information to optimize current and subsequent queries, with a QER manager that increments frequency counts for frequently used query expressions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If traditional query optimization techniques are used, then query execution plans can be generated, but query execution time cannot be minimized effectively due to lack of historical query data utilization
Solution Approach 1:
The system performs preliminary actions by storing query execution plans and runtime information in a query expression repository (QER) before future queries are executed. The QER manager pre-processes and archives historical query data, including execution plans, runtime metrics, and frequency counts, so that when new queries arrive, the optimizer can immediately leverage this pre-prepared historical information to make faster, more informed optimization decisions without waiting for data collection during query execution.
2Productivity
If query execution plans are stored and reused, then optimization performance improves, but system complexity increases due to additional repository management
Solution Approach 1:
The QER manager is designed as a multi-functional component that simultaneously performs multiple tasks: storing query execution plans, tracking runtime information, maintaining frequency counts for query expressions, managing repository indexing, and supporting retrieval operations. This universal manager consolidates what could be separate complex subsystems into a single coordinated component, reducing overall system complexity while maintaining high query optimization efficiency through centralized historical data management.
Data Source
AI summary
An apparatus, method and computer program product for query optimization in a Relational Database Management System (RDBMS), wherein an optimizer accesses a query expression repository (QER), so that the optimizer learns from previous versions of the queries to improve current and subsequent versions of the queries. The QER stores planning and execution information for QEs from the previous versions of the queries, wherein the QEs comprise table relations, intermediate results and/or final results of operations in the previous versions of the queries. The optimizer searches the QER for QEs from the query execution plans, and uses information from the QEs stored in the QER when optimizing the current and subsequent versions of the queries. The optimizer may also reuses results from the QEs stored in the QER.