Query Optimization via Relationship Modeling and Merging
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing methods for query optimization in database-driven applications are inefficient, as they either require manual rewriting of queries for every possible code path, which is impractical for large query sets, or rely on tools like QBS that fail to reduce the number of queries and are limited by source code availability and programming language specificity.
Innovation Solution
The proposed solution involves a system and method for query optimization that receives a query stream, determines relationships among queries with associated prior probabilities, models the queries based on these relationships, and rewrites queries to combine multiple related queries into fewer queries, thereby reducing round trips and improving efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If multiple queries are sent separately to the database, then each query can be processed independently, but the number of round trips increases causing latency
Solution Approach 1:
The system combines multiple separate queries into a single query by analyzing query patterns, determining relationships among queries, and merging them into consolidated queries that achieve the same data retrieval goals with fewer database round trips, thereby reducing latency while maintaining correctness
2Productivity
If query statements are hand-written for every possible code path, then query optimization can be achieved, but the complexity and effort increase significantly for large amounts of query statements
Solution Approach 1:
The system automatically analyzes query patterns, determines relationships among queries, and generates optimized queries without requiring manual intervention. The query optimization process serves itself by using the collected query data to automatically identify optimization opportunities and execute the optimization, eliminating the need for developers to manually write optimized queries for every code path
3Productivity
If conventional tools like Quro are used to re-arrange query statements, then processing order efficiency is improved, but the number of queries remains unchanged
Solution Approach 1:
The system goes beyond re-arranging queries by actually merging multiple queries into single consolidated queries. By analyzing relationships among queries and identifying opportunities to combine them, the system reduces the total number of queries sent to the database, thereby reducing round trips and latency in addition to improving processing order efficiency
Data Source
AI summary
The present teaching relates to methods and systems for query optimization by receiving a query stream comprising a plurality of queries from one or more applications via the network; determining relationships among the plurality of queries, wherein each of the relationships is associated with a prior probability; modeling the plurality of queries based on the relationships with the corresponding prior probabilities; re-writing at least one of the plurality of queries based on the model for the plurality of queries by, for each of the at least one of the plurality of queries generating a new query based on more than one query in the plurality of queries that are related via one of the relationships with a corresponding prior probability, and updating the corresponding prior probability based on the model of the plurality of queries.