ML-Predicted Query Parsing Time for Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database systems face inefficiencies in query parsing and optimization, where the time spent by parsers and optimizers often exceeds actual query execution time, leading to suboptimal resource utilization and potential Service-Level Agreement (SLA) breaches, necessitating a predictive approach to optimize query processing.
Innovation Solution
A system utilizing a trained machine-learning algorithm to predict query parsing time, which is then used to inform the query execution plan, allowing for focused optimization and resource allocation, thereby enhancing query execution efficiency and compliance with SLAs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Manufacturing precision
If query parsing and optimization are performed thoroughly, then query execution accuracy is improved, but query processing time increases
Solution Approach 1:
The system performs preliminary actions by training a machine learning model in advance to predict query parsing time. This prediction capability is established before actual query processing, allowing the system to make informed decisions about optimization resource allocation without performing full optimization for every query, thus reducing processing time while maintaining execution accuracy.
Solution Approach 2:
The system changes the parameter of optimization depth based on the predicted parsing time. For queries with short predicted parsing times, minimal optimization is applied. For queries with longer predicted parsing times, more comprehensive optimization is performed. This dynamic parameter adjustment resolves the contradiction by adapting optimization thoroughness to each query's characteristics.
2Productivity
If comprehensive query optimization is performed, then resource utilization is improved, but processing overhead increases
Solution Approach 1:
The system applies partial optimization action based on predicted parsing time. Instead of performing comprehensive optimization for all queries, it performs optimization only when the predicted parsing time indicates it would be beneficial. This partial action approach improves resource utilization for critical queries while avoiding unnecessary processing overhead for queries where optimization would not provide sufficient benefit.
3Productivity
If query optimization time is increased, then query execution efficiency is improved, but total query response time increases
Solution Approach 1:
The machine learning model performs preliminary prediction of parsing time before optimization begins. This preliminary information allows the system to determine in advance whether extensive optimization is warranted, preventing unnecessary optimization time from being spent on queries where it would not improve execution efficiency sufficiently to justify the added response time.
Solution Approach 2:
The system dynamically adjusts the level of optimization applied to each query based on its predicted parsing time characteristics. This dynamic approach ensures that optimization resources are concentrated on queries where increased optimization time will yield proportional improvements in execution efficiency, while minimizing total response time for the overall query workload.
Data Source
AI summary
A query is preprocessed for features identified by a Data Manipulation Language (DML) in the text of the query. A machine-learning algorithm uses the features as input and provides as output a predicted query parsing execution time needed by a query parser to parse the query. The predicted query parsing time is provided as input to a query optimizer. The query optimizer uses the predicted query parsing time as a factor in optimizing a query execution plan for the query. Subsequently, the query execution plan is executed against a database as the query.