Database Query Rewrite Using Confidence Scores
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database query performance is hindered by query terms being given default meanings due to semantic and language usage deviations, and the difficulty in perceiving correct data correlations among columns.
Innovation Solution
A method that analyzes database queries to identify index columns and predicate columns, determines associated columns, calculates confidence scores, generates a list of associated columns with scores, and rewrites the query based on this information to improve performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If developers focus on default meanings of query terms based on semantics and language usage, then the query is easier to write, but query performance deteriorates due to inability to perceive correct data correlations
Solution Approach 1:
The patent introduces an intermediary system (query analysis and rewrite mechanism) that sits between the developer's original query and the database execution. This intermediary automatically analyzes the query, identifies data correlations between columns, generates candidate rewrites, and selects the optimal query formulation without requiring the developer to manually understand complex data relationships, thus maintaining ease of operation while improving query performance
2Productivity
If the system automatically analyzes queries to identify data correlations and generate rewritten queries, then query performance improves, but system complexity increases
Solution Approach 1:
The patent segments the query optimization process into distinct modular components: query analysis module (identifying index and predicate columns), correlation identification module (determining associations between columns), query generation module (creating candidate rewrites with confidence scores), and selection module (choosing the best rewrite). This segmentation allows each component to handle a specific task independently, reducing overall system complexity while achieving improved query performance
3Productivity
If developers manually understand and utilize data correlations between columns, then query performance improves, but the difficulty of detecting and measuring correlations increases
Solution Approach 1:
The patent implements a self-service mechanism where the system automatically detects, analyzes, and utilizes data correlations between columns without requiring developer intervention. The query analysis mechanism autonomously identifies index columns, predicate columns, and their correlations, then generates and applies optimized queries automatically, eliminating the difficulty of detecting and measuring correlations for the developer
Data Source
AI summary
A method for improving a query performance of a query of a database application using rewrite includes introducing a database query into a database application to obtain a query result, analyzing the database query to identify index columns and a predicate column; identifying associated columns by determining if any associations exist between the index columns and the predicate column, calculating a confidence score value for each of the associated columns responsive to the associated columns and the predicate column, generating a list of associated columns with the confidence score values, generating a rewritten query by rewriting the database query based on the list of associated columns and the confidence score values and validating the rewritten query.