Application Server Execution Plan Generation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The generation of query execution plans in database servers is resource-intensive and heavily contends central data structures, especially for small queries, as it requires parsing and execution within the database process, leading to inefficiencies.
Innovation Solution
Generating query execution plans within the application server process using a distributed metadata cache, which de-contents central database structures, and submitting the plan to the database for execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If query execution plan generation is performed in the database server, then the execution plan can be generated using centralized metadata, but it heavily contends central data structures and increases database server load
Solution Approach 1:
The patent divides the execution plan generation process into separate components: metadata retrieval remains centralized in the database server, while parsing and plan generation are segmented and performed in the application server. This segmentation allows the database server to provide metadata without being blocked by plan generation operations, reducing contention on central data structures while maintaining execution plan correctness through coordinated metadata access.
2Productivity
If execution plan generation is moved to the application server, then database process contention is reduced, but the application server must access centralized metadata
Solution Approach 1:
The patent introduces metadata caches as intermediary components between the application server and the database server's centralized metadata. These caches store metadata locally in the application server, allowing plan generation to proceed without direct access to centralized metadata structures. This intermediary approach reduces system complexity by providing a simple cache-and-use model while maintaining the benefits of reduced database contention.
3Loss of time
If prepared statements are used, then query parsing is pre-computed, but execution plan generation still occurs in the database process
Solution Approach 1:
The patent extends the prepared statement concept by performing not only query parsing but also execution plan generation as preliminary actions in the application server. When a prepared statement is created, the application server generates the execution plan upfront using metadata retrieved from the database server. This preliminary action eliminates the need for execution plan generation during actual query execution, significantly reducing database process involvement and improving overall query execution efficiency.
Data Source
AI summary
A system and method for execution plan preparation. One or more processors executing an application process of an application server receive a request from a client computer over a network. The one or more processors parse the request to determine one or more components of the request. An execution plan for the request is generated based on the one or more components determined by the parsing, and the execution plan is then sent to a database for execution of the request by a database process.