Performance evaluation method and device of storage process, electronic equipment and storage medium

Abstract

The invention discloses a performance evaluation method and device of a storage process, electronic equipment and a storage medium, and relates to the technical field of big data or other related fields, the method comprises the following steps: using a preset parser generator to carry out grammar parsing on a target storage process code to obtain a grammar parsing result; according to the unfavorable operation condition list, simulating unfavorable operation conditions according to the database table field information and the variable information to obtain a simulated operation parameter set; executing the SQL statements by using the simulation operation parameter set, and obtaining execution plan information and performance data corresponding to each SQL statement in the execution process; the execution plan information and the performance data are analyzed, a performance evaluation report is obtained, and SQL statements with performance scores lower than a standard threshold value are identified in the performance evaluation report. Through the method and the device, the technical problem that performance evaluation of an existing storage process depends on actual production operation parameters and cannot test and evaluate performance influence conditions in advance in the prior art is solved.

Description

Technical Field

[0001] This invention relates to the field of big data technology or other related fields. Specifically, it relates to a method and apparatus for performance evaluation of stored procedures, electronic devices, and storage media. Background Technology

[0002] In today's era of data-intensive applications, enterprise databases are bearing unprecedented pressure on data access and processing. With the explosive growth of data volume, distributed database design and architecture have become increasingly important, aiming to improve data processing efficiency and reliability to meet the demands of high concurrency and high throughput business needs. However, despite significant progress in distributed database technology, the performance evaluation of stored procedures still faces serious challenges in predicting and addressing data processing efficiency degradation under extreme conditions.

[0003] Traditional stored procedure performance evaluation methods often rely on the analysis of actual data running in a production environment. Performance bottlenecks are only detected and addressed when the stored procedure encounters large amounts of data or specific data distributions, a process that is often delayed and reactive. More problematic is that the complexity and parameter dependencies of stored procedure call chains mean that even actual production runs may not cover all potential performance risks. This not only increases maintenance costs but can also lead to compromised user experience and business interruptions due to the failure to identify and fix performance issues in a timely manner.

[0004] There is currently no effective solution to the above problems. Summary of the Invention

[0005] The main objective of this application is to provide a method, apparatus, electronic device, and storage medium for evaluating the performance of stored processes, so as to at least solve the technical problem that the performance evaluation of existing stored processes in related technologies depends on actual production operating parameters and cannot test and evaluate the performance impact in advance.

[0006] To achieve the above objectives, according to one aspect of this application, a performance evaluation method for a stored procedure is provided. The method includes: parsing the target stored procedure code using a preset parser generator to obtain a parsing result, wherein the parsing result includes SQL statements, database table field information, and variable information involved in the stored procedure; simulating unfavorable operating conditions based on a list of unfavorable operating conditions, according to the database table field information and the variable information, to obtain a set of simulated operating parameters; executing the SQL statements using the set of simulated operating parameters, and obtaining execution plan information and performance data corresponding to each SQL statement during execution; analyzing the execution plan information and the performance data to obtain a performance evaluation report, wherein the performance evaluation report identifies SQL statements whose performance scores are below a standard threshold.

[0007] Further, the step of using a preset parser generator to perform syntax parsing on the target stored procedure code to obtain the syntax parsing result includes: obtaining the target syntax structure used by the target database system, and initializing the parser generator according to the target syntax structure; preprocessing the target stored procedure code, wherein the preprocessing includes the following operations: formatting and comment cleanup; using the initialized parser generator to parse the preprocessed target stored procedure code to obtain the syntax parsing result containing the SQL statement, the database table field information, and the variable information.

[0008] Furthermore, the list of unfavorable operating conditions is constructed through the following steps: collecting performance bottleneck data of the target stored procedure in its historical execution cycles from the system database, wherein the performance bottleneck data includes at least: execution data identified as having high data volume and / or complex query patterns; analyzing each piece of performance bottleneck data based on database theory to obtain the corresponding performance impact type, wherein the performance impact type includes: missing indexes and uneven data distribution; defining the boundary range of unfavorable operating conditions by combining the performance bottleneck data and the corresponding performance impact type; and integrating the boundary ranges of the unfavorable operating conditions corresponding to all the performance bottleneck data to establish the list of unfavorable operating conditions.

[0009] Further, the step of simulating unfavorable operating conditions and obtaining a set of simulated operating parameters according to the list of unfavorable operating conditions and the database table field information and the variable information includes: for each unfavorable operating condition, mapping the boundary range of the unfavorable operating condition to the database table field corresponding to the target database according to the database table field information to obtain a first simulated operating parameter; mapping the boundary range of the unfavorable operating condition to the database variable corresponding to the target database according to the variable information to obtain a second simulated operating parameter; and integrating the first simulated operating parameter and the second simulated operating parameter to obtain the set of simulated operating parameters.

[0010] Further, the step of executing the SQL statement using the simulated running parameter set and obtaining the execution plan information corresponding to each SQL statement during execution includes: assigning values ​​to variables in the SQL statement based on the values ​​in the simulated running parameter set to obtain the SQL statement to be executed; requesting the execution plan corresponding to the SQL statement from the target database system and extracting key operators from the execution plan, wherein the key operators include: index scan operators and table scan operators; injecting the key operators into the SQL statement to be executed and executing the SQL statement; and obtaining the fluctuation information of the key operators during execution to obtain the execution plan information.

[0011] Furthermore, the step of executing the SQL statement using the simulated running parameter set and obtaining performance data corresponding to each SQL statement during the execution process includes: periodically obtaining the running monitoring data of the SQL statement during the execution process; analyzing the running monitoring data according to a preset performance indicator list to obtain the performance data, wherein the performance data includes: query duration and number of data rows.

[0012] Further, the step of analyzing the execution plan information and the performance data to obtain a performance evaluation report includes: calculating the execution plan information and the performance data according to preset scoring rules to obtain a performance score for each SQL statement; filtering SQL statements whose performance scores are lower than the standard threshold, and writing the SQL statements, corresponding performance data, and execution plan information into the performance evaluation report.

[0013] To achieve the above objectives, according to another aspect of this application, a performance evaluation apparatus for a stored procedure is also provided. The apparatus includes: a parsing unit, configured to perform syntax parsing on target stored procedure code using a preset parser generator to obtain a syntax parsing result, wherein the syntax parsing result includes SQL statements, database table field information, and variable information involved in the stored procedure; a simulation unit, configured to simulate unfavorable operating conditions according to a list of unfavorable operating conditions, based on the database table field information and the variable information, to obtain a set of simulated operating parameters; an execution unit, configured to execute the SQL statements using the set of simulated operating parameters, and to obtain execution plan information and performance data corresponding to each SQL statement during execution; and an analysis unit, configured to analyze the execution plan information and the performance data to obtain a performance evaluation report, wherein the performance evaluation report identifies SQL statements whose performance scores are below a standard threshold.

