Query Optimizer Cost Calculation Using Table Similarity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing query execution systems face inefficiencies in selecting the most effective access path for data transfer between tables in SQL databases, leading to suboptimal performance due to varying query execution methods and complex conditions.
Innovation Solution
A processor calculates the cost of data transfer based on the similarity between tables, using a cost function to estimate processor usage and identify the most efficient access path by considering factors like table structure, data type, and order, thereby optimizing CPU utilization.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If the query optimizer evaluates multiple alternative execution plans using traditional cost formulas, then the query execution correctness is ensured, but the query execution performance is suboptimal due to insufficient consideration of table similarity and data transfer efficiency
Solution Approach 1:
The system pre-calculates and stores similarity metrics between tables before query execution. When a query is executed, the optimizer can directly use these pre-computed similarity values to evaluate access paths, avoiding the need to perform complex similarity analyses during query optimization, thus improving execution performance without proportionally increasing complexity
Solution Approach 2:
The patent introduces table similarity as an intermediary metric that mediates between the query optimizer and the access path selection process. This similarity metric serves as a bridge that quantifies the relationship between source and target tables, enabling the optimizer to make more informed decisions about data transfer operations without directly evaluating all possible execution plans in detail
2Measurement precision
If the system uses traditional cost formulas with I/O and CPU utilization weights, then the evaluation is computationally feasible, but the cost estimation accuracy is insufficient for optimizing data transfer operations between tables
Solution Approach 1:
The system enhances the traditional cost formula by incorporating table similarity as an additional parameter. This parameter change allows the cost estimation to account for the specific characteristics of data transfer between similar tables, improving accuracy without requiring a complete redesign of the cost evaluation framework, thus maintaining computational feasibility
3Reliability
If the query optimizer considers all possible access paths for data retrieval, then the optimal path is guaranteed to be found, but the optimization time increases significantly due to the large number of alternatives
Solution Approach 1:
The system pre-computes and caches table similarity metrics before query optimization. During query execution, the optimizer can quickly retrieve these pre-calculated values and use them to prune the search space of possible access paths, reducing the time required to evaluate alternatives while still ensuring that the optimal path is selected based on accurate similarity-based cost estimates
Data Source
AI summary
In an approach for optimizing query executions a processor receives a request to transfer data from a first table to a second table. A processor detects a relationship between the first table and the second table, wherein the relationship is based on a similarity between the first table and the second table. A processor identifies at least a first access path for transferring the data from the first table to the second table. A processor calculates a first cost of transferring the data from the first table to the second table using the first access path based on the similarity between the first table and the second table, wherein the first cost indicates estimated processor usage to transfer the data.