Temporary Materialized Views for Database Session Caching
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face inefficiencies in managing materialized views, particularly in resource consumption and query performance, due to the need for persistent data in two locations and synchronization challenges.
Innovation Solution
Implementing temporary materialized views that use session-specific global temporary tables for result set storage, which are refreshed at read time and deleted at session end, reducing resource consumption and improving query performance by caching results for multiple queries within a session.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If materialized views are used to improve query performance by pre-computing and persisting results, then query execution speed is improved, but resource consumption increases due to persistent storage of data in two locations and synchronization requirements
Solution Approach 1:
The patent implements temporary materialized views that are session-specific rather than persistent, allowing the system to dynamically allocate and release storage resources based on session needs. The temporary tables are automatically dropped when the session ends, converting a static resource allocation model into a dynamic one that adapts to actual usage patterns.
Solution Approach 2:
The patent creates session-specific temporary tables for each user session rather than using a single global materialized view. This allows different sessions to have their own localized copies of computed results, enabling parallel query execution without lock contention while reducing the overall storage footprint compared to maintaining one persistent copy for all users.
2Productivity
If materialized views are used to cache computation results, then query performance improves through result reuse, but device complexity increases due to synchronization mechanisms and lock management
Solution Approach 1:
The patent segments the materialized view functionality into session-specific temporary tables, isolating each user's cached results in separate database objects. This segmentation eliminates the need for complex lock management and synchronization mechanisms that would be required if multiple users shared the same materialized view, as each session operates independently on its own temporary table.
Solution Approach 2:
The patent uses temporary tables that are automatically dropped at the end of each session, replacing expensive persistent materialized views with cheap, short-lived storage objects. These temporary tables provide the caching benefit during the session but require no long-term maintenance, synchronization, or lock management infrastructure.
3Loss of time
If persistent materialized views are used to store computed results, then data reuse across queries is improved, but harmful factors increase due to lock contention and synchronization overhead
Solution Approach 1:
By creating separate temporary tables for each session, the patent segments the data storage so that multiple users can simultaneously access and reuse computed results without any lock contention. Each session's temporary table is isolated from others, eliminating the synchronization overhead that would exist with shared persistent materialized views.
Solution Approach 2:
The patent creates copies of the computed results in session-specific temporary tables rather than having users access a single persistent materialized view. This copying approach allows each session to have its own local copy that can be freely accessed and modified without affecting other sessions, eliminating lock contention while maintaining data reuse benefits.
Data Source
AI summary
A system includes reception of a first query including a first temporary materialized view, where the first temporary materialized view associated with a first view and a first temporary table. The first temporary table is refreshed if it is determined that the first temporary materialized view is stale, and the first query is executed using a result set cached in the first temporary table. The first temporary table caches the result set for the remainder of the current database session.