Database Access Path Optimization via Predicted Values
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Relational databases face suboptimal performance when handling SQL statements with placeholders in the WHERE clause, leading to inaccurate results and increased compile load due to the need for frequent regeneration of access paths.
Innovation Solution
A method that generates predicted values using a time series forecasting model based on historical data to create candidate access paths, selecting the optimal path based on similarity with actual values, and generating a new path if necessary, thereby optimizing database operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If access paths are regenerated frequently for SQL statements with placeholders, then the database can handle varying queries, but the compile load increases and performance deteriorates
Solution Approach 1:
The system performs preliminary action by generating predicted values for placeholders before the actual query execution. The database predicts what values are likely to be provided for placeholders based on historical data and workload patterns, then pre-generates candidate access paths using these predicted values. This allows the database to prepare access paths in advance rather than regenerating them frequently when actual queries arrive with different values.
Solution Approach 2:
The system creates candidate access paths by copying and adapting from previously successful access paths. Instead of regenerating access paths from scratch for each query with placeholders, the system copies existing access path structures and modifies them based on predicted values and similarity matching, thereby reducing the computational overhead of access path generation.
2Loss of time
If access paths are generated using predicted values, then compile time is reduced, but the accuracy of results may be affected if predicted values differ from actual values
Solution Approach 1:
The system implements feedback by comparing the actual query values with the predicted values after the query is executed. When actual values are provided for placeholders, the system measures the similarity between actual and predicted values and uses this feedback to determine whether to accept the pre-generated access path or regenerate it. This feedback mechanism allows the system to maintain accuracy while benefiting from time savings.
Solution Approach 2:
The system dynamically adjusts its behavior based on the similarity between predicted and actual values. When similarity is high, the system dynamically accepts the pre-generated access path; when similarity is low, it dynamically regenerates the access path. This dynamic approach allows the system to optimize compile time without sacrificing accuracy when necessary.
3Reliability
If manual diagnosis and problem resolution is required for suboptimal access paths, then precise control is achieved, but operational complexity increases
Solution Approach 1:
The system performs self-service by automatically generating predicted values, creating candidate access paths, and selecting optimal paths without requiring manual intervention. The database monitors query execution, measures similarities between predicted and actual values, and autonomously determines when to accept pre-generated access paths or regenerate them, eliminating the need for manual diagnosis and problem resolution while maintaining reliable control over access path selection.
Data Source
AI summary
A method, system, and computer program product that are configured to: receive a Structured Query Language (SQL) statement including placeholders; generate sets of predicted values for the placeholders; generate candidate access paths in a database using the sets of predicted values; receive a query including the SQL statement with actual values instead of the placeholders; select one of the candidate access paths based on determining similarities of the actual values to ones of the sets of predicted values; in response to the selected one of the candidate access paths being acceptable, execute the query using the selected one of the candidate access paths; and in response to the selected one of the candidate access paths not being acceptable, generate a new access path in the database using the actual values, and execute the query using the new access path.