Deferred Stored Procedure Compilation for Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database optimization methods for stored procedures often result in inadequate performance due to the need for optimizer guesses about run-time variables, leading to inefficient access plans.
Innovation Solution
A method of deferred compilation where run-time values are substituted for variables in stored procedures before creating an access plan, optimizing the statements with these values just before execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Loss of time
If optimization is performed during procedure creation, then the system can prepare access plans in advance, but the optimizer must make guesses about run-time variables resulting in inadequate performance
Solution Approach 1:
The patent applies preliminary action by creating access plans in advance during procedure creation, but defers the actual optimization process until run-time when variable values are known. The system prepares the procedural framework beforehand while performing the critical optimization step just before execution, ensuring both advance preparation benefits and accurate run-time optimization.
Solution Approach 2:
The patent implements dynamics by transitioning from static pre-compilation optimization to dynamic deferred compilation. The access plan creation is delayed until the procedure is invoked and variable values are available, allowing the optimization process to adapt dynamically to actual run-time conditions rather than relying on static guesses.
2Ease of manufacture
If access plans are created using variable placeholders, then the procedure can be compiled in advance, but the access plans are inefficient due to lack of actual run-time values
Solution Approach 1:
The system performs preliminary compilation of the procedure structure in advance, but defers the critical access plan creation until run-time. This allows the procedural framework to be prepared beforehand while ensuring that access plans are generated with actual variable values, maintaining both compilation ease and computational efficiency.
Solution Approach 2:
The patent introduces an intermediary step of procedure invocation that bridges the gap between pre-compilation and execution. During this intermediary phase, variable values are obtained and used to generate accurate access plans, serving as a mediator between the compilation phase and the execution phase.
Data Source
AI summary
A system, method, and computer program product are provided for compiling a stored procedure in a database. The compiling includes the steps of invoking the stored procedure, the stored procedure comprising one or more statements, substituting run-time values for one or more variables in the statements, and creating an access plan for the statements having the substituted run-time values.