Automated Persistent Database View Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Enterprise organizations face significant storage costs and computational overhead due to the manual creation and maintenance of multiple database views, which are necessary to optimize database queries but are wasteful and inefficient.
Innovation Solution
An automated system using AI/ML and graph-based algorithms processes queries to identify commonalities and create optimized persistent database views, reducing storage costs and computational expenses by leveraging machine learning and genetic algorithms to score and optimize view creation.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If multiple database views are manually created and maintained to optimize queries, then query performance is improved, but storage costs and computational overhead increase significantly
Solution Approach 1:
The system enables automated view creation and optimization through self-service mechanisms. The view optimizer automatically analyzes query patterns, identifies optimization opportunities, creates optimized views, and manages their maintenance without human intervention. This self-service approach resolves the contradiction by eliminating the manual labor while maintaining query performance improvements.
Solution Approach 2:
The system dynamically changes parameters such as view creation timing, optimization criteria, and view lifecycle management based on query patterns and system state. By adjusting these parameters automatically, the system improves query performance while controlling storage and computational resources, resolving the trade-off between performance and overhead.
2Adaptability or versatility
If multiple database views are manually created to serve different queries, then query accessibility is improved, but personnel overhead and processing time increase
Solution Approach 1:
The automated view optimization system performs self-service by automatically analyzing query patterns, determining which views to create, and managing their lifecycle. This eliminates the need for personnel to manually create and maintain views, thereby improving query accessibility while eliminating personnel overhead and processing time associated with manual view management.
Solution Approach 2:
The system performs preliminary actions by proactively analyzing query patterns and pre-creating optimized views before they are needed. This preliminary action ensures query accessibility is improved while eliminating the need for manual intervention, as the system autonomously prepares optimized views in advance based on predicted query needs.
3Adaptability or versatility
If data is constantly transformed through grouping and aggregation, then data utility is improved, but computational cost and latency increase
Solution Approach 1:
The system performs preliminary data transformation by pre-computing and storing transformed data in optimized views before actual queries are executed. This preliminary action maintains data utility for various queries while significantly reducing computational cost and latency during query execution, as the heavy transformation work has already been done in advance.
Solution Approach 2:
The system dynamically adjusts data transformation strategies based on query patterns and system state. By making transformations dynamic and adaptive rather than constant, the system improves data utility for different query types while reducing overall computational cost by avoiding unnecessary transformations. The system only performs transformations when and where they are actually needed based on real-time analysis.
Data Source
AI summary
Embodiments for automatically optimizing and persisting database views by receiving queries made to a database, wherein each query generates a respective database view, and generating a set of database maintained views generated by the queries. The system obtains, for each generated view, certain telemetry information about a respective view including latency, memory space utilization, and processor utilization, among other factors. It then scores each view of the generated views based on an base score modified by the obtained information to determine which one or more of the generated views to make persistent, and maintains the one or more persistent views to produce an optimized persistent set of database views. It further adapts later queries to use the optimized persistent views.