[0014] Further, the parsing unit includes: a first acquisition module, used to acquire the target syntax structure used by the target database system, and initialize the parser generator according to the target syntax structure; a preprocessing module, used to preprocess the target stored procedure code, wherein the preprocessing includes the following operations: formatting and comment cleanup; and a parsing module, used to use the initialized parser generator to parse the preprocessed target stored procedure code to obtain the syntax parsing result containing the SQL statement, the database table field information, and the variable information.

[0015] Furthermore, the performance evaluation device for the stored procedure further includes: a collection module, used to collect performance bottleneck data of the target stored procedure in historical execution cycles from the system database, wherein the performance bottleneck data includes at least: execution data identified as high data volume and / or complex query patterns; a first analysis module, used to analyze each piece of performance bottleneck data based on database theory to obtain the corresponding performance impact type, wherein the performance impact type includes: missing index and uneven data distribution; a definition module, used to define the boundary range of unfavorable operating conditions by combining the performance bottleneck data and the corresponding performance impact type; and an establishment module, used to integrate the boundary ranges of the unfavorable operating conditions corresponding to all the performance bottleneck data to establish the list of unfavorable operating conditions.

[0016] Further, the simulation unit includes: a first mapping module, used to map the boundary range of each unfavorable operating condition to the corresponding database table field of the target database according to the database table field information, to obtain a first simulation operating parameter; a second mapping module, used to map the boundary range of the unfavorable operating condition to the corresponding database variable of the target database according to the variable information, to obtain a second simulation operating parameter; and an integration module, used to integrate the first simulation operating parameter and the second simulation operating parameter to obtain the simulation operating parameter set.

[0017] Further, the execution unit includes: an assignment module, used to assign values ​​to variables in the SQL statement based on the values ​​in the simulated running parameter set, to obtain the SQL statement to be executed; an extraction module, used to request the execution plan corresponding to the SQL statement from the target database system, and extract the key operators in the execution plan, wherein the key operators include: an index scan operator and a table scan operator; an injection module, used to inject the key operators into the SQL statement to be executed, and execute the SQL statement; and a second acquisition module, used to acquire the fluctuation information of the key operators during the execution process, to obtain the execution plan information.

[0018] Furthermore, the execution unit also includes: a third acquisition module, used to periodically acquire the runtime monitoring data of the SQL statement during execution; and a second analysis module, used to analyze the runtime monitoring data according to a preset performance indicator list to obtain the performance data, wherein the performance data includes: query duration and number of data rows.

[0019] Furthermore, the analysis unit includes: a calculation module, used to calculate the execution plan information and the performance data according to a preset scoring rule to obtain a performance score for each SQL statement; and a filtering module, used to filter SQL statements whose performance scores are lower than the standard threshold, and write the SQL statements, corresponding performance data, and execution plan information into the performance evaluation report.

[0020] To achieve the above objectives, according to another aspect of this application, a computer-readable storage medium is also provided, the computer-readable storage medium including a stored computer program, wherein, when the computer program is executed, a performance evaluation method is provided to control the device where the computer-readable storage medium is located to execute the stored process described in any one of the above claims.

[0021] To achieve the above objectives, according to another aspect of this application, an electronic device is also provided, including one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the performance evaluation method of the stored process described in any one of the above claims.

[0022] To achieve the above objectives, according to another aspect of this application, a computer program product is also provided, including computer instructions, wherein when the computer instructions are executed by a processor, they implement the steps of the performance evaluation method for the stored procedure described in any one of the above claims.

[0023] This invention proposes a performance evaluation method for stored procedures. First, a preset parser generator is used to perform syntax parsing on the target stored procedure code to obtain a syntax parsing result. The syntax parsing result includes the SQL statements involved in the stored procedure, database table field information, and variable information. Then, according to a list of unfavorable operating conditions, unfavorable operating conditions are simulated based on the database table field information and variable information to obtain a set of simulated operating parameters. Next, the SQL statements are executed using the set of simulated operating parameters, and the execution plan information and performance data corresponding to each SQL statement are obtained during the execution process. Finally, the execution plan information and performance data are analyzed to obtain a performance evaluation report. The performance evaluation report identifies SQL statements whose performance scores are lower than the standard threshold.

[0024] This invention employs a method of pre-simulating adverse operating conditions, utilizing syntax parsing and dynamic parameter generation to proactively identify potential performance bottlenecks in stored procedures. This achieves the technical effect of efficient performance evaluation during the development phase. Specifically, by using a pre-defined parser generator to perform deep syntax parsing on the stored procedure code, key elements such as SQL statements, database table field information, and variable information are accurately extracted. Based on this information and a list of adverse operating conditions, a set of simulated operating parameters is generated, simulating the operating environment most likely to consume resources and time, ensuring the comprehensiveness and depth of the evaluation. Under the influence of the simulated parameters, SQL statements are executed, and execution plan information and performance data are captured. Finally, by comprehensively analyzing the execution plan information and performance data, a performance evaluation report is generated, accurately locating SQL statements with performance scores below the standard threshold. This provides a clear direction for optimizing code and improving the execution efficiency of stored procedures. It not only fills the gap in proactive and forward-looking performance evaluation of stored procedures but also significantly improves the accuracy and efficiency of the evaluation. Furthermore, it solves the technical problem in related technologies where the performance evaluation of existing stored procedures relies on actual production operating parameters, making it impossible to test and evaluate the performance impact in advance. Attached Figure Description

[0025] The accompanying drawings, which form part of this application, are used to provide a further understanding of this application. The illustrative embodiments and descriptions of this application are used to explain this application and do not constitute an undue limitation of this application. In the drawings:

[0026] Figure 1 A hardware block diagram of a computer terminal (or mobile device) for implementing a performance evaluation method for stored procedures is shown.

[0027] Figure 2 This is a flowchart of an optional performance evaluation method for a stored procedure according to an embodiment of the present invention;

[0028] Figure 3 This is a schematic diagram of an optional stored procedure performance evaluation apparatus according to an embodiment of the present invention;

[0029] Figure 4 This is a structural block diagram of an electronic device for a performance evaluation method of executing a stored procedure according to an embodiment of the present invention. Detailed Implementation

[0030] To enable those skilled in the art to better understand the present invention, the technical solutions of the present invention will be clearly and completely described below with reference to the accompanying drawings of the embodiments of the present invention. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of the present invention.

[0031] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of the invention described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0032] It should be noted that the performance evaluation method and apparatus for stored procedures in this application can be used in the field of big data technology for active performance testing and optimization of stored procedures, and can also be used in any field other than big data technology for active performance testing and optimization of stored procedures. This application does not limit the application field of the performance evaluation method and apparatus for stored procedures.

[0033] It should be noted that all relevant information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, and displayed data) involved in this application are information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, storage, processing, transmission, provision, disclosure, use, and handling of such data comply with the laws, regulations, and standards of the relevant regions, necessary confidentiality measures have been taken, and they do not violate public order and good morals. Corresponding access points are provided for users to choose to authorize or refuse access. For example, this system has interfaces with relevant users or organizations. Before obtaining relevant information, a request to obtain the information needs to be sent to the aforementioned user or organization through the interface, and the relevant information is obtained only after receiving consent from the aforementioned user or organization.

