Database Query Optimization via Selectivity-Based Caching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
As database queries become increasingly complex, existing databases face challenges in optimizing execution costs, particularly due to complex sub-queries that involve overlapping portions of database tables, leading to inefficient processing times.
Innovation Solution
The method involves classifying source nodes in a hierarchical tree representation of database queries as highly selective or not highly selective based on access criteria, and modifying the query structure by replacing source nodes with sub-trees that optimize access control and caching strategies, depending on selectivity status, to improve query execution performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If complex sub-queries are used to optimize database queries, then query optimization capability is improved, but processing time increases
Solution Approach 1:
The patent segments complex sub-queries into smaller, reusable query components stored in a query plan cache. By breaking down complex queries into manageable segments that can be independently cached and reused, the system maintains optimization capability while reducing overall processing time through avoidance of redundant computation.
Solution Approach 2:
The system performs preliminary analysis of query components and caches optimized query plans before actual query execution. By pre-computing and storing optimized execution plans for common query patterns, the database can rapidly retrieve and apply these plans during actual query processing, eliminating the need to re-optimize identical or similar queries each time they are executed.
2Productivity
If query optimization is performed for all sub-queries, then query execution efficiency is improved, but computational resource consumption increases
Solution Approach 1:
The patent applies different optimization strategies to different query components based on their characteristics. Highly selective source nodes receive different treatment compared to non-highly selective nodes, with optimization applied locally where most beneficial rather than uniformly across all query components, thus improving execution efficiency while conserving computational resources.
Solution Approach 2:
The system dynamically adjusts optimization parameters such as caching thresholds and selectivity criteria based on query characteristics and system state. By changing these parameters adaptively, the database can optimize query execution efficiency while controlling computational resource consumption according to current workload conditions.
3Reliability
If source nodes are replaced with filtered selections and intermediate tables, then access control is optimized, but query structure complexity increases
Solution Approach 1:
The patent introduces intermediate tables as mediators between the original query structure and the optimized execution plan. These intermediate tables serve as placeholders that simplify the immediate query structure while enabling sophisticated access control and caching operations to be performed during execution, thus improving reliability without permanently increasing structural complexity.
Data Source
AI summary
A database query is modified to improve performance by identifying portions of database tables that are likely to be reused and modifying the database query accordingly. According to certain embodiments, the degree of selectivity with which a table is accessed is used to identify tables that are likely to be reused in subsequent database operations.