SQL Execution Sequence Access Path Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database operations using SQL execution sequences often encounter errors and performance issues that need to be corrected to enhance efficiency.
Innovation Solution
A method is implemented where the SQL execution sequence is recorded in an event log and a logical log, allowing for the generation of a new access path through analysis, which is then used to create a modified database instance with improved CPU performance, replacing the original access path if the new results replicate the original results with lower CPU costs.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the original access path is used for SQL execution, then the execution produces correct results, but the CPU cost is high and performance is poor
Solution Approach 1:
The system performs preliminary analysis of SQL execution sequences by recording event logs and logical logs before actual execution. It generates alternative access paths in advance and evaluates them using cost models, so that when a SQL sequence needs to be executed, the optimized access path is already prepared and can be applied immediately, reducing CPU cost during actual execution
Solution Approach 2:
The system implements feedback mechanisms by recording actual execution results and CPU costs in event logs and logical logs. After generating alternative access paths and testing them, the system compares the new results with original results and evaluates the new CPU cost against the original CPU cost. This feedback loop allows the system to learn from actual performance data and continuously improve access path selection
2Use of energy by moving object
If a new access path is generated through analysis, then CPU cost may be reduced, but the risk of producing incorrect results increases
Solution Approach 1:
The system verifies result correctness by comparing new execution results with original results. The event log and logical log are analyzed to ensure that the new access path produces the same correct results as the original access path, providing feedback validation before adopting the optimized path
Solution Approach 2:
The system performs preliminary testing of alternative access paths by executing them in a modified database instance before deploying them to production. This preliminary validation ensures that the new access path will produce correct results when used, reducing the risk of incorrect results
Data Source
AI summary
A method, computer program product, and computer system for improving performance of a SQL execution sequence of SQL statements. The SQL execution sequence is recorded in an event log. Original results of executing the SQL statements and an original CPU cost of executing the SQL statements in accordance with the original access path are recorded in a logical log. A new access path is generated from analysis of the event log and the logical log. The SQL statements are executed in accordance with the new access path resulting in new results of executing the SQL statements including a new CPU cost of executing the SQL statements in accordance with the new access path. In response to a determination that the new results replicate the original results and that the new CPU cost is less than the original CPU cost, the original access path is replaced with the new access path.