[0034] The information collection (e.g., user voice, video, and text collection) and analysis operations involved in this application have provided users with corresponding operation entry points during execution, allowing users to choose to agree to or reject the automated decision results; if the user chooses to reject, the process will proceed to the expert decision-making process.

[0035] The following embodiments of the present invention can be applied to various systems / applications / devices that require stored procedure performance evaluation and SQL statement optimization, enabling a stored procedure performance evaluation and optimization mechanism based on active simulation. The present invention parses the stored procedure code's syntax structure and extracts key information, then simulates adverse operating conditions to generate and execute parameterized SQL statements. This allows for better capture of the stored procedure's performance under extreme conditions, while also providing in-depth analysis and location of SQL statements with performance scores below the standard threshold.

[0036] This invention also accurately identifies performance bottlenecks through intelligent analysis of execution plans, dynamically adjusts parameters of SQL statements, and eliminates the need for intervention in the production environment during testing. It quickly and accurately locates and optimizes inefficient SQL, greatly improving the operating efficiency and stability of the database system. Especially in big data and distributed network environments, this proactive performance monitoring and optimization capability has immeasurable value for preventing and solving performance problems.

[0037] The present invention will now be described in detail with reference to various embodiments.

[0038] Example 1

[0039] According to an embodiment of the present invention, an embodiment of a performance evaluation method for a stored procedure is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0040] The performance evaluation method for stored procedures provided in Embodiment 1 of the present invention can be executed on a mobile terminal, a computer terminal, or a similar computing device. Figure 1 A hardware block diagram of a computer terminal (or mobile device) for implementing a performance evaluation method for stored procedures is shown. Figure 1 As shown, the computer terminal 10 (or mobile device) may include one or more processors 102 (shown as 102a, 102b, ..., 102n in the figure) 102 (processor 102 may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.), a memory 104 for storing data, and a transmission device 106 for communication functions. In addition, it may also include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of a BUS bus), a network interface, a power supply, and / or a camera. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the aforementioned electronic device. For example, computer terminal 10 may also include... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.

[0041] It should be noted that the aforementioned one or more processors 102 and / or other data processing circuits are generally referred to herein as "data processing circuits". These data processing circuits may be embodied, in whole or in part, in software, hardware, firmware, or any other combination thereof. Furthermore, the data processing circuits may be a single, independent processing module, or may be integrated, in whole or in part, into any other element within the computer terminal 10 (or mobile device). As involved in the embodiments of this application, the data processing circuits serve as a processor control mechanism (e.g., selection of a variable resistor termination path connected to an interface).

[0042] The memory 104 can be used to store software programs and modules of application software, such as the program instructions / data storage device corresponding to the performance evaluation method of the stored process in the embodiments of this application. The processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, thereby realizing the aforementioned performance evaluation method of the stored process. The memory 104 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the computer terminal 10 via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0043] The transmission device 106 is used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of the computer terminal 10. In one example, the transmission device 106 includes a Network Interface Controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the transmission device 106 may be a Radio Frequency (RF) module, used for wireless communication with the Internet.

[0044] The display may be, for example, a touchscreen liquid crystal display (LCD) that allows the user to interact with the user interface of the computer terminal 10 (or mobile device).

[0045] Under the above operating environment, the present invention provides, as follows: Figure 2 The performance evaluation method for stored procedures shown is implemented by a performance evaluation and optimization system. It combines syntax parsing technology and is used in big data processing and distributed computing scenarios. In particular, it is used to identify performance bottlenecks in stored procedures and optimize SQL statements. By simulating unfavorable operating conditions, specifically through syntax parsing, parameter simulation generation, execution plan capture, and performance data analysis, it aims to proactively identify and prevent performance problems in stored procedures and improve overall data processing efficiency.

[0046] The embodiments of the present invention will now be described in detail with reference to each specific step.

[0047] Figure 2 This is a flowchart of an optional stored procedure performance evaluation method according to an embodiment of the present invention, such as... Figure 2 As shown, the method includes the following steps:

[0048] Step S201: Use a preset parser generator to perform syntax parsing on the target stored procedure code to obtain the syntax parsing result, which includes the SQL statements, database table field information and variable information involved in the stored procedure.

[0049] It's important to note that the core objective of deep parsing of stored procedure code using a parser generator is to accurately distinguish and identify SQL statements, database table field information, and variable information within the code. This process can be achieved by defining a specific set of grammatical rules. These rules guide the parser generator to correctly segment and semantically understand the target code, thereby extracting the aforementioned key information. For example, the parser can identify the structure and syntax of a "SELECT" statement, as well as the database tables and fields referenced in the statement. It can also identify variables passed as parameters and record their usage in the code.

[0050] This embodiment overcomes the limitations of relying on actual production environment data for performance evaluation, and the difficulty in accurately identifying and locating performance bottlenecks when stored procedure code is complex and the call chain is deep, by using the above steps. Syntax parsing allows for the understanding and analysis of the code's internal structure and logic in advance without running the stored procedure, preparing for performance testing under simulated adverse operating conditions. This enables the discovery of potential performance issues early in development and avoids unpredictable performance problems in the production environment.

[0051] Optionally, in the performance evaluation method for stored procedures provided in this embodiment of the invention, the step of using a preset parser generator to perform syntax parsing on the target stored procedure code to obtain the syntax parsing result includes: obtaining the target syntax structure used by the target database system and initializing the parser generator according to the target syntax structure; preprocessing the target stored procedure code, wherein the preprocessing includes the following operations: formatting and comment cleanup; using the initialized parser generator to parse the preprocessed target stored procedure code to obtain the syntax parsing result containing SQL statements, database table field information, and variable information.

[0052] It's important to note that different database systems may support different SQL syntax variations. Therefore, before performing syntax parsing, it's necessary to obtain the target syntax structure used by the target database system. Determining the specific SQL syntax structure of the target database system ensures that the parsing tool can correctly interpret the SQL statements in the stored procedure. For example, relational database systems may follow standard SQL syntax, but they may also have vendor-specific extensions or variations. These need to be considered in the parser generator configuration to ensure that the target stored procedure code can be understood and correctly parsed.

[0053] Next, the target stored procedure code undergoes preprocessing, including formatting and comment cleanup. Formatting primarily standardizes the code's format, such as unifying indentation and line breaks, and eliminating unnecessary spaces and line breaks, making it easier for the parser to recognize the code's structure. Comment cleanup removes comments from the code, as comments do not affect code execution but may introduce interference for syntax parsing, leading to parsing errors or redundancy.

[0054] The preprocessed code can then be parsed using the initialized parser generator. Crucially, the parser generator must be correctly initialized beforehand based on the target syntax structure to accurately parse the SQL statements, database table field information, and variable information within the stored procedure. During parsing, a Syntax Tree (AST) can be generated to extract key information for syntax parsing, facilitating subsequent parameter generation and performance evaluation.

[0055] Step S202: According to the list of unfavorable operating conditions, simulate unfavorable operating conditions based on the database table field information and variable information to obtain a set of simulated operating parameters.

