Automated Materialized View Generation for Database Latency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Users experience high latency when submitting queries to services with large databases, due to factors like extensive data tables and increasing query volumes, leading to user dissatisfaction and abandonment.
Innovation Solution
Automated materialized view table generation and maintenance, where a materialized view table management component identifies frequently occurring query fields and latency-prone queries to create, update, and delete materialized view tables dynamically, reducing the load on main databases and improving query processing efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If queries are processed directly against the main database, then data completeness is maintained, but query latency increases significantly
Solution Approach 1:
The patent segments the database structure by creating materialized view tables that contain pre-computed results for specific query combinations. This divides the monolithic database into specialized subsets (materialized views) optimized for particular query patterns, reducing the complexity of processing each individual query while maintaining access to the complete data set through the main database.
Solution Approach 2:
The system performs preliminary actions by pre-computing and storing query results in materialized view tables before actual user queries are executed. These pre-computed results are maintained through automated refresh processes, allowing frequently accessed data to be readily available without performing the full computation again, thus reducing query latency.
2Loss of time
If materialized view tables are manually created and maintained, then query performance improves, but operational complexity and user burden increase
Solution Approach 1:
The system implements self-service by automatically monitoring query patterns, identifying candidates for materialized views, generating the appropriate SQL statements, creating and maintaining the materialized view tables, and managing their refresh schedules without requiring manual user intervention. The system serves itself by autonomously optimizing its own query performance.
Solution Approach 2:
The system uses feedback from actual query execution patterns to dynamically adjust which materialized views are created and maintained. By monitoring query frequency, latency, and resource consumption, the system continuously refines its materialized view strategy, adding views for beneficial query patterns and removing or updating those that are no longer optimal.
3Productivity
If materialized view tables are automatically generated and maintained through continuous feedback-driven procedures, then query latency is reduced and service performance is enhanced, but system complexity increases
Solution Approach 1:
The automated materialized view management system performs multiple functions within a single integrated framework: it monitors query patterns, analyzes performance metrics, generates SQL statements, creates and drops materialized views, schedules refresh operations, and evaluates the effectiveness of these views. This multi-functional approach consolidates complexity into a manageable system rather than requiring separate components for each task.
Data Source
AI summary
One or more computing devices, systems, and/or methods for automated materialized view table generation and maintenance are provided. A log, comprising queries and latencies of processing the queries, is evaluated to identify a list of combinations of fields that occur greater than a threshold frequency and/or occur in queries having latencies greater than a threshold latency. A materialized view generation script is executed against a main database to generate a materialized view table associated with a combination of one or more fields from the list. A middleware component is configured to selectively direct a query to the main database or to the materialized view table based upon whether the materialized view table comprises preliminary query results for fields specified by the query.