PSM Compiler Optimizer Bulk DML Array Bind Parameters
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current PSM compilers face performance issues due to increased SQL parsing overhead and difficulty in improving efficiency during bulk operations, as they require explicit writing of bulk parameters and process each bind parameter sequentially, leading to inefficient data manipulation.
Innovation Solution
A PSM compiler optimizer that analyzes the pattern of the PSM program to generate instruction codes for bulk operations in the form of an array of bind parameters, using a virtual machine to interpret and batch-process DMLs, reducing the need for repetitive interpretation and enhancing performance by optimizing bulk cursor operations.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If bulk parameters are explicitly written and processed sequentially, then the PSM program can be executed, but the SQL parsing overhead increases and processing speed decreases
Solution Approach 1:
The patent merges multiple individual DML operations into a single bulk DML operation. Instead of processing each bind parameter separately through individual SQL parsing, the system combines them into one bulk operation that processes multiple rows simultaneously, eliminating repetitive SQL parsing overhead and significantly improving processing speed.
Solution Approach 2:
The patent performs preliminary actions by pre-compiling bulk DML statements with placeholder parameters before actual data processing. The bulk DML is prepared in advance with parameter positions marked, and only the data values need to be filled in during execution, avoiding repeated SQL parsing and compilation for each individual operation.
2Productivity
If bulk operation codes are generated to improve performance, then processing efficiency increases, but the device complexity increases due to additional components
Solution Approach 1:
The patent segments the bulk DML processing into distinct functional components: a code generator that creates bulk operation codes with placeholder parameters, a parameter binding module that binds actual values to placeholders, and an executor that runs the bulk operation. This segmentation allows each component to specialize in one task, improving overall efficiency while keeping the added complexity manageable through clear separation of concerns.
Data Source
AI summary
A PSM complier optimizer analyzes a pattern of the PSM program to generate instruction codes, which will be subjected to a bulk operation and makes the instruction codes for the bulk operation in a form of an array of bind parameters to perform a bulk cursor. Accordingly, the bulk DML processing improves the performance of the PSM program.