[0056] It's important to note that the adverse operating conditions list describes scenarios that, in real-world applications, are likely to lead to increased stored procedure response times and resource consumption. Examples include large-scale data queries, complex join operations, high-concurrency transaction processing, and insufficient index usage. By simulating these scenarios, the performance of stored procedures under extreme conditions can be tested.

[0057] First, the implementation system can extract all relevant database tables, fields, and variable information from the stored procedure's code. Variable information is particularly important because different variable values ​​can affect the stored procedure's execution plan and performance. For example, if a stored procedure contains conditional queries based on variables, different values ​​for those variables may lead to different execution paths and performance results.

[0058] Based on the extracted information, the implementation system further determines the simulation parameters to ensure the stored procedure operates under unfavorable conditions. For SQL statements with constant fields, extreme values ​​can be set based on the field type; for example, the maximum or minimum value might be selected for numeric fields, and the longest text string could be chosen for text fields. For SQL statements with variable fields, a GROUP BY statement covering all possible values ​​can be generated to simulate the largest dataset scenario. Furthermore, for function calls embedded within the stored procedure, if the parameters are variables, the implementation system can set specific parameter values ​​to ensure the function can be executed correctly and used for subsequent performance analysis.

[0059] Next, the generated simulation parameters are used to execute SQL statements, obtaining execution plan information and performance data under adverse conditions. This step helps identify potential performance issues in stored procedures because actual execution allows observation of how the database selects execution paths and the response time and resource consumption of stored procedures under different parameter conditions.

[0060] Optionally, in the performance evaluation method for stored procedures provided in this embodiment of the invention, the list of unfavorable operating conditions is constructed through the following steps: collecting performance bottleneck data of the target stored procedure in historical running cycles from the system database, wherein the performance bottleneck data includes at least: running data identified as high data volume and / or complex query patterns; analyzing each piece of performance bottleneck data based on database theory to obtain the corresponding performance impact type, wherein the performance impact type includes: missing index and uneven data distribution; defining the boundary range of unfavorable operating conditions by combining the performance bottleneck data and the corresponding performance impact type; and integrating the boundary ranges of unfavorable operating conditions corresponding to all performance bottleneck data to establish a list of unfavorable operating conditions.

[0061] In embodiments of the present invention, the step of constructing a list of unfavorable operating conditions aims to define the conditional boundaries that may negatively impact the performance of the stored procedure through historical data and theoretical analysis, thereby providing foundational data for subsequent performance evaluation and optimization. The following are the detailed steps for constructing the list of unfavorable operating conditions:

[0062] First, collect performance data of stored procedures from the system database during their historical execution cycles, especially those records identified as high-volume operations or those with complex query patterns. This data typically includes specific SQL statements, execution time, number of rows returned, and memory and CPU resources used. For example, a query might experience a longer response time within a certain period due to a surge in data volume; the execution data during this period will be considered part of the performance bottleneck data.

[0063] Next, we will conduct a theoretical analysis of the types of performance impacts. Using database theory, we will conduct an in-depth analysis of the collected performance bottleneck data to identify the root causes of performance degradation. Common types of performance impacts include: missing indexes, uneven data distribution, full table scans instead of index scans, lock contention under high concurrency, unnecessary data sorting and join operations, etc. For example, by analyzing the execution plan, we can identify that some SQL statements perform full table scans without suitable index support. This is a typical time-consuming operation and can be classified as a performance bottleneck caused by missing indexes.

[0064] Next, define the boundary range of adverse operating conditions. Based on the specific circumstances of historical performance bottleneck data and the identified types of performance impact, define the boundary range of adverse operating conditions. It should be noted that this boundary range is not limited to high data volumes and complex queries, but also includes any scenario description that may lead to performance degradation, such as the number of concurrent users, changes in table structure, and data distribution patterns. For example, if historical data shows that executing a query on a table with extremely uneven data distribution leads to a significant performance drop, then the condition "extremely uneven data distribution" should be added to the list of adverse operating conditions, along with a possible boundary range, such as a data distribution skew exceeding 90%.

[0065] Finally, an unfavorable operating condition list is established, which integrates all analyzed and defined unfavorable operating conditions and their boundary ranges into a list, namely the unfavorable operating condition list, as a guiding document for generating simulation operating parameters and performing performance evaluation tests.

[0066] Optionally, in the performance evaluation method for stored procedures provided in this embodiment of the invention, the step of simulating unfavorable operating conditions according to a list of unfavorable operating conditions and database table field information and variable information to obtain a set of simulated operating parameters includes: for each unfavorable operating condition, mapping the boundary range of the unfavorable operating condition to the database table field corresponding to the target database according to the database table field information to obtain a first simulated operating parameter; mapping the boundary range of the unfavorable operating condition to the database variable corresponding to the target database according to the variable information to obtain a second simulated operating parameter; and integrating the first simulated operating parameter and the second simulated operating parameter to obtain a set of simulated operating parameters.

[0067] It should be noted that for each adverse operating condition, the boundary range of these conditions can be mapped to the relevant fields and variables in the actual database, ensuring that the SQL statement can run under conditions that are closest to the actual adverse situation in the simulation test, thereby accurately identifying the performance bottleneck in the stored procedure.

[0068] In this embodiment, the first simulated running parameter is the result of mapping adverse running conditions to database table fields. For example, if one of the adverse running conditions is "large data volume query", then the implementation system selects a larger data volume as the query condition based on the statistical information of the fields in the table, simulating the impact of high data volume on the execution performance of SQL statements.

[0069] The second simulation run parameter is the result of mapping adverse run conditions to variables in the stored procedure. For example, if the adverse run condition involves a "complex query pattern," then the implementation system provides values ​​for variables that can trigger complex query patterns, such as combinations of multiple conditions or subqueries, which helps identify performance degradation caused by the variable values.

[0070] Combining the first and second simulation parameters creates a comprehensive set of simulation parameters, ensuring that all adverse operating conditions are fully covered when executing SQL statements, thus enabling the testing of stored procedure performance in demanding environments.

[0071] Alternatively, in practical applications, the set of simulated execution parameters can be dynamically adjusted based on the execution status of the stored procedure. For example, if initial testing reveals performance issues with a particular SQL statement when dealing with a specific data distribution, the implementation system can adaptively generate more simulated parameters for that data distribution for in-depth analysis and optimization.

[0072] Step S203: Execute SQL statements using the simulated running parameter set, and obtain the execution plan information and performance data corresponding to each SQL statement during the execution process.

[0073] The steps described above simulate the execution of SQL statements within a stored procedure under adverse conditions in a secure and controlled environment using a predefined set of parameters to assess potential performance issues. After extracting the SQL statement and variable information during the syntax parsing phase, a series of simulated execution parameters can be generated based on this information, covering as many conditions as possible that may affect SQL execution efficiency. For example, if the SQL statement involves a table with an extremely large amount of data, the simulation parameters could include using the maximum amount of data in the table as the query condition, or using the most complex data distribution as the input parameter, to test the performance of the SQL statement under high load or data anomaly conditions.

