Single Value Cache for Database Read Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In enterprise resource planning (ERP) scenarios, the existing locking mechanisms for reading single values from column-store database tables introduce significant overhead, limiting parallel user operations and leading to user dissatisfaction, revenue loss, and potential rejection of the ERP application due to high user lockout and poor performance.
Innovation Solution
Implementing a single value cache (SVC) that stores single or unique data values in database table metadata, allowing data to be retrieved without locking the column, thereby eliminating the need for traditional locking mechanisms and enhancing scalability and performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional locking mechanisms are used to read single values from column-store database tables, then data consistency is ensured, but significant overhead is introduced and parallel user operations are limited
Solution Approach 1:
The patent segments the database column into two parts: a single-value cache storing the current single value for each column, and the main column-store table storing historical and multi-value data. This segmentation allows readers to access the cached single value without locking the entire column, while writers continue to update the main table, thus resolving the contradiction between data consistency and parallel operations.
Solution Approach 2:
The single-value cache acts as an intermediary between readers and the main column-store table. Readers query the cache first, which provides the current single value without requiring locks on the main table. This intermediary mechanism enables high-speed read operations while maintaining data consistency, as the cache is updated during exclusive lock phases when writers have completed their operations.
2Reliability
If the whole column is locked to read a single value, then data consistency is maintained, but notable overhead penalty is introduced
Solution Approach 1:
The system performs preliminary actions by maintaining a single-value cache that is updated during exclusive lock phases before read operations occur. This preliminary caching of single values allows subsequent read operations to access data immediately without acquiring locks, eliminating the time overhead associated with traditional locking mechanisms while ensuring data consistency.
3Reliability
If traditional column-store table reading is used, then all column data is accessed, but memory overhead increases and read performance decreases
Solution Approach 1:
The patent extracts the single value from each column and stores it separately in a single-value cache, removing it from the main column-store table structure. This extraction reduces memory overhead by avoiding the instantiation of basic data structures for columns with single values, while the extracted values are readily accessible in the cache for fast read operations.
Solution Approach 2:
The system applies local quality by treating columns with single values differently from columns with multiple values. The single-value cache stores only the current single value for each column, allowing the system to optimize memory usage and access patterns for this specific case, rather than using the generic column-store table structure for all columns.
Data Source
AI summary
The present disclosure describes methods, systems, and computer program products for optimizing single-value database read operations. One computer-implemented method includes initializing, by a computer, a single value cache associated with a created database table where a column of the database table contains a single or unique data value, verifying that the created database table is in a useable state, performing an operation on the database table, and retrieving the data value from the single value cache without locking the column containing the single or unique data value.