ERP Reporting Cache Server Query Routing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing ERP reporting systems face performance issues due to slow query response times and the need for repeated database queries, which can be exacerbated by the need to traverse wide area networks, and modifying existing systems to implement caching introduces complexity and bugs.
Innovation Solution
Implementing a cache server that routes queries and query results through a database-specific driver located on the database server, avoiding direct communication between the query device and the database server, and using a custom driver to interface with the cache server, thus reducing latency and enabling caching without modifying the application layer.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If a cache server is implemented to reduce query latency, then query response time is improved, but system complexity increases due to additional routing infrastructure
Solution Approach 1:
A cache server is introduced as an intermediary component between the query application server and the database server. The cache server receives queries, checks for cached results, and routes queries to the database server when necessary. This intermediary layer provides caching functionality without requiring modifications to the application layer, thus reducing query latency while managing system complexity through a dedicated routing component.
Solution Approach 2:
The system is segmented into distinct functional components: the query application server, the cache server, and the database server. Each component has a specific responsibility - the application server handles query generation, the cache server handles result caching and routing, and the database server handles data storage and retrieval. This segmentation allows caching to be implemented independently without affecting the application logic.
2Device complexity
If direct communication between query device and database server is used, then system simplicity is maintained, but query latency increases due to WAN traversal
Solution Approach 1:
The cache server acts as a mediator that optimizes communication between the query application server and the database server. By routing queries through the cache server, the system reduces latency because the cache server can serve results from local cache without requiring full WAN traversal to the database server, while maintaining a relatively simple communication architecture through standardized routing protocols.
3Productivity
If caching is implemented by modifying the application layer, then caching functionality is achieved, but complexity and risk of bugs increase
Solution Approach 1:
Instead of modifying the application layer to implement caching, a separate cache server intermediary is introduced. This allows caching functionality to be added to the system without changing the application code, thereby maintaining productivity benefits of caching while avoiding the complexity and bug risks associated with application layer modifications.
Solution Approach 2:
The caching functionality is extracted from the application layer and placed in a separate cache server. This extraction removes the complexity of caching logic from the application code, allowing the application to remain simple while still benefiting from caching through the external cache server infrastructure.
Data Source
AI summary
This disclosure describes systems, methods, and apparatus for ERP reporting using a cache server to cache previous query results. Query latency is further reduced by routing queries and responses to queries through the cache server rather than via direct communication between a querying device and a server hosting the database. This is done by moving a driver for interfacing with the database to the server hosting the database, thus avoiding slow protocol communications between this driver and the database when such communications occur over a WAN. A custom driver is also designed to interface with the cache server and control routing of queries and responses through the cache server. Further, a cache server is selected that enables queries to be passed straight to the server hosting the database rather than the query device having to pass these queries to the database.