Database Query Execution Time Estimation via Historical Data Mining
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database query execution time estimation methods, relying on execution costs, are not reliable for predicting actual execution times, leading to inaccuracies in workload management and user decision-making.
Innovation Solution
A data mining approach combined with a binary decision tree and multiple linear regression techniques is used to estimate the static execution time of database queries by analyzing historical query information, providing either a time range or a specific time estimate.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If execution cost is used to estimate execution time, then the estimation process is simple and fast, but the accuracy of execution time prediction is poor
Solution Approach 1:
The patent introduces historical query information as an intermediary between the execution cost and the actual execution time. Instead of directly using execution cost to predict execution time, the system uses historical data that captures the actual relationship between cost and time for similar queries, thereby improving prediction accuracy while maintaining a relatively simple estimation process.
Solution Approach 2:
The patent creates a copy of actual execution time data from historical queries and uses this copied information to predict future execution times. By storing and retrieving actual measured execution times from the history table, the system avoids the inaccuracy of theoretical execution cost models while keeping the prediction mechanism simple through direct data lookup and comparison.
2Reliability
If execution cost is used to estimate execution time, then the method is easy to implement, but the reliability of the estimation is low
Solution Approach 1:
The patent performs preliminary actions by collecting and storing actual execution time data in a history table before making predictions. The system pre-processes execution data, recording actual times alongside execution costs, so that when a prediction is needed, reliable historical information is already available to improve the reliability of the estimation without adding complexity to the prediction algorithm itself.
Solution Approach 2:
The patent implements feedback by using actual execution times from historical queries to improve future predictions. The system continuously accumulates execution data, compares predicted times with actual times, and uses this feedback loop to refine the estimation process, thereby increasing reliability while managing system complexity through iterative improvement.
3Measurement precision
If detailed analysis of execution cost is performed, then resource utilization understanding is improved, but actual execution time prediction remains inaccurate
Solution Approach 1:
The patent performs preliminary analysis by collecting execution time data during normal query operations and storing it in advance. This pre-collection of historical data allows the system to have accurate execution time measurements ready before they are needed for prediction, eliminating the need for time-consuming analysis at query execution time while maintaining high measurement accuracy.
Solution Approach 2:
The patent enables the system to self-service by automatically collecting, storing, and utilizing its own execution time data without requiring external intervention or complex real-time analysis. The database system uses its own historical performance data to predict future execution times, achieving accurate measurements without additional time loss through automated data reuse.
Data Source
AI summary
In a method for estimating a static execution time of a database query, a prediction of query runtime tree is built from historical query information. A database query is received. The prediction of query runtime tree is used to estimate the static execution time of the database query.