[0074] During the execution of SQL statements, the implementation system captures detailed execution plan information and performance data. Execution plan information includes, but is not limited to: the access method selected by the query optimizer (e.g., sequential scan, index scan), data filtering conditions, join strategies, and execution order. Performance data covers key metrics such as execution time, number of returned rows, number of filtered rows, number of executions, and cache usage, all of which are important bases for evaluating SQL statement performance and resource consumption.

[0075] Through the above steps, the implementation system can proactively identify inefficient SQL statements in stored procedures without waiting for these statements to actually cause performance problems in the production environment. This is something that existing passive performance evaluation methods, which usually rely on actual test data and log monitoring, do not have. It can provide a more proactive and forward-looking means for database performance management and optimization.

[0076] Optionally, in the performance evaluation method for stored procedures provided in this embodiment of the invention, the step of executing SQL statements using a set of simulated running parameters and obtaining execution plan information corresponding to each SQL statement during execution includes: assigning values ​​to variables in the SQL statement based on the values ​​in the set of simulated running parameters to obtain the SQL statement to be executed; requesting the execution plan corresponding to the SQL statement from the target database system and extracting key operators from the execution plan, wherein the key operators include: index scan operators and table scan operators; injecting the key operators into the SQL statement to be executed and executing the SQL statement; and obtaining the fluctuation information of the key operators during execution to obtain execution plan information.

[0077] The core of the above steps lies in assigning values ​​to variables in the SQL statement using given simulation parameters, thereby obtaining the SQL statement to be executed, and requesting and parsing the execution plan in the target database system to evaluate the performance of the SQL statement under adverse conditions.

[0078] The step of assigning values ​​to variables to generate the SQL statement to be executed refers to assigning values ​​to the variables involved in the SQL statement within the stored procedure based on the values ​​in the simulated execution parameter set. This process ensures that each SQL statement can be executed under conditions closest to the unfavorable operating conditions, rather than just based on average or typical conditions. For example, if the unfavorable operating conditions involve queries with a large amount of data, the implementation system will set the values ​​of the relevant variables to the maximum amount of data allowed in the range when generating the SQL statement to be executed.

[0079] After assigning values ​​to variables, the system requests the execution plan of the SQL statement from the database. This plan includes the query execution path determined by the database optimizer. By analyzing the execution plan, key operators can be extracted, especially those that have a significant impact on performance, such as index scan operators and table scan operators. Index scans typically indicate that the database uses indexes to optimize queries under specific conditions, while table scans represent inefficient full table scans.

[0080] After obtaining the execution plan, the system executes the SQL statement while monitoring and recording the execution status of key operators. Particular attention is paid to performance fluctuations of these operators during execution, including but not limited to changes in parameters such as execution time, amount of data read, and cache size used, to reveal the true performance of the SQL statement under adverse conditions.

[0081] Optionally, in the performance evaluation method for stored procedures provided in this embodiment of the invention, the step of executing SQL statements using a set of simulated running parameters and obtaining performance data corresponding to each SQL statement during execution includes: periodically obtaining running monitoring data of the SQL statements during execution; analyzing the running monitoring data according to a preset performance indicator list to obtain performance data, wherein the performance data includes: query duration and number of data rows.

[0082] In this embodiment of the invention, while executing SQL statements using a set of simulated running parameters, the execution process can also be monitored to collect performance data for each SQL statement. Specifically, performance monitoring data is captured at preset time intervals during the execution of the SQL statement, including but not limited to the timestamps of the query start and end, the resources required to execute the SQL statement (CPU, memory usage), the number of returned data rows, and error messages (if any). This timed acquisition mechanism ensures the continuity and integrity of the data, which helps to analyze the performance of the SQL statement at different stages.

[0083] Based on the collected operational monitoring data, the data is analyzed according to a pre-defined list of performance metrics to extract key performance data for specific SQL statements. For example, "query duration" refers to the time from the start of SQL statement execution to its completion, while "number of rows" is the number of rows contained in the result set returned by the SQL statement. All of this data is ultimately used to evaluate the efficiency of the SQL statements.

[0084] Step S204: Analyze the execution plan information and performance data to obtain a performance evaluation report, in which SQL statements with performance scores below the standard threshold are identified.

[0085] It's important to note that the execution plan, generated by the database query optimizer, describes the detailed steps of how to execute an SQL statement. The implementation system can analyze this information to extract key execution operators, such as full table scans, index scans, and join operations, as well as related resource consumption information, such as CPU time, I / O operations, and the amount of buffers used, to understand the SQL statement's execution mechanism and resource requirements.

[0086] In addition to the execution plan, the implementation system can also evaluate the actual execution of SQL statements and obtain performance data, including the execution time of the SQL statement, the number of rows returned, the number of rows filtered out, etc., which directly reflects the efficiency of the SQL statement and the resource usage.

[0087] Furthermore, the implementation system can score the execution plan and performance data of each SQL statement according to preset scoring rules. These scoring rules may include a weighted average of execution time and resource consumption, a measure of deviation from the optimal execution plan, and specific performance metrics such as index utilization and concurrency performance. If the performance score of a SQL statement is lower than a set standard threshold, it can be marked as a candidate for a performance problem.

[0088] Finally, all SQL statements with performance scores below the threshold are compiled, and combined with specific execution plan information and performance data, a detailed performance evaluation report is generated. The report details the performance score, execution plan, and suggested optimization directions for each problematic statement, providing developers with a clear guide to performance improvement.

[0089] Optionally, in the performance evaluation method for stored procedures provided in this embodiment of the invention, the step of analyzing execution plan information and performance data to obtain a performance evaluation report includes: calculating the execution plan information and performance data according to preset scoring rules to obtain a performance score for each SQL statement; filtering SQL statements with performance scores lower than a standard threshold, and writing the SQL statements, corresponding performance data, and execution plan information into the performance evaluation report.

[0090] In one optional embodiment, the implementation system uses pre-defined scoring rules to quantitatively analyze the execution plan information and performance data of each SQL statement, thereby deriving a comprehensive performance score. The pre-defined scoring rules can cover multiple dimensions, such as execution time, resource consumption, and the effectiveness of data access patterns, to comprehensively reflect the performance characteristics of the SQL statements. The implementation system can filter out SQL statements with scores below a standard threshold based on the performance score; these low-scoring SQL statements represent the performance bottlenecks. This standard threshold can be set according to the specific needs and performance benchmarks of the database application.

[0091] Furthermore, the low-scoring SQL statements, along with their associated performance data and execution plan information, can be recorded in the performance evaluation report, clearly presenting the original code, execution environment, performance score, specific performance issues, and potential optimization suggestions of the SQL statements.

