Client-Side Statement Cache for Database Performance
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Many database client developers are unaware of or do not utilize Dynamic Structured Query Language (DSQL) performance-enhancing techniques, leading to suboptimal database performance despite the availability of modern performance-enhancing methods.
Innovation Solution
Implementing a client-side statement cache that converts and maps application instructions into references to pre-processed database command statements stored on the server, enabling the use of DSQL instructions to improve database performance without increasing application complexity.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If developers use traditional embedded SQL instead of DSQL, then application simplicity is maintained, but database performance is suboptimal
Solution Approach 1:
The patent introduces a statement cache as an intermediary component between the application and the database. This cache stores pre-processed DSQL instructions and their corresponding execution plans, allowing the system to automatically translate traditional embedded SQL into optimized DSQL without requiring developers to manually write complex DSQL code. The intermediary handles the complexity of translation and optimization while maintaining application simplicity.
Solution Approach 2:
The system performs preliminary processing of SQL statements by pre-compiling and caching optimized DSQL versions along with their execution plans before they are actually executed. This preliminary action occurs when the statement is first encountered in the cache, and subsequent executions can directly retrieve the pre-processed version, improving performance without adding complexity to the application code.
2Productivity
If developers manually implement DSQL instructions, then database performance is improved, but application complexity increases
Solution Approach 1:
The statement cache system provides self-service by automatically detecting when a traditional embedded SQL statement needs to be converted to DSQL, retrieving the appropriate pre-processed version from the cache, and executing it without requiring developer intervention. The system monitors SQL statements, matches them against cached entries, and automatically uses the optimized versions when available, making the performance improvement transparent to the application developer.
3Productivity
If a statement cache is implemented, then database performance is enhanced through DSQL instruction execution, but system complexity increases
Solution Approach 1:
The system creates a simplified copy of the SQL statement structure (the cache entry containing the statement text and execution plan) rather than modifying the original application code. This copying approach allows the optimized DSQL version to be stored separately and retrieved as needed, improving execution efficiency while keeping the application codebase simple and unchanged.
Data Source
AI summary
Embodiments include a method, apparatus and computer program product for providing instructions from a database client to a database server. The method includes the steps of receiving from a database a representation of a first instruction and an associated identifier. The representation of the first instruction and the associated identifier are stored in a cache. A second instruction, from an application, is matched with the representation of the first instruction stored in the cache. The associated identifier is retrieved from the cache based on the matching and the associated identifier is sent to the database server. Upon receipt of the associated identifier, the database server executes the first instruction.