Inline optimization method and device for SQL callable routine, equipment and medium
By compiling and inlining SQL callable routines to optimize execution, a new execution plan is generated, which solves the performance loss problem when SQL callable routines call sub-methods and improves execution performance.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-11-27
- Publication Date
- 2026-03-10
AI Technical Summary
In existing database systems, SQL callable routines need to generate function stack frames corresponding to sub-methods when calling sub-methods, which impacts performance. Inline unrolling is not supported, resulting in performance loss.
By compiling the SQL callable routines, an initial execution plan is generated, the list of sub-methods is parsed, and each sub-method that can be inlined is inlined and expanded one by one to generate a new execution plan. The current execution plan is then updated until all sub-methods are processed, resulting in the optimized target execution plan.
This eliminates the context switching overhead caused by switching stack frames during SQL callable routines, thus improving the execution performance of SQL callable routines.
Smart Images
Figure CN121635879A_ABST
Abstract
Description
TECHNICAL FIELD
[0001] Embodiments of the present disclosure relate to the technical field of database, and particularly relate to a method, apparatus, device and medium for inline optimization of SQL callable routine. BACKGROUND
[0002] Structured Query Language (SQL) callable routine reduces repeated parsing time through pre-compilation, because encapsulating complex SQL logic realizes code reuse and security control functions. However, the current database system does not support inline expansion of SQL routine, and when a sub-method is called in the SQL routine, a function stack frame corresponding to the sub-method needs to be generated, and a series of operations such as parameter stack push and pop, function call, function state saving and restoring are performed, which will affect the overall performance of the SQL routine. SUMMARY
[0003] Embodiments of the present disclosure provide a method, apparatus, device and medium for inline optimization of SQL callable routine, which saves the context switching cost caused by switching stack frames when the SQL callable routine is called, thereby improving the execution performance of the SQL callable routine.
[0004] In a first aspect, a method for inline optimization of SQL callable routine is provided, comprising:
[0005] compiling the SQL callable routine to obtain an initial execution plan;
[0006] parsing the initial execution plan to determine a sub-method list, the sub-method list including at least one sub-method in the SQL callable routine that can be expanded inline;
[0007] setting the initial execution plan as a current execution plan;
[0008] traversing each sub-method in the sub-method list and performing the following loop operation: performing inline expansion operation on a target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is the sub-method currently traversed in the sub-method list;
[0009] determining the current execution plan obtained after the loop operation is completed as an optimized target execution plan to complete the inline optimization of the SQL callable routine.
[0010] In a second aspect, a device for inline optimization of SQL callable routine is provided, comprising:
[0011] an initial execution plan determination module, configured to compile the SQL callable routine to obtain an initial execution plan;
[0012] a sub-method list determination module, configured to parse the initial execution plan to determine a sub-method list, the sub-method list including at least one inline-expandable sub-method in the SQL callable routine;
[0013] a setting module, configured to set the initial execution plan as a current execution plan;
[0014] a plan updating module, configured to traverse each sub-method in the sub-method list and perform the following loop operation: performing an inline expansion operation on a target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is a sub-method currently traversed in the sub-method list;
[0015] an inline optimization module, configured to determine the current execution plan obtained after the loop operation is completed as an optimized target execution plan to complete the inline optimization of the SQL callable routine.
[0016] In a third aspect, an electronic device is provided, comprising:
[0017] at least one processor; and
[0018] a memory communicatively connected to the at least one processor; wherein
[0019] the memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to perform the inline optimization method of the SQL callable routine according to the first aspect.
[0020] In a fourth aspect, a computer readable storage medium is provided, which stores a computer program, and the program is executed by a processor to implement the inline optimization method of the SQL callable routine according to the first aspect.
[0021] In a fifth aspect, a computer program product is provided, which includes a computer program, and the computer program is executed by a processor to implement the inline optimization method of the SQL callable routine according to the first aspect.
[0022] The embodiment of the present disclosure discloses a method, device, equipment and medium for inline optimization of a SQL callable routine, the method comprising: compiling a SQL callable routine to obtain an initial execution plan; parsing the initial execution plan to determine a sub-method list, the sub-method list including at least one sub-method in the SQL callable routine that can be inlined and expanded; setting the initial execution plan as a current execution plan, traversing each sub-method in the sub-method list, and performing the following loop operation: performing an inline expansion operation on a target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is the sub-method currently traversed in the sub-method list; determining the current execution plan obtained after the loop operation is completed as an optimized target execution plan to complete the inline optimization of the SQL callable routine. The technical solution generates an initial execution plan by compiling a SQL callable routine, determines a sub-method list that can be inlined and expanded by parsing the plan, then inlines and expands each sub-method, constantly updates the execution plan, and finally obtains an optimized target execution plan, thereby completing the inline optimization of the SQL callable routine, saving the context switching cost caused by switching stack frames when the SQL callable routine is called, and improving the execution performance of the SQL callable routine.
[0023] It should be understood that the content described in this part is not intended to identify key or important features of the embodiments of the present disclosure, nor is it used to limit the scope of the embodiments of the present disclosure. Other features of the embodiments of the present disclosure will become apparent from the following description. BRIEF DESCRIPTION OF DRAWINGS
[0024] In order to more clearly illustrate the technical solutions in the embodiments of the present disclosure, the drawings needed in the embodiment description will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments of the present disclosure, and other drawings can be obtained by those skilled in the art without creative labor.
[0025] Figure 1 is a flowchart of a SQL callable routine inline optimization method provided by the first embodiment of the present disclosure;
[0026] Figure 2 is an execution process schematic diagram of another SQL callable routine inline optimization method provided by the first embodiment of the present disclosure;
[0027] Figure 3 is a structural schematic diagram of a SQL callable routine inline optimization device provided by the second embodiment of the present disclosure;
[0028] Figure 4is a structural schematic diagram of an electronic device provided by Embodiment Three of the present disclosure. DETAILED DESCRIPTION
[0029] In order for those skilled in the art to better understand the solutions of the embodiments of the present disclosure, the technical solutions in the embodiments of the present disclosure will be clearly and completely described below in conjunction with the drawings in the embodiments of the present disclosure. Obviously, the described embodiments are only part of the embodiments of the present disclosure, not all. Based on the embodiments in the present disclosure, all other embodiments obtained by those skilled in the art without creative work should fall within the scope of protection of the present disclosure.
[0030] It should be noted that the terms "first", "second" and the like in the specification and claims of the present disclosure and the above-described drawings are used to distinguish similar objects, and do not necessarily indicate a specific order or a chronological sequence. It should be understood that the data thus used can be interchanged under appropriate circumstances, so that the present disclosure described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "include" and "have" and any variations thereof are intended to cover non-exclusive inclusion, for example, a process, method, system, product or device that includes a series of steps or units does not have to be limited to those steps or units clearly listed, but can include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.
[0031] Embodiment One
[0032] Figure 1 A flowchart of a SQL callable routine inline optimization method provided by Embodiment One of the present disclosure, the present embodiment can be applicable to the case of inline optimization of a SQL callable routine, the method can be executed by a SQL callable routine inline optimization device, the SQL callable routine inline optimization device can be realized in the form of hardware and / or software, and the SQL callable routine inline optimization device can be configured in an electronic device, which includes but is not limited to a computer, a computer, an electronic device, and a server and other devices with data processing capability. As shown in the figure, the method comprises: Figure 1
[0033] S110, compiling the SQL callable routine to obtain an initial execution plan.
[0034] In this embodiment, the SQL callable routine can be a program unit that can be invoked in a SQL environment, which can be a stored procedure or a SQL function. The SQL function can be a reusable code unit in a database, which is predefined or user-defined, for completing specific database computation or processing. It receives input parameters, performs operations through encapsulated logic, and returns a unique result value, which is often used as part of a query. The stored procedure can be a stored procedure of a database, which can be a set of SQL statements stored together for completing a specific function. The SQL statements in the stored procedure are pre-compiled and stored in the database. A user can call the stored procedure by specifying the name of the stored procedure and giving the required parameters to achieve a specific set of data operations. The stored procedure can support parameter passing, flow control, and transaction management, and encapsulate multiple SQL operations on data, which can greatly reduce the cost of network communication and improve execution efficiency.
[0035] Specifically, the SQL callable routine can be obtained, and the SQL callable routine can be compiled to obtain an initial execution plan. The SQL method compilation can refer to a process in which a database system converts source code in a SQL callable routine into machine instructions or intermediate code that can be directly executed by the database, with the purpose of efficient execution in subsequent calls and avoiding repeated parsing of source code each time the call is made. The initial execution plan can be an internal strategy document generated by the database before executing a SQL statement, which is used to describe the execution steps, resource allocation, and optimization path of the SQL statement in detail. The SQL plan can mainly include operators, operation costs, execution paths, correlations, and statistical information.
[0036] In S120, the initial execution plan is parsed to determine a sub-method list, and the sub-method list includes at least one inline-expandable sub-method in the SQL callable routine.
[0037] It can be understood that after obtaining the initial execution plan, the initial execution plan can be parsed, that is, the execution strategy in the initial execution plan can be analyzed in detail to understand the specific operations and logic contained therein. By parsing the initial execution plan, a sub-method list can be determined. The sub-method list can be a collection of all sub-methods identified after parsing the initial execution plan, and the sub-method list includes at least one inline-expandable sub-method in the SQL callable routine. The sub-method can be a smaller, independently executable logic unit defined in the SQL callable routine. These sub-methods can be other stored procedures, functions, or subroutines called in the current method.
[0038] In connection with the foregoing description, in-line expansion is an optimization technique that directly embeds the implementation of a sub-method into the code that calls it, rather than executing it through a function call (i.e. all the sub-method code within a SQL function or stored procedure is expanded to the outermost level as part of the execution code in the outer method). This can reduce the overhead of function calls and improve execution efficiency. The sub-methods that can be in-line expanded are those that are suitable for such optimization.
[0039] By way of example, all the sub-methods can be identified from the initial execution plan. From the identified sub-methods, those that are suitable for in-line expansion can be selected according to the selection criteria of the sub-methods that can be in-line expanded. These sub-methods will be recorded in the sub-method list. The sub-methods that can be in-line expanded can have the following characteristics: 1) there are no special SQL statements in the sub-method, such as exception handling, etc.; 2) the number of rows of the sub-method is small, and the number of execution instructions after replacement is small; 3) there is a limit to the number of parameters in the sub-method; 4) there is a limit to the number of variables in the sub-method; 5) there are restrictions on optimization parameters, etc.
[0040] S130, setting the initial execution plan as the current execution plan.
[0041] Specifically, the initial execution plan can be set as the current execution plan. The current execution plan refers to the execution plan that is currently being processed in the optimization process. It is a dynamic concept that will be updated continuously as the optimization process proceeds. Setting the initial execution plan as the current execution plan means that at the beginning of the optimization process, the initial execution plan is used as the basis for subsequent optimization operations. This step is the starting point of the optimization process, and subsequent optimization operations (such as in-line expansion, etc.) will be based on this current execution plan.
[0042] S140, performing in-line expansion operation on the target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is the sub-method currently being traversed in the sub-method list.
[0043] Specifically, each sub-method in the sub-method list can be traversed, and the sub-method currently being traversed can be determined as the target sub-method. The in-line expansion operation is performed on the target sub-method in the current execution plan. After the in-line expansion operation, the current execution plan is modified to generate a new execution plan. This new execution plan contains the in-line expanded code, and the newly generated execution plan is set as the current execution plan, so that subsequent optimization operations can be based on this new execution plan. This is an iterative process, and after each in-line expansion operation, the current execution plan is updated until all sub-methods suitable for in-line expansion are processed.
[0044] S150, judging whether each sub-method in the sub-method list is traversed. If yes, S160 is executed, if not, S140 is executed.
[0045] It can be known that in the optimization process, the sub-methods in the sub-method list can be processed one by one. After processing each sub-method, the current execution plan is updated. It is judged whether each sub-method in the sub-method list is traversed. If all the sub-methods are traversed, it means that the optimization process is completed. At this time, S160 step is executed. If there are still sub-methods that have not been traversed, it means that the optimization process has not been completed. At this time, S140 step is executed, and the next sub-method is continued to be processed.
[0046] S160, the current execution plan obtained after the loop operation is completed is determined as the target execution plan after optimization, so as to complete the inline optimization of the SQL callable routine.
[0047] In this embodiment, when all the sub-methods are traversed, the current execution plan already contains all the optimization results of the inline expansion. At this time, the current execution plan is determined as the target execution plan after optimization, and the inline optimization of the SQL callable routine is completed.
[0048] The embodiment provides an inline optimization method of a SQL callable routine, which comprises the following steps: compiling a SQL callable routine to obtain an initial execution plan; parsing the initial execution plan to determine a sub-method list, wherein the sub-method list comprises at least one sub-method in the SQL callable routine which can be expanded in line; setting the initial execution plan as a current execution plan, traversing each sub-method in the sub-method list, and executing the following loop operation: performing an inline expansion operation on a target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is a sub-method currently traversed in the sub-method list; and determining the current execution plan obtained after the loop operation is completed as a target execution plan after optimization, so as to complete the inline optimization of the SQL callable routine. The technical scheme saves the context switching cost caused by switching the stack frame of the SQL callable routine when calling, so that the execution performance of the SQL callable routine is improved.
[0049] As an optional implementation manner of the embodiment, the operation of performing an inline expansion operation on the target sub-method in the current execution plan to generate a new execution plan comprises the following steps:
[0050] 1) determining a variable corresponding to the target sub-method as a target new variable in the current execution plan.
[0051] Specifically, after the target sub-method is determined, variables (such as local variables, parameters, etc.) used in the target sub-method can be added to the current execution plan, so that after inlining expansion, these variables can be correctly used and managed in the current execution plan. Among them, the SQL method variable can be the variable information defined in the SQL function or stored procedure, saved on the runtime method, and the temporary variable information will also be generated in the planning generation stage. Each variable has a corresponding variable number on the runtime method, and each variable on the value stack of the runtime method is stored in order according to the variable number and the stack space occupied by the variable.
[0052] 2) According to the preset parameter mapping relationship, the parameter value input when the target sub-method is called is assigned to the corresponding variable in the current execution plan to determine the variable value.
[0053] In this embodiment, the preset parameter mapping relationship can be a mapping relationship pre-set to describe how the parameters input when the target sub-method is called are mapped to the variables inside the target sub-method. According to the preset parameter mapping relationship, the parameter value input when the target sub-method is called can be assigned to the corresponding variable in the current execution plan to determine the variable value. It can be understood that the parameter value input can be assigned to the corresponding variable in the current execution plan according to the preset parameter mapping relationship, to ensure that these variables have correct values after inlining expansion. Among them, the parameters of the SQL method can be the parameter information defined when the SQL function or stored procedure is defined, saved on the runtime method, and each parameter has a corresponding parameter number. During actual execution, the calling method pushes the parameter data onto the stack, and the called method obtains the pushed parameter information from the value stack during execution. The value stack can refer to the stack space of the virtual machine execution stage, used to save the information required in the current execution stage, including parameters, variables, etc. The instruction execution stage uses the data on the top of the stack, and after execution, the data is pushed to the top of the stack. Data interaction between instructions is performed through the stack space. Before the calling instruction of the SQL callable routine, the parameter information used by the called function needs to be pushed onto the value stack
[0054] 3) Based on the variable number corresponding to the target new variable and the variable value, a function body execution instruction corresponding to the target sub-method is generated. The variable number is the number of the target new variable in the current execution plan.
[0055] It can be known that the variables of the target sub-method will be the target new variables in the current execution plan, that is, the variables in the current execution plan will be increased, and each new variable has a unique number in the current execution plan, which is used to identify and manage these variables.
[0056] According to the above description, the function body execution instruction corresponding to the target sub-method can be generated based on the variable number corresponding to the target newly added variable and the variable value. The variable number can be the number of the target newly added variable in the current execution plan. The function body execution instruction can be a code segment generated after the target sub-method is inlined, which contains the implementation logic of the target sub-method. The function body execution instruction can also describe the specific operations of the virtual machine in executing the SQL plan, including data loading, calculation, storage, and function calling, etc. The SQL callable routine generates a calling instruction of the SQL callable routine when called, which contains the id information of the function.
[0057] It should be noted that after the function body is generated and before the inlining optimization is performed, when a sub-method is called, the parameter values usually need to be pushed into the call stack. The call stack is a data structure used to store function calling information. When a function is called, the parameter values of the function are pushed into the call stack so as to use the parameters in the function body. After the sub-method is inlined, the parameter pushing operation that needs to be performed when the sub-method is called is no longer needed, because the function body of the sub-method has been directly embedded into the calling point, and the use of the parameters in the function body has been replaced by the calling of the global variables. The SQL function stack frame can be a stack frame generated during the execution of the SQL callable routine, which stores the start address of the current function in the value stack of the virtual machine, the stack space of the variables and parameters, the function calling and returning points, the parameters used by the current function, the positions of the variables in the value stack of the virtual machine, and the specific execution instructions of the current function in the virtual machine, etc.
[0058] 4) storing the function body execution instruction into a target storage location in the current execution plan to generate a new execution plan.
[0059] Specifically, after the function body execution instruction is obtained, the function body execution instruction can be stored into a target storage location in the current execution plan to generate a new execution plan. The target storage location can be the storage location of the calling instruction of the target sub-method.
[0060] As an optional implementation of the embodiment, the SQL callable routine inlining optimization method provided by the embodiment further includes the following steps after the function body execution instruction is stored into the target storage location in the current execution plan to generate a new execution plan.
[0061] 1) in the case where the function calling instruction exists in the function body execution instruction, determining whether there is a sub-method that can be inlined in the function body execution instruction.
[0062] It should be noted that after storing the function body execution instruction to the target storage location in the current execution plan, it can also be determined whether the function call instruction is contained in the function body execution instruction (that is, whether the function call operation needs to be performed). If there is a function call instruction, it can be determined whether there are other sub-methods that can be inlined in the generated function body execution instruction after inlining the target sub-method. In the generated function body execution instruction, there can also be other sub-method calls. These sub-methods can also be inlined if they meet the conditions for inlining (such as small code size, frequent calls, etc.).
[0063] 2) If there is, perform the same inlining operation on the inlinable sub-methods in the function body execution instruction as the target sub-method.
[0064] Specifically, if the inlinable sub-methods found in the function body execution instruction can perform the same inlining operation as the target sub-method. That is, after inlining a sub-method, if a new sub-method is found that can be inlined, continue to inlining these sub-methods. It should be noted that the maximum expansion depth in the inlining operation process can also be controlled by setting the optimization parameter. If the maximum expansion depth is not set, only one expansion can be performed.
[0065] As an optional implementation of the embodiment, the function body execution instruction corresponding to the target sub-method is generated based on the variable number corresponding to the target new variable and the variable value, and further includes:
[0066] 1) Determine the variable number sequence corresponding to the current execution plan.
[0067] Specifically, after determining the variable corresponding to the target sub-method as the target new variable in the current execution plan, the variables (such as local variables, parameters, etc.) used inside the target sub-method can be added to the current execution plan. Then the variable number sequence corresponding to the current execution plan can be determined. The variable number is a unique identifier assigned by the database system to each variable in the execution plan, which is used to correctly reference and manage these variables during execution. By determining the variable number sequence, it can be ensured that the newly introduced variable can be correctly integrated into the current execution plan in the subsequent steps.
[0068] 2) Determine the variable number corresponding to the target new variable based on the variable number sequence.
[0069] Specifically, after determining the variable number sequence, the variable number corresponding to the target new variable can be determined based on the variable number sequence.
[0070] 3) recompile the function body of the target sub-method based on the variable number and the variable value to generate the function body execution instruction.
[0071] It can be known that the function body of the target sub-method can be recompiled according to the variable number and the variable value of the target new variable to generate specific function body execution instructions, which will be embedded into the current execution plan. During the recompilation process, the variable number and the variable value are correctly mapped to the function body, ensuring that the generated function body execution instruction can correctly operate the variables when executed.
[0072] As an optional implementation of the embodiment, the storing the function body execution instruction to the target storage location in the current execution plan further includes:
[0073] 1) determining a target storage location in the current execution plan; the target storage location is the storage location of the calling instruction corresponding to the target sub-method.
[0074] Specifically, after obtaining the function body execution instruction, the specific location of the calling instruction of the target sub-method in the current execution plan can be found. The target storage location refers to the specific location where the calling instruction of the target sub-method is stored in the execution plan.
[0075] 2) replacing the calling instruction corresponding to the target sub-method with the function body execution instruction at the target storage location.
[0076] Specifically, after the target storage location is determined, the calling instruction of the target sub-method can be replaced with the function body execution instruction after inline expansion. Specifically, the original calling instruction corresponding to the target sub-method is deleted at the target storage location, and the newly generated function body execution instruction is inserted.
[0077] As an optional implementation of the embodiment, the SQL callable routine inline optimization method provided by the embodiment further includes, before the replacing the calling instruction corresponding to the target sub-method with the function body execution instruction:
[0078] adding a start execution instruction at a position adjacent to the target storage location, and adding an end execution instruction at a position adjacent to the target storage location.
[0079] It needs to be explained that a start execution instruction and an end execution instruction can also be added before and after the target storage location respectively. These instructions can be used to mark the start and end of the function body execution instruction after inline expansion, which helps to better manage and optimize the execution plan.
[0080] The overall idea of the technical solution is as follows: the SQL routine is initially compiled to generate an execution plan, at this time, no attempt is made to perform inline expansion optimization. After the initial plan is generated, the plan is added to the plan cache, at this time the system will start an asynchronous task to attempt to perform inline expansion optimization on the SQL routine. First, analyze the SQL sub-method, if the sub-method can be expanded inline, attempt to replace the call of the parameters and local variables in the sub-method function body, generate a new SQL sub-method instruction and insert it into the original function call point in the outer SQL routine instruction to obtain a new SQL execution plan. If there are still calls to the sub-method in the expanded SQL sub-method, continue to perform the SQL sub-method inline optimization operation. Finally, all sub-methods and nested sub-methods in the original SQL will be expanded to the outermost SQL routine, at this time the obtained execution plan replaces the original execution plan in the system plan cache. Through the inline expansion of the SQL routine, the generation and call overhead of the sub-method function stack during the execution of the SQL routine can be greatly reduced, and the execution performance of the SQL routine will be greatly improved. The above technical solution analyzes whether the SQL routine can be expanded inline during the system running stage, replaces the original function call execution code with the executable code of the expandable SQL sub-method, saves the context switching cost caused by switching the stack frame during the execution of the SQL routine, and improves the execution performance of the SQL routine
[0081] Figure 2 Another execution process schematic diagram of the inline optimization method of the SQL callable routine provided by the embodiment is as follows: Figure 2As shown, 1) the SQL routine can be compiled to obtain an initial execution plan and added to the plan cache. 2) The SQL plan (initial execution plan) can be parsed, all called sub-methods in the SQL routine are analyzed to determine whether the sub-methods can be expanded inline, and all SQL sub-methods that can be expanded inline are registered. 3) The registered SQL sub-methods are obtained, and the local variables of the SQL sub-methods are replaced with constants as local variables defined at the outermost level. 4) The local variable numbers used by the sub-methods are replaced with new outer variable information, and the parameter information used by the sub-methods is replaced with the corresponding variable information passed in the outer method (initial execution plan). 5) The sub-methods are recompiled according to the replaced information and new function body execution instructions are generated. 6) At the same time, the call function parameter information that needs to be pushed onto the stack before the SQL sub-method call instruction in the outer method can also be removed, because the SQL sub-function body no longer needs to load parameters, and the use of parameters in the function body has been replaced with a call to a global variable. 7) Replacement of the sub-method function call instruction in the outer method: find the instruction position of the outer method calling the sub-method, add start and end distinguishing instructions for sub-method replacement as an interface for sub-method instruction replacement to mark the position of sub-method replacement, and insert new sub-method function body execution instructions at the interface instruction position. 8) The new SQL plan can continue to be searched for sub-method calls within the sub-method, and if there are, step 2) is performed to expand the sub-method and the nested sub-method inline; if not, step 3) is performed to expand the next inlineable SQL sub-method, and this is done until all registered SQL sub-methods are processed. 9) The final SQL execution plan is obtained to replace the original SQL execution plan.
[0082] It can be known that after the SQL routine inline expansion optimization, the SQL sub-methods used in the original complex SQL are expanded to the outermost SQL method, at this time the call instruction for other SQL sub-methods in the outer method is directly replaced with the function body execution instruction corresponding to the SQL sub-method and inserted into the execution instruction of the outermost SQL, and the SQL routine execution stage no longer needs to call the sub-method and generate the function stack frame of the sub-method, saving the function context switching cost generated when the sub-method is called, greatly improving the execution efficiency of the complex SQL.
[0083] Embodiment Two
[0084] Figure 3 is a structural schematic diagram of a SQL callable routine inline optimization device provided by Embodiment Two of the present disclosure; as Figure 3 shown, the device comprises an initial execution plan determination module 210, a sub-method list determination module 220, a setting module 230, a plan updating module 240, and an inline optimization module 250.
[0085] The initial execution plan determination module 210 is configured to compile the SQL callable routine to obtain an initial execution plan.
[0086] The sub-method list determination module 220 is configured to parse the initial execution plan to determine a sub-method list, wherein the sub-method list includes at least one inline-expandable sub-method in the SQL callable routine.
[0087] The setting module 230 is configured to set the initial execution plan as a current execution plan.
[0088] The plan updating module 240 is configured to traverse each sub-method in the sub-method list and perform the following loop operation: performing an inline expansion operation on a target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is a sub-method currently traversed in the sub-method list.
[0089] The inline optimization module 250 is configured to determine the current execution plan obtained after the loop operation is completed as an optimized target execution plan, so as to complete the inline optimization of the SQL callable routine.
[0090] Embodiment two of the present disclosure provides an inline optimization device of a SQL callable routine, which eliminates the context switching cost caused by switching stack frames when the SQL callable routine is called, thereby improving the execution performance of the SQL callable routine.
[0091] Further, the plan updating module 240 further includes:
[0092] The target new variable determination unit is configured to determine a variable corresponding to the target sub-method as a target new variable in the current execution plan.
[0093] The variable value determination unit is configured to assign a parameter value input when the target sub-method is called to a corresponding variable in the current execution plan according to a preset parameter mapping relationship, so as to determine a variable value.
[0094] The instruction generation unit is configured to generate a function body execution instruction corresponding to the target sub-method based on a variable number corresponding to the target new variable and the variable value; the variable number is a number of the target new variable in the current execution plan.
[0095] The generation unit is configured to store the function body execution instruction to a target storage location in the current execution plan to generate a new execution plan.
[0096] Further, the device further includes:
[0097] The sub-method determination module is configured to determine whether there is a sub-method that can be inlined and expanded in the function body execution instruction if there is a function call instruction in the function body execution instruction.
[0098] The execution module is configured to perform the same inlining and expanding operation on the sub-method that can be inlined and expanded in the function body execution instruction if there is such a sub-method.
[0099] Further, the instruction generation unit is further configured to:
[0100] determine a variable number sequence corresponding to the current execution plan;
[0101] determine a variable number corresponding to the target new variable based on the variable number sequence;
[0102] recompile a function body of the target sub-method based on the variable number and the variable value to generate the function body execution instruction.
[0103] Further, the generation unit is further configured to:
[0104] determine a target storage location in the current execution plan; the target storage location is a storage location of a call instruction corresponding to the target sub-method;
[0105] replace the call instruction corresponding to the target sub-method with the function body execution instruction at the target storage location.
[0106] Further, the apparatus further comprises:
[0107] The instruction addition module is configured to add a start execution instruction at a position adjacent to the target storage location and add an end execution instruction at a position adjacent to the target storage location.
[0108] The SQL callable routine inlining optimization apparatus provided by the embodiments of the present disclosure can perform the SQL callable routine inlining optimization method provided by any of the embodiments of the present disclosure, and has the corresponding function modules and beneficial effects of the execution method.
[0109] Embodiment three
[0110] Figure 4A structural diagram of an electronic device 10 that can be used to implement embodiments of the present disclosure is shown. The electronic device is intended to represent various forms of digital computers, such as laptops, desktops, tablets, personal digital assistants, servers, blade servers, mainframes, and other appropriate computers. The components shown here, their connections and relationships, and their functions, are meant to be examples only, and are not intended to limit implementations of the present disclosure described and / or claimed in this document.
[0111] As shown in Figure 4 The electronic device 10 includes at least one processor 11, and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., connected in communication with the at least one processor 11, where the memory stores computer programs executable by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer programs stored in the read-only memory (ROM) 12 or loaded into the random access memory (RAM) 13 from the storage unit 18. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.
[0112] Various components in the electronic device 10 are connected to the I / O interface 15, including an input unit 16, such as a keyboard, a mouse, etc., an output unit 17, such as various types of displays, a speaker, etc., a storage unit 18, such as a magnetic disk, an optical disk, etc., and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network, such as the Internet, and / or various telecommunication networks.
[0113] The processor 11 can be various general and / or special-purpose processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any appropriate processor, controller, microprocessor, etc. The processor 11 performs various methods and processes described above, such as the inline optimization method of the SQL callable routine.
[0114] In some embodiments, the inlining optimization method for SQL callable routines may be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or mounted on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the inlining optimization method for SQL callable routines described above may be performed. Alternatively, in other embodiments, processor 11 may be configured to execute the inlining optimization method for SQL callable routines by any other suitable means (e.g., by means of firmware).
[0115] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), systems-on-a-chip (SoCs), payload-programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.
[0116] Computer programs for implementing the methods of embodiments of this disclosure may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.
[0117] In the context of embodiments of this disclosure, a computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. A computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, a computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.
[0118] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).
[0119] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.
[0120] A computing system can include clients and servers. Clients and servers are generally located far apart and typically interact through communication networks. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system to address the shortcomings of traditional physical hosts and VPS services, such as high management difficulty and weak business scalability.
[0121] It should be understood that the various forms of processes shown above can be used to reorder, add, or delete steps. For example, the steps described in the embodiments of this disclosure can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of the embodiments of this disclosure can be achieved, and this document does not impose any limitations.
[0122] The specific embodiments described above do not constitute a limitation on the scope of protection of the embodiments disclosed herein. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of the embodiments disclosed herein should be included within the scope of protection of the embodiments disclosed herein.
[0123] This disclosure also provides a computer program product, including a computer program and / or instructions, which, when executed by a processor, implements the inline optimization method for SQL callable routines as provided in any embodiment of this application.
[0124] In implementing a computer program product, computer program code for performing the operations of the embodiments of this disclosure can be written in one or more programming languages or a combination thereof. Programming languages include object-oriented programming languages such as Java, Smalltalk, and C++, as well as conventional procedural programming languages such as C or similar languages. The program code can be executed entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving remote computers, the remote computer can be connected to the user's computer via any type of network—including a local area network (LAN) or a wide area network (WAN)—or can be connected to an external computer (e.g., via the Internet using an Internet service provider).
[0125] Note that the above are merely preferred embodiments and the technical principles applied in this disclosure. Those skilled in the art will understand that this disclosure is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions can be made without departing from the protection scope of this disclosure. Therefore, although the embodiments of this disclosure have been described in detail above, this disclosure is not limited to the above embodiments. More other equivalent embodiments may be included without departing from the concept of this disclosure, and the scope of this disclosure is determined by the scope of the appended claims.
Claims
1. A method for inline optimization of SQL callable routines, characterized by, The method comprises: compiling a SQL callable routine to obtain an initial execution plan; parsing the initial execution plan to determine a sub-method list, the sub-method list including at least one sub-method in the SQL callable routine that can be inlined and unfolded; setting the initial execution plan as a current execution plan; traversing each sub-method in the sub-method list and performing the following loop operation: performing inlining and unfolding operation on a target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is the sub-method currently traversed in the sub-method list; determining the current execution plan obtained after the loop operation is completed as an optimized target execution plan to complete the inlining optimization of the SQL callable routine.
2. The method of claim 1, wherein, The method of performing inlining and unfolding operation on the target sub-method in the current execution plan to generate a new execution plan comprises: determining a variable corresponding to the target sub-method as a target new variable in the current execution plan; assigning a parameter value input when the target sub-method is called to a corresponding variable in the current execution plan according to a preset parameter mapping relationship to determine a variable value; generating a function body execution instruction corresponding to the target sub-method based on a variable number corresponding to the target new variable and the variable value; the variable number is the number of the target new variable in the current execution plan; storing the function body execution instruction to a target storage location in the current execution plan to generate a new execution plan.
3. The method of claim 2, wherein, After the function body execution instruction is stored to the target storage location in the current execution plan to generate a new execution plan, the method further comprises: in the case that there is a function call instruction in the function body execution instruction, determining whether there is a sub-method that can be inlined and unfolded in the function body execution instruction; if there is, performing the same inlining and unfolding operation on the sub-method that can be inlined and unfolded in the function body execution instruction as the target sub-method.
4. The method of claim 2, wherein, The method of generating a function body execution instruction corresponding to the target sub-method based on a variable number corresponding to the target new variable and the variable value comprises: determining a variable number sequence corresponding to the current execution plan; determining the variable number corresponding to the target new variable based on the variable number sequence; recompiling the function body of the target sub-method based on the variable number and the variable value to generate the function body execution instruction.
5. The method of claim 2, wherein, The method of storing the function body execution instruction to the target storage location in the current execution plan comprises: determining a target storage location in the current execution plan; the target storage location is a storage location of a call instruction corresponding to the target sub-method; replacing the call instruction corresponding to the target sub-method with the function body execution instruction at the target storage location.
6. The method of claim 5, wherein, Before the function body execution instruction is used to replace the call instruction corresponding to the target sub-method, the method further comprises: add a start execution instruction at a position adjacent to the target storage position, and add an end execution instruction at a position adjacent to the target storage position.
7. An apparatus for inlining optimization of a SQL callable routine, comprising: The method comprises the steps of: an initial execution plan determination module, configured to compile the SQL callable routine to obtain an initial execution plan; a sub-method list determination module, configured to parse the initial execution plan to determine a sub-method list, the sub-method list comprising at least one inline-expandable sub-method in the SQL callable routine; a setting module, configured to set the initial execution plan as a current execution plan; a plan updating module, configured to traverse each sub-method in the sub-method list, and perform the following loop operation: performing an inline expansion operation on a target sub-method in the current execution plan to generate a new execution plan; and updating the new execution plan as the current execution plan; wherein the target sub-method is a sub-method currently being traversed in the sub-method list; an inline optimization module, configured to determine the current execution plan obtained after the loop operation is completed as an optimized target execution plan, to complete the inline optimization of the SQL callable routine.
8. An electronic device, comprising: The method comprises the steps of: at least one processor; and a memory connected in communication with the at least one processor; wherein the memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to perform the inline optimization method of the SQL callable routine according to any one of claims 1-6.
9. A computer readable storage medium having stored thereon a computer program, characterized in that, The program is executed by the processor to implement the inline optimization method of the SQL callable routine according to any one of claims 1-6.
10. A computer program product, characterised in that, The computer program product comprises a computer program, and the computer program implements the inline optimization method of the SQL callable routine according to any one of claims 1-6 when executed by the processor.