[0092] Through steps S201 to S204 above, the target stored procedure code can first be parsed using a preset parser generator to obtain a parsing result. The parsing result includes the SQL statements, database table field information, and variable information involved in the stored procedure. Then, according to the list of unfavorable operating conditions, unfavorable operating conditions are simulated based on the database table field information and variable information to obtain a set of simulated operating parameters. The SQL statements are then executed using the set of simulated operating parameters, and the execution plan information and performance data corresponding to each SQL statement are obtained during the execution process. Finally, the execution plan information and performance data are analyzed to obtain a performance evaluation report. The performance evaluation report identifies SQL statements whose performance scores are lower than the standard threshold.

[0093] In this embodiment of the invention, by pre-simulating adverse operating conditions and using syntax parsing and dynamic parameter generation, the potential performance bottlenecks of stored procedures are proactively identified. This achieves the technical effect of efficient performance evaluation during the development phase. Specifically, by using a pre-set parser generator to perform deep syntax parsing on the stored procedure code, key elements such as SQL statements, database table field information, and variable information can be accurately extracted. Based on this information and a list of adverse operating conditions, a set of simulated operating parameters is generated to simulate the operating environment most likely to consume resources and time, ensuring the comprehensiveness and depth of the evaluation. Under the action of the simulated parameters, the SQL statements are executed, and execution plan information and performance data are captured. Finally, by comprehensively analyzing the execution plan information and performance data, a performance evaluation report is generated, accurately locating SQL statements with performance scores below the standard threshold. This provides a clear direction for optimizing code and improving the execution efficiency of stored procedures. It not only fills the gap in the proactive and forward-looking nature of stored procedure performance evaluation but also significantly improves the accuracy and efficiency of the evaluation. This solves the technical problem in related technologies where the performance evaluation of existing stored procedures relies on actual production operating parameters and cannot test and evaluate the performance impact in advance.

[0094] The invention will now be described in conjunction with another alternative embodiment.

[0095] Example 2

[0096] This invention also provides a storage procedure performance evaluation device. It should be noted that the storage procedure performance evaluation device of this invention includes multiple implementation units, which can be used to execute the storage procedure performance evaluation method provided in the first embodiment above. Each implementation unit corresponds to each implementation step in the first embodiment above.

[0097] Figure 3 This is a schematic diagram of an optional stored procedure performance evaluation apparatus according to an embodiment of the present invention, such as... Figure 3As shown, the device may include: a parsing unit 31, a simulation unit 32, an execution unit 33, and an analysis unit 34.

[0098] The parsing unit 31 is used to perform syntax parsing on the target stored procedure code using a preset parser generator to obtain a syntax parsing result, which includes the SQL statements, database table field information, and variable information involved in the stored procedure.

[0099] Simulation unit 32 is used to simulate unfavorable operating conditions according to the list of unfavorable operating conditions, based on the database table field information and variable information, to obtain a set of simulated operating parameters.

[0100] Execution unit 33 is used to execute SQL statements using a set of simulated running parameters, and to obtain execution plan information and performance data for each SQL statement during the execution process.

[0101] Analysis unit 34 is used to analyze execution plan information and performance data to obtain a performance evaluation report, in which SQL statements with performance scores below the standard threshold are identified.

[0102] The aforementioned performance evaluation device for stored procedures can first use the parsing unit 31 to perform syntax parsing on the target stored procedure code using a preset parser generator to obtain a syntax parsing result. The syntax parsing result includes the SQL statements, database table field information, and variable information involved in the stored procedure. Then, the simulation unit 32 simulates unfavorable operating conditions according to the database table field information and variable information, based on a list of unfavorable operating conditions, to obtain a set of simulated operating parameters. Next, the execution unit 33 executes the SQL statements using the set of simulated operating parameters, and obtains the execution plan information and performance data corresponding to each SQL statement during the execution process. Finally, the analysis unit 34 analyzes the execution plan information and performance data to obtain a performance evaluation report, in which the performance evaluation report identifies SQL statements whose performance scores are lower than the standard threshold.

[0103] In this embodiment of the invention, by pre-simulating adverse operating conditions and using syntax parsing and dynamic parameter generation, the potential performance bottlenecks of stored procedures are proactively identified. This achieves the technical effect of efficient performance evaluation during the development phase. Specifically, by using a pre-set parser generator to perform deep syntax parsing on the stored procedure code, key elements such as SQL statements, database table field information, and variable information can be accurately extracted. Based on this information and a list of adverse operating conditions, a set of simulated operating parameters is generated to simulate the operating environment most likely to consume resources and time, ensuring the comprehensiveness and depth of the evaluation. Under the action of the simulated parameters, the SQL statements are executed, and execution plan information and performance data are captured. Finally, by comprehensively analyzing the execution plan information and performance data, a performance evaluation report is generated, accurately locating SQL statements whose performance scores are below the standard threshold. This provides a clear direction for optimizing code and improving the execution efficiency of stored procedures. It not only fills the gap in the proactive and forward-looking nature of stored procedure performance evaluation but also significantly improves the accuracy and efficiency of the evaluation. This solves the technical problem in related technologies where the performance evaluation of existing stored procedures relies on actual production operating parameters and cannot test and evaluate the performance impact in advance.

[0104] Furthermore, the parsing unit includes: a first acquisition module, used to acquire the target syntax structure used by the target database system and initialize the parser generator according to the target syntax structure; a preprocessing module, used to preprocess the target stored procedure code, wherein the preprocessing includes the following operations: formatting and comment cleanup; and a parsing module, used to parse the preprocessed target stored procedure code using the initialized parser generator to obtain a syntax parsing result containing SQL statements, database table field information, and variable information.

[0105] Furthermore, the performance evaluation device for stored procedures also includes: a collection module, used to collect performance bottleneck data of the target stored procedure in historical execution cycles from the system database, wherein the performance bottleneck data includes at least: execution data identified as high data volume and / or complex query patterns; a first analysis module, used to analyze each piece of performance bottleneck data based on database theory to obtain the corresponding performance impact type, wherein the performance impact type includes: missing indexes and uneven data distribution; a definition module, used to define the boundary range of unfavorable operating conditions by combining the performance bottleneck data and the corresponding performance impact type; and an establishment module, used to integrate the boundary ranges of unfavorable operating conditions corresponding to all performance bottleneck data and establish a list of unfavorable operating conditions.

[0106] Furthermore, the simulation unit includes: a first mapping module, used to map the boundary range of each unfavorable operating condition to the corresponding database table field of the target database based on the database table field information, to obtain the first simulation operating parameters; a second mapping module, used to map the boundary range of the unfavorable operating condition to the corresponding database variable of the target database based on the variable information, to obtain the second simulation operating parameters; and an integration module, used to integrate the first simulation operating parameters and the second simulation operating parameters to obtain a set of simulation operating parameters.

[0107] Furthermore, the execution unit includes: an assignment module, used to assign values ​​to variables in the SQL statement based on the values ​​in the simulated running parameter set, to obtain the SQL statement to be executed; an extraction module, used to request the execution plan corresponding to the SQL statement from the target database system, and extract the key operators in the execution plan, wherein the key operators include: index scan operator and table scan operator; an injection module, used to inject the key operators into the SQL statement to be executed, and execute the SQL statement; and a second acquisition module, used to acquire the fluctuation information of the key operators during the execution process, and obtain the execution plan information.

