Reverse Mapping for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database query tuning techniques only provide partial improvements and are not sufficient for achieving a more efficient database environment, as they lack comprehensive methods for optimizing query performance.
Innovation Solution
A reverse mapping technique using unique query identifiers (UQIs) is implemented to identify and analyze expensive queries, allowing for automatic query tuning by generating potential indexes and storing mapping information in a distributed cache system, enabling efficient retrieval and improvement of query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional database query tuning techniques (statistics and optimized indexes) are used, then partial improvement in query performance is achieved, but comprehensive query optimization is not attained
Solution Approach 1:
The patent introduces a reverse mapping table as an intermediary data structure that stores the correspondence between universal query identifiers and original SQL queries. This intermediary enables the database system to efficiently retrieve and analyze query information without complex parsing operations, thereby achieving comprehensive query optimization while maintaining manageable system complexity
Solution Approach 2:
The patent replaces traditional mechanical query analysis methods with a identifier-based lookup mechanism. Instead of using complex statistics and index optimization procedures, the system generates universal query identifiers and uses them to directly retrieve query information from the reverse mapping table, substituting mechanical optimization processes with efficient identifier-based access
2Productivity
If comprehensive query analysis is implemented to identify expensive queries, then query optimization is improved, but system resource usage increases
Solution Approach 1:
The patent extracts only the essential query identification information into universal query identifiers and stores them in a reverse mapping table. This extraction approach allows the system to analyze and optimize queries without processing complete query texts, thereby improving query optimization capability while minimizing system resource consumption
Solution Approach 2:
The patent changes the parameter representation from complete SQL queries to compact universal query identifiers. This parameter transformation enables efficient storage and retrieval of query information, allowing comprehensive query analysis with reduced memory usage and lower computational overhead
Data Source
AI summary
Reverse database query mapping. A database query to be executed on a computing platform against a database managed by the computing platform is received. Query mapping information is stored in a memory system accessible to the one or more processors. A source query corresponding to a query identifier is retrieved in response to statistical analysis. One or more potential indexes are generated for use with the source query, wherein at least one of the one or more potential indexes can function to improve performance of the source query.