Stored Procedure Query Execution Plan for Database Module Decoupling
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems face challenges in efficiently managing different types of queries, such as OLTP and OLAP queries, which require distinct data structures, leading to complex query dispatching and tight coupling between clients and database servers, increasing maintenance costs and limiting dynamic query execution across differently structured database management systems.
Innovation Solution
A method and system that automatically computes a query execution plan to identify data containers and synchronize data between database management modules, allowing dynamic dispatch of computational tasks to the most suitable DBMM, even if the source data container does not exist in the target DBMM, by creating structurally identical data containers and resolving unsynchronized data containers, thereby decoupling clients from database servers.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If data is synchronized between multiple database management systems with differently structured tables, then queries can be performed more quickly on the appropriate DBMS, but the client must know in which DBMS a query should be executed, leading to tight coupling and increased maintenance costs
Solution Approach 1:
The patent introduces an intermediary layer (the database system with stored procedures) that automatically determines the optimal DBMS for query execution. The stored procedure acts as a mediator between the client and multiple DBMS instances, eliminating the need for clients to know which DBMS should handle specific queries while still achieving optimized query performance.
Solution Approach 2:
The patent segments the database functionality by implementing the same stored procedure in multiple DBMS instances with differently structured tables. Each DBMS instance handles specific query types more efficiently, and the system segments the workload across these instances based on query characteristics rather than requiring client-side routing decisions.
2Adaptability or versatility
If the same stored procedure is implemented in multiple database management systems, then queries can be executed on the most suitable DBMS, but dynamic dispatching is not possible due to query complexity and deviations in table structure and database-internal functions
Solution Approach 1:
The patent creates copies of the stored procedure implementation across multiple DBMS instances. Each instance contains a copy of the procedure logic adapted to its specific table structure and internal functions. This allows the system to copy and paste the same computational task across different DBMS environments, enabling flexible dispatching while managing complexity through standardized procedure templates.
Solution Approach 2:
The patent utilizes parameter changes in the query execution plan to dynamically adapt the stored procedure execution to different DBMS instances. By modifying execution parameters rather than the core procedure logic, the system can dispatch queries to different DBMS instances with differently structured tables while maintaining procedure functionality and handling deviations in database-internal functions.
3Productivity
If clients directly access database tables with specific structures optimized for their query types, then query performance is improved, but clients become tightly coupled to specific database structures, increasing maintenance costs
Solution Approach 1:
The patent extracts the query optimization logic from the client application and places it within the database system itself through stored procedures. By taking out the structural coupling requirements from the client side and embedding them in the database layer, clients can access optimized database structures without directly depending on specific table structures, thereby improving query performance while reducing client maintenance effort.
Solution Approach 2:
The stored procedure serves as an intermediary between the client and the optimized database tables. Clients interact with the standardized procedure interface rather than directly accessing table structures, allowing the database system to maintain high-performance table structures while shielding clients from structural complexity and maintenance burdens.
Data Source
AI summary
A method includes storing a first stored procedure in a first database management module and a second stored procedure in a second database management module, receiving by the first database management module a request to run a computational task with the first stored procedure, receiving a request to perform the computational task and in response thereto causing a first query optimizer in the first database management module to automatically compute a query execution plan for the first stored procedure, using the query execution plan to identify all first data containers on the first database management module that provide input data for the requested computational task, identifying a list of names of all second data containers mapped to the identified first data containers, and performing the second stored procedure using the list of names. A corresponding computer program product and data management system are also disclosed herein.