[0108] Furthermore, the execution unit also includes: a third acquisition module, used to periodically acquire the runtime monitoring data of the SQL statement during the execution process; and a second analysis module, used to analyze the runtime monitoring data according to a preset performance indicator list to obtain performance data, wherein the performance data includes: query duration and number of data rows.

[0109] Furthermore, the analysis unit includes: a calculation module, used to calculate the execution plan information and performance data according to preset scoring rules to obtain the performance score of each SQL statement; and a filtering module, used to filter SQL statements with performance scores lower than the standard threshold and write the SQL statements, corresponding performance data, and execution plan information into the performance evaluation report.

[0110] It should be noted that the parsing unit 31, simulation unit 32, execution unit 33, and analysis unit 34 mentioned above correspond to steps S201 to S204 in Embodiment 1. The instances and application scenarios implemented by the above units and corresponding steps are the same, but are not limited to the content disclosed in Embodiment 1. It should be noted that the above modules or units can be hardware or software components stored in memory (e.g., memory 104) and processed by one or more processors (e.g., processors 102a, 102b, ..., 102n). The above modules or units can also run as part of a device in the computer terminal 10 provided in Embodiment 1.

[0111] The invention will now be described in conjunction with another alternative embodiment.

[0112] Example 3

[0113] The present invention can also provide an electronic device. Figure 4 This is a structural block diagram of an electronic device for a performance evaluation method of executing a stored procedure according to an embodiment of the present invention, such as... Figure 4 As shown, the electronic device may include: one or more ( Figure 4 (Only one is shown) Processor 402, memory 404, memory controller, and peripheral interface, wherein the peripheral interface is connected to the radio frequency module, audio module and display.

[0114] The memory can be used to store software programs and modules, such as the program instructions / modules corresponding to the performance evaluation method and apparatus for the stored process in this application embodiment. The processor executes various functional applications and data processing by running the software programs and modules stored in the memory, thereby realizing the aforementioned performance evaluation method for the stored process. The memory may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory may further include memory remotely located relative to the processor, and these remote memories can be connected to the terminal via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0115] The processor can invoke information and applications stored in memory via a transmission device to perform the following steps: parsing the target stored procedure code using a preset parser generator to obtain a parsing result, which includes SQL statements, database table field information, and variable information involved in the stored procedure; simulating unfavorable operating conditions based on a list of unfavorable operating conditions and the database table field and variable information to obtain a set of simulated operating parameters; executing SQL statements using the simulated operating parameter set, and obtaining execution plan information and performance data for each SQL statement during execution; analyzing the execution plan information and performance data to obtain a performance evaluation report, which identifies SQL statements with performance scores below a standard threshold.

[0116] The processor can also invoke information and applications stored in memory via a transmission device to perform the following steps: obtain the target syntax structure used by the target database system and initialize the parser generator according to the target syntax structure; preprocess the target stored procedure code, wherein the preprocessing includes the following operations: formatting and comment cleanup; use the initialized parser generator to parse the preprocessed target stored procedure code to obtain a syntax parsing result containing SQL statements, database table field information, and variable information.

[0117] The processor can also invoke information and applications stored in memory via a transmission device to perform the following steps: collect performance bottleneck data of the target stored procedure in historical execution cycles from the system database, wherein the performance bottleneck data includes at least: execution data identified as high data volume and / or complex query patterns; analyze each piece of performance bottleneck data based on database theory to obtain the corresponding performance impact type, wherein the performance impact type includes: missing index and uneven data distribution; define the boundary range of unfavorable operating conditions by combining the performance bottleneck data and the corresponding performance impact type; integrate the boundary ranges of unfavorable operating conditions corresponding to all performance bottleneck data to establish a list of unfavorable operating conditions.

[0118] The processor can also call the information and application program stored in the memory through the transmission device to perform the following steps: for each unfavorable operating condition, map the boundary range of the unfavorable operating condition to the corresponding database table field of the target database according to the database table field information to obtain the first simulation operating parameter; map the boundary range of the unfavorable operating condition to the corresponding database variable of the target database according to the variable information to obtain the second simulation operating parameter; integrate the first simulation operating parameter and the second simulation operating parameter to obtain the simulation operating parameter set.

[0119] The processor can also access information and applications stored in memory via a transmission device to perform the following steps: assigning values ​​to variables in an SQL statement based on values ​​in the set of simulated running parameters to obtain the SQL statement to be executed; requesting the execution plan corresponding to the SQL statement from the target database system and extracting key operators from the execution plan, including index scan operators and table scan operators; injecting the key operators into the SQL statement to be executed and executing the SQL statement; and obtaining the execution plan information by acquiring fluctuation information of the key operators during execution.

[0120] The processor can also access information and applications stored in the memory via a transmission device to perform the following steps: during execution, periodically acquire SQL statement execution monitoring data; analyze the execution monitoring data according to a preset performance indicator list to obtain performance data, which includes: query duration and number of data rows.

[0121] The processor can also access information and applications stored in the memory via a transmission device to perform the following steps: calculate the performance score of each SQL statement by analyzing the execution plan information and performance data according to preset scoring rules; filter SQL statements with performance scores below the standard threshold and write the SQL statements, corresponding performance data, and execution plan information into a performance evaluation report.

[0122] This invention provides a performance evaluation scheme for stored procedures. By pre-simulating adverse operating conditions and using syntax parsing and dynamic parameter generation, it proactively identifies potential performance bottlenecks in stored procedures, achieving efficient performance evaluation during the development phase. Specifically, by using a pre-defined parser generator to perform deep syntax parsing on the stored procedure code, key elements such as SQL statements, database table field information, and variable information are accurately extracted. Based on this information and a list of adverse operating conditions, a set of simulated operating parameters is generated to simulate the operating environment most likely to consume resources and time, ensuring the comprehensiveness and depth of the evaluation. Under the influence of the simulated parameters, SQL statements are executed, and execution plan information and performance data are captured. Finally, by comprehensively analyzing the execution plan information and performance data, a performance evaluation report is generated, accurately locating SQL statements with performance scores below the standard threshold. This provides a clear direction for optimizing code and improving the execution efficiency of stored procedures. It not only fills the gap in proactive and forward-looking performance evaluation of stored procedures but also significantly improves the accuracy and efficiency of the evaluation. This solves the technical problem in related technologies where the performance evaluation of stored procedures relies on actual production operating parameters, making it impossible to test and evaluate the performance impact in advance.

[0123] Those skilled in the art will understand that Figure 4 The structure shown is for illustrative purposes only. Electronic devices can also be smartphones, tablets, handheld computers, mobile internet devices (MIDs), PADs, and other terminal devices. Figure 4 This does not limit the structure of the aforementioned electronic device. For example, electronic devices may also include components that are more... Figure 4 The more or fewer components shown (such as network interfaces, display devices, etc.), or having the same Figure 4 The different configurations shown.

