Adaptive Caching for High Volume ETL Processes
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
High volume ETL processes in MES environments face inefficiencies due to frequent database accesses for key-value pair lookups, resulting in significant network overhead and slowed processing speeds, especially when dealing with millions of rows.
Innovation Solution
A caching system is implemented that preloads a subset of key-value pairs into a cache, aiming for a predetermined hit ratio, and queues cache misses until a threshold is reached, then executes a single query to retrieve all accumulated values from the source database, reducing the need for separate database accesses.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If frequent database accesses are performed for key-value pair lookups during ETL processing, then data retrieval completeness is ensured, but network overhead increases and processing speed decreases
Solution Approach 1:
The patent implements a cache that preloads key-value pairs from the source database before the ETL process begins. This preliminary action stores frequently accessed keys and their corresponding natural keys in memory, allowing the ETL process to retrieve values without repeated database accesses, thereby reducing network overhead and improving processing speed
Solution Approach 2:
The patent introduces a cache as an intermediary layer between the source database and the ETL process. This cache acts as a mediator that intercepts lookup requests, serves cached values when available, and only accesses the database when necessary, thus reducing the frequency of database accesses and associated network overhead
2Productivity
If a cache is preloaded with key-value pairs to reduce database accesses, then processing speed improves, but cache memory is required and increases system complexity
Solution Approach 1:
The cache implementation uses a hash array data structure that automatically handles key-value pair storage and retrieval operations. The hash array provides O(1) average time complexity for lookups, and the system self-manages the caching logic without requiring complex external management, thereby improving lookup speed while keeping the system relatively simple
Solution Approach 2:
The patent uses a hash array, which is a simple in-memory data structure that is inexpensive to implement and manage. While the cache resides in memory rather than persistent storage, it provides fast access for the duration of the ETL process and is discarded afterward, offering a cost-effective solution that balances speed improvement with system complexity
3Loss of time
If all key-value pairs are loaded into cache, then all lookups can be served from memory, but memory consumption increases significantly
Solution Approach 1:
The patent implements a cache that selectively stores only the keys and their corresponding natural keys that are actually needed during the ETL process. Rather than loading all key-value pairs from the source database, the cache dynamically loads only the relevant subset, thereby reducing memory consumption while still eliminating database access delays for the required data
Data Source
Figure 1
Figure 2
Figure 3
AI summary
A method (300) and system (100) to provide a mechanism to cache key-value pairs of a lookup process during an extract transform load process of a manufacturing execution system (110). The system (100) includes a first database (105); and an extract transform load (ETL) process (115) of a manufacturing execution system (MES) (110) to extract data from the first database (115), perform a transformation of the extracted data, and load the transformed data into the second database (130).