Column Smart Mechanism for OLTP Transaction Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Running both OLTP and OLAP systems on a column-based in-memory database, such as SAP HANA, leads to inefficiencies in OLTP performance compared to traditional row-based databases, as column-based storage requires accessing all columns, increasing response time due to unnecessary data retrieval.
Innovation Solution
Implementing a smart query optimization mechanism that utilizes a cache to determine if column information is available, allowing OLTP transactions to operate in a learned mode using only necessary columns, thereby reducing the need for querying all columns and minimizing response time.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If column-based storage is used for OLTP transactions, then data compression and OLAP query efficiency are improved, but response time increases due to accessing all columns
Solution Approach 1:
The patent segments the column storage by creating separate column caches for different OLTP applications. Each application maintains its own cache containing only the specific columns it needs, rather than accessing all columns from a unified column storage. This segmentation allows OLTP applications to retrieve only necessary column data, reducing access time while preserving the compression benefits of column-based storage for OLAP operations.
Solution Approach 2:
The patent implements preliminary action by pre-loading and caching column data into application-specific column caches before OLTP transactions occur. The system proactively identifies and stores frequently accessed columns in memory caches, so that when OLTP transactions need this data, it is already available without requiring access to the full column storage. This preliminary caching action eliminates the need to access all columns during transaction processing.
2Reliability
If all columns are accessed in column-based storage, then data integrity is maintained, but computation costs increase due to processing unnecessary data
Solution Approach 1:
The patent extracts only the necessary columns from the full column storage and places them into application-specific caches. Each OLTP application's cache contains only the subset of columns relevant to that application, eliminating the need to process or access unrelated columns. This extraction maintains data integrity for the required columns while significantly reducing computation costs by avoiding unnecessary data processing.
3Device complexity
If column-based in-memory database is used, then system complexity is reduced by consolidating OLTP and OLAP, but OLTP performance does not improve compared to row-based databases
Solution Approach 1:
The patent applies local quality by creating application-specific column caches with different characteristics optimized for each OLTP application's needs. Rather than using a uniform access pattern, each application has its own cache containing locally relevant columns with appropriate caching strategies. This allows the system to maintain the simplified column-based architecture while achieving OLTP performance improvements through localized, application-tailored data access.
Data Source
AI summary
Embodiments of the present disclosure may provide a system and method for processing an online transactional processing (OLTP) transaction on a column-based storage of a database. The method may include receiving a request of the OLTP transaction to access data on the column-based storage. A determination may be made whether a cache associated with the database includes column information for the OLTP transaction. If the cache includes the column information for the OLTP transaction, the method may include processing the OLTP transaction with the column information in the cache. If the cache does not include the column information for the OLTP transaction, the method may include selecting columns from the column-based storage of the database.