[0124] Those skilled in the art will understand that all or part of the steps in the various methods of the above embodiments can be implemented by a program instructing the hardware related to the terminal device. The program can be stored in a computer-readable storage medium, which may include: flash drive, read-only memory (ROM), random access memory (RAM), disk or optical disk, etc.

[0125] The invention will now be described in conjunction with another alternative embodiment.

[0126] Example 4

[0127] This invention also provides a computer-readable storage medium. Optionally, in this invention, the computer-readable storage medium can be used to store the program code executed by the performance evaluation method for the stored procedure provided in Embodiment 1.

[0128] Optionally, in this embodiment of the invention, the storage medium may be located in any computer terminal in a group of computer terminals in a computer network, or in any mobile terminal in a group of mobile terminals.

[0129] This invention also provides a computer program product, which, when executed on a data processing device, is suitable for performing the performance evaluation method steps of a stored procedure: parsing the target stored procedure code using a preset parser generator to obtain a parsing result, wherein the parsing result includes SQL statements, database table field information, and variable information involved in the stored procedure; simulating unfavorable operating conditions according to a list of unfavorable operating conditions, based on the database table field information and variable information, to obtain a set of simulated operating parameters; executing SQL statements using the set of simulated operating parameters, and obtaining execution plan information and performance data corresponding to each SQL statement during execution; analyzing the execution plan information and performance data to obtain a performance evaluation report, wherein the performance evaluation report identifies SQL statements whose performance scores are lower than a standard threshold.

[0130] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0131] In the above embodiments of this application, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0132] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0133] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0134] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0135] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, read-only memory (ROM), random access memory (RAM), portable hard drive, magnetic disk, or optical disk.

[0136] The above description is only a preferred embodiment of this application. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of this application, and these improvements and modifications should also be considered within the scope of protection of this application.

Claims

1. A method for evaluating the performance of a stored procedure, characterized in that, include: The target stored procedure code is parsed using a preset parser generator to obtain a parsing result, which includes the SQL statements, database table field information, and variable information involved in the stored procedure. Based on the list of unfavorable operating conditions, and according to the database table field information and the variable information, unfavorable operating conditions are simulated to obtain a set of simulated operating parameters; The SQL statements are executed using the set of simulated running parameters, and the execution plan information and performance data corresponding to each SQL statement are obtained during the execution process. Analyze the execution plan information and the performance data to obtain a performance evaluation report, in which the SQL statements with performance scores below the standard threshold are identified.

2. The performance evaluation method according to claim 1, characterized in that, The steps for parsing the target stored procedure code using a preset parser generator to obtain the parsing results include: Obtain the target syntax structure used by the target database system, and initialize the parser generator according to the target syntax structure; The target stored procedure code is preprocessed, wherein the preprocessing includes the following operations: formatting and comment cleanup; The parser generator, after initialization and configuration, is used to parse the preprocessed target stored procedure code to obtain the syntax parsing result containing the SQL statement, the database table field information, and the variable information.

3. The performance evaluation method according to claim 1, characterized in that, The list of unfavorable operating conditions is constructed through the following steps: Collect performance bottleneck data of the target stored procedure in the historical execution cycle from the system database. The performance bottleneck data includes at least the execution data identified as having high data volume and / or complex query patterns. Based on database theory, each piece of performance bottleneck data is analyzed to obtain the corresponding performance impact type, which includes: missing index and uneven data distribution; Define the boundary range of unfavorable operating conditions by combining the performance bottleneck data and the corresponding performance impact types; By integrating the boundary ranges of all the aforementioned performance bottleneck data corresponding to the unfavorable operating conditions, a list of unfavorable operating conditions is established.

4. The performance evaluation method according to claim 1, characterized in that, The steps of simulating unfavorable operating conditions and obtaining a set of simulated operating parameters based on the database table field information and the variable information, according to the list of unfavorable operating conditions, include: For each unfavorable operating condition, the boundary range of the unfavorable operating condition is mapped to the corresponding database table field of the target database according to the database table field information to obtain the first simulated operating parameters; Based on the variable information, the boundary range of the unfavorable operating conditions is mapped to the database variables corresponding to the target database to obtain the second simulation operating parameters; The first simulation operation parameters and the second simulation operation parameters are integrated to obtain the simulation operation parameter set.

5. The performance evaluation method according to claim 1, characterized in that, The steps of executing the SQL statements using the simulated execution parameter set and obtaining the execution plan information corresponding to each SQL statement during execution include: Based on the values ​​in the set of simulated running parameters, the variables in the SQL statement are assigned values ​​to obtain the SQL statement to be executed; Request the execution plan corresponding to the SQL statement from the target database system, and extract the key operators in the execution plan, wherein the key operators include: index scan operator and table scan operator; The key operator is injected into the SQL statement to be executed, and the SQL statement is then executed. During execution, fluctuation information of the key operators is obtained to obtain the execution plan information.

6. The performance evaluation method according to claim 5, characterized in that, The steps of executing the SQL statements using the simulated execution parameter set and obtaining performance data corresponding to each SQL statement during execution include: During execution, the runtime monitoring data of the SQL statement is periodically obtained; The performance data is obtained by analyzing the operation monitoring data according to the preset performance index list, wherein the performance data includes: query time and number of data rows.

7. The performance evaluation method according to claim 1, characterized in that, The steps for analyzing the execution plan information and the performance data to obtain a performance evaluation report include: The performance score of each SQL statement is obtained by calculating the execution plan information and the performance data according to the preset scoring rules. Filter the SQL statements whose performance scores are lower than the standard threshold, and write the SQL statements, corresponding performance data, and execution plan information into the performance evaluation report.

8. A performance evaluation device for a stored procedure, characterized in that, include: The parsing unit is used to perform syntax parsing on the target stored procedure code using a preset parser generator to obtain a syntax parsing result, wherein the syntax parsing result includes the SQL statements, database table field information and variable information involved in the stored procedure; The simulation unit is used to simulate unfavorable operating conditions according to the list of unfavorable operating conditions, based on the database table field information and the variable information, to obtain a set of simulated operating parameters. An execution unit is used to execute the SQL statement using the set of simulated running parameters, and to obtain the execution plan information and performance data corresponding to each SQL statement during the execution process. An analysis unit is used to analyze the execution plan information and the performance data to obtain a performance evaluation report, wherein the performance evaluation report identifies the SQL statements whose performance scores are lower than the standard threshold.

9. A computer-readable storage medium, characterized in that, The computer-readable storage medium includes a stored computer program, wherein, when the computer program is executed, it controls the device containing the computer-readable storage medium to perform a performance evaluation method for the stored procedure as described in any one of claims 1 to 7.

10. An electronic device, characterized in that, It includes one or more processors and a memory, the memory being used to store one or more programs, wherein when the one or more programs are executed by the one or more processors, the one or more processors cause the one or more processors to implement the performance evaluation method of the stored procedure according to any one of claims 1 to 7.

11. A computer program product, characterized in that, Includes computer instructions, wherein when executed by a processor, the computer instructions implement the steps of the performance evaluation method for the stored procedure according to any one of claims 1 to 7.

Images