Performance assessment method and device and electronic equipment

Abstract

The invention discloses a performance assessment method and device and electronic equipment. The method comprises the following steps: acquiring a database performance analysis report; determining a target SQL script from the database performance analysis report; a rectification report of the target SQL script is generated, and the rectification report comprises a target object corresponding to the target SQL script; and performing performance assessment on the target object by using the rectification report. The technical problems that performance assessment of research and development personnel generally depends on manual analysis of research and development scripts, the process is tedious and the accuracy is poor in the prior art are solved.

Description

Technical Field

[0001] The present application relates to the field of data processing, and in particular to a performance appraisal method, device and electronic equipment. Background Art

[0002] A large number of SQL scripts are executed during the operation of the database system. The efficiency of SQL scripts will directly affect the performance of the database system. At present, the evaluation of SQL script performance mainly depends on the experience of developers. The performance of SQL script developers is mainly evaluated by manual evaluation of SQL scripts. The process is cumbersome and the accuracy of the evaluation results is poor.

[0003] To address the above-mentioned problems, no effective solution has been proposed yet. Summary of the invention

[0004] The embodiments of the present application provide a performance appraisal method, device and electronic device to at least solve the technical problem that the performance appraisal of R&D personnel in related technologies usually relies on manual analysis of R&D scripts, which is cumbersome and has poor accuracy.

[0005] According to one aspect of an embodiment of the present application, a performance appraisal method is provided, including: obtaining a database performance analysis report; determining a target SQL script from the database performance analysis report; generating a rectification report for the target SQL script, wherein the rectification report includes a target object corresponding to the target SQL script; and performing performance appraisal on the target object using the rectification report.

[0006] In some embodiments of the present application, determining a target SQL script from a database performance analysis report includes: obtaining a first execution time of at least one SQL script from the database performance analysis report; comparing the first execution time with a preset threshold; and determining a SQL script whose first execution time is greater than the preset threshold in at least one SQL script as a target SQL script.

[0007] In some embodiments of the present application, the execution time of at least one SQL script is obtained from a database performance analysis report, including: obtaining a database type corresponding to the database performance analysis report; determining a performance indicator mapping rule corresponding to the database type, wherein the performance indicator mapping rule is used to indicate a mapping relationship between a data field in the database performance analysis report and the performance report; and mapping the database performance analysis report using the performance indicator mapping rule to obtain the execution time of the SQL script.

[0008] In some embodiments of the present application, a rectification report for a target SQL script is generated, including: obtaining execution information of the target SQL script and a target object corresponding to the target SQL script, wherein the execution information includes statement text of the target SQL script; analyzing the execution information to obtain analysis results; generating optimization suggestions for the target SQL script corresponding to the analysis results; and constructing a rectification report for the target SQL script using the execution information, the target object, and the optimization suggestions.

[0009] In some embodiments of the present application, the execution information is the statement text of the target SQL script. After obtaining the execution information of the target SQL script, the method also includes: executing the target statement text extracted from the target SQL script to obtain a test performance indicator of the target SQL script; comparing the test performance indicator with the performance indicator corresponding to the target SQL script in the database performance analysis report; when the test performance indicator is inconsistent with the performance indicator corresponding to the target SQL script in the database performance analysis report, determining an error segment in the target statement text; correcting the error segment in the target statement text to obtain the statement text of the target SQL script.

[0010] In some embodiments of the present application, a rectification report is used to perform performance appraisal on a target object, including: obtaining a target performance indicator of a target SQL script from the rectification report, wherein the target performance indicator includes a second execution duration; determining a score of the target SQL script based on the target performance indicator, wherein the score is used to quantitatively evaluate the severity of an impact of the target SQL script on database performance; and performing performance appraisal on the target object using the score.

[0011] In some embodiments of the present application, a score of a target SQL script is determined based on a target performance indicator, including: obtaining other performance indicators corresponding to the target SQL script from a performance indicator set, wherein the other performance indicators are indicators in the performance indicator set other than the target performance indicator; determining weights corresponding to each performance indicator in the other performance indicators, and the weight of the target performance indicator; performing weighted operations on each performance indicator in the other performance indicators and the target performance indicator to obtain a comprehensive performance indicator; and determining the score of the target SQL script using the comprehensive performance indicator.

[0012] According to another aspect of the embodiment of the present application, a performance appraisal device is also provided, including: an acquisition module for acquiring a database performance analysis report; a determination module for determining a target SQL script from the database performance analysis report; a report generation module for generating a rectification report for the target SQL script, wherein the rectification report includes a target object corresponding to the target SQL script; and an execution module for performing performance appraisal on the target object using the rectification report.

[0013] According to another aspect of the embodiment of the present application, there is also provided an electronic device, including: a memory and a processor, the memory being used to store program instructions; the processor being connected to the memory and being used to execute the method for implementing the above-mentioned performance appraisal.

[0014] According to another aspect of the embodiments of the present application, a non-volatile storage medium is provided, which includes a stored computer program, wherein the device where the non-volatile storage medium is located executes the above-mentioned performance evaluation method by running the computer program.

[0015] According to another aspect of the embodiments of the present application, a computer program product is provided, including computer instructions, which implement the above-mentioned performance evaluation method when executed by a processor.

[0016] In the embodiment of the present application, an automated analysis method is adopted to determine the target SQL script through the database performance analysis report, and a corresponding rectification report is generated using the target SQL script. Performance appraisal is performed by analyzing the rectification report, thereby achieving the purpose of quantitatively evaluating the quality of SQL scripts and associating it with performance appraisal, thereby achieving the technical effect of improving the efficiency of database performance management and objectively and impartially performing automated performance appraisal of the work of the target object, thereby solving the technical problem that the performance appraisal of R&D personnel in related technologies usually relies on manual analysis of R&D scripts, which is cumbersome and has poor accuracy. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0018] Figure 1 It is a hardware structure block diagram of a computer terminal of a performance evaluation method according to an embodiment of the present application;

[0019] Figure 2 is a flow chart of a performance appraisal method according to an embodiment of the present application;

[0020] Figure 3 It is a schematic diagram of a rectification report of a performance appraisal method according to an embodiment of the present application;

[0021] Figure 4 It is a schematic diagram of the overall process of a performance appraisal method according to an embodiment of the present application;

[0022] Figure 5 It is a structural schematic diagram of a performance appraisal device according to an embodiment of the present application. DETAILED DESCRIPTION

[0023] In order to enable those skilled in the art to better understand the solution of the present application, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work should fall within the scope of protection of the present application.

[0024] It should be noted that the terms "first", "second", etc. in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0025] In order to better understand the embodiments of the present application, the technical terms involved in the embodiments of the present application are explained as follows:

[0026] Automatic Workload Repository Report (AWR report): A performance analysis report automatically generated by the Oracle database system to diagnose database performance problems.

[0027] SQL Script: A collection of codes containing one or more SQL statements used to perform database operations such as data query, update, insert or delete.

[0028] Query Execution Plan (QEP): refers to a detailed description of the strategies and steps selected by the database when executing a SQL query.

[0029] Performance Optimization Engine: An automated tool or system that analyzes database performance and generates optimization recommendations.

[0030] pawSQL Audit Engine: A web-based SQL query analysis and optimization tool that can deeply analyze the performance issues of SQL scripts and generate specific optimization suggestions, including syntax, logic improvements, and resource consumption optimization.

[0031] Git Code Repository: A code repository under a version control system that is used to store and manage the source code of software projects, including various code files including SQL scripts.

[0032] The performance appraisal of R&D personnel depends on the quality of their R&D. However, the evaluation of the R&D quality of relevant technical teams is often limited by the degree of manual intervention and lacks the support of automated processes and intelligent analysis, resulting in low efficiency in problem identification and resolution. Manual analysis is not only time-consuming, but may also affect the accurate identification of problems due to differences in subjective judgments, so it is difficult to ensure the fairness and real-time nature of performance appraisal. In order to solve the above technical problems, the embodiments of the present application provide corresponding solutions, which are described in detail below.

[0033] The performance appraisal method embodiment provided in the embodiments of the present application can be executed in a mobile terminal, a computer terminal or a similar computing device. Figure 1 The hardware structure block diagram of a computer terminal for implementing a performance appraisal method is shown in FIG. Figure 1 As shown, the computer terminal 10 may include one or more (102a, 102b, ..., 102n are used to illustrate) processors (the processor may include but is not limited to a processing device such as a microprocessor MCU or a programmable logic device FPGA), a memory 104 for storing data, and a transmission module 106 for communication functions connected via a wired and / or wireless network. In addition, it may also include: a display, a keyboard, a cursor control device, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of the I / O interface), a network interface, and a BUS bus. Those skilled in the art can understand that Figure 1 The structure shown is only for illustration and does not limit the structure of the above electronic device. Figure 1 More or fewer components as shown, or with Figure 1 Different configurations are shown.

[0034] It should be noted that the one or more processors and / or other data processing circuits described above may generally be referred to herein as "data processing circuits". The data processing circuits may be embodied in whole or in part as software, hardware, firmware, or any other combination thereof. In addition, the data processing circuit may be a single independent processing module, or may be incorporated in whole or in part into any of the other components in the computer terminal 10. As described in the embodiments of the present application, the data processing circuit acts as a processor control (e.g., selection of a variable resistor terminal path connected to an interface).

[0035] 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 appraisal method in the embodiment of the present application. The processor executes various functional applications and data processing by running the software programs and modules stored in the memory 104, that is, realizing the above-mentioned performance appraisal method. The memory 104 may include a high-speed random access memory, and may also include a non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, the memory 104 may further include a memory remotely arranged relative to the processor, and these remote memories may be connected to the computer terminal 10 via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0036] The transmission module 106 is used to receive or send data via a network. The specific example of the above network may include a wireless network provided by a communication provider of the computer terminal 10. In one example, the transmission module 106 includes a network adapter (Network Interface Controller, NIC), which can be connected to other network devices through a base station so as to communicate with the Internet. In one example, the transmission module 106 can be a radio frequency (RF) module, which is used to communicate with the Internet wirelessly.

[0037] The display may be, for example, a touch screen liquid crystal display (LCD) that enables a user to interact with a user interface of the computer terminal 10 .

[0038] It should be noted that, in some optional embodiments, the above Figure 1 The computer terminal shown may include hardware elements (including circuits), software elements (including computer code stored on a computer-readable medium), or a combination of hardware elements and software elements. It should be noted that Figure 1 This is merely one example of a particular embodiment and is intended to illustrate the types of components that may be present in the computer terminal described above.

[0039] In the above-mentioned operating environment, an embodiment of the present application provides a method embodiment of performance appraisal. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although the logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0040] Figure 2 is a flow chart of a performance evaluation method according to an embodiment of the present application. Figure 2 As shown, the method comprises the following steps:

[0041] Step S202: Obtain a database performance analysis report.

[0042] In the above step S202, the database performance analysis report is a system-generated document that records in detail the operation status, performance indicators, resource usage and potential performance bottlenecks of the database in a specific time period, and is used to diagnose database performance problems and provide a basis for optimization. The database performance analysis report usually includes but is not limited to query execution time, wait events, lock contention, resource consumption (such as CPU usage, memory usage) and other information. For example, the AWR (Automatic Workload Repository) report in the Oracle database provides performance data of the database instance, including but not limited to SQL execution statistics, session statistics, system statistics, wait events, etc.

[0043] Step S204: determine the target SQL script from the database performance analysis report.

[0044] In the above step S204, the target SQL script refers to the SQL query statement that is identified as having performance problems in the database performance analysis report.

[0045] Since the database performance analysis report contains a variety of quantifiable data fields (such as performance indicators and resource usage), the quantifiable data fields in the database performance analysis report can be used to determine the target SQL script.

[0046] In some embodiments, the execution time of the SQL script can be used to determine the target SQL script from the database performance analysis report. Specifically: obtain the first execution time of at least one SQL script from the database performance analysis report; compare the first execution time with a preset threshold; and determine the SQL script whose first execution time is greater than the preset threshold in at least one SQL script as the target SQL script.

[0047] (1) Obtaining the first execution time of the SQL script: The first execution time refers to the execution time of the SQL script recorded in the database performance analysis report, which can be the average execution time, the maximum execution time, or the cumulative execution time within a preset time period of the query.

[0048] Since the database performance analysis reports generated by different types of databases have certain differences, the acquisition of the first execution duration needs to be dynamically adjusted according to the type of database, specifically: obtaining the database type corresponding to the database performance analysis report; determining the performance indicator mapping rule corresponding to the database type, wherein the performance indicator mapping rule is used to indicate the mapping relationship between the data fields in the database performance analysis report and the performance report; using the performance indicator mapping rule to map the database performance analysis report to obtain the execution time of the SQL script.

[0049] Database types include Oracle, MySQL, PostgreSQL, SQL Server and other databases. Different types of databases differ in performance monitoring, report generation, and data field naming.

[0050] The performance indicator mapping rule is a parsing strategy used to indicate the correspondence between specific data fields and performance indicators in the database performance analysis report. Since different database types may use different data field names or report structures, the mapping rule is the key to ensure that the required performance data is correctly extracted from the report. For example, for Oracle databases, the corresponding performance indicator mapping rule instructs the system to search for SQL_ID-related data fields in the AWR report, such as ELAPSED_TIME (execution time), to extract the execution time of the SQL script; for PostgreSQL databases, the corresponding performance indicator mapping rule points to the "query" (indicating the executed SQL query statement) and "total_time" (indicating the total time spent executing the query statement) fields in the "pg_stat_statements" table (a system table used to track the execution of SQL statements in the database) to obtain the execution time of the SQL script.

[0051] (2) Comparing the first execution time with a preset threshold: The preset threshold is a standard set by the system based on business requirements, performance goals, or historical data, and is used to determine whether the execution time of the SQL script exceeds an acceptable range. For example, the system or operation and maintenance personnel set an execution time threshold (e.g., 3 seconds) that reflects the business requirements for response time and the limitations of system performance based on business scenarios and system resources.

[0052] (3) Determine the target SQL script: In some embodiments, the system administrator sets a preset threshold of 1.5 seconds based on current business needs and database performance goals (the preset threshold takes into account the business's sensitivity to response time and system resource limitations). That is, if the execution time of any SQL script exceeds 1.5 seconds, it is considered to have a performance problem and needs to be optimized.

[0053] Step S206: Generate a rectification report for the target SQL script, wherein the rectification report includes a target object corresponding to the target SQL script.

[0054] In the above step S206, the rectification report is a document or data set used to identify problems, provide optimization suggestions, and guide the responsible person to take actions. It is expressed as a structured output and may include the performance analysis results of the SQL script, optimization suggestions, and associated information of the target object. The target object corresponding to the target SQL script includes the identity information (such as ID) or account of the developer, and the relationship between the target SQL script and the target object is expressed as the relationship between the developed and the developer.

[0055] In some embodiments, the rectification report may include the following sections (or data fields):

[0056] (1) SQL script performance analysis: This describes the script execution time, resource consumption (such as CPU usage and disk I / O times), query efficiency (such as index usage and JOIN operation efficiency), and other information.

[0057] (2) Optimization suggestions: These are specific improvement measures provided based on performance analysis, such as adjusting SQL statement logic, adding or optimizing indexes, modifying JOIN types, or optimizing database configuration parameters, to improve the execution efficiency of SQL scripts and reduce resource consumption.

[0058] (3) Target object information: This refers to the information of the developer or team responsible for the target SQL script, including the name, work number, contact information, and role in the team of the person responsible, so as to facilitate direct communication with the person responsible and ensure the effective implementation of the optimization suggestions.

[0059] In some embodiments, to generate a rectification report for a target SQL script, the following process may be performed: obtaining execution information of the target SQL script and a target object corresponding to the target SQL script, wherein the execution information includes statement text of the target SQL script; analyzing the execution information to obtain analysis results; generating optimization suggestions for the target SQL script corresponding to the analysis results; and constructing a rectification report for the target SQL script using the execution information, the target object, and the optimization suggestions.

[0060] (1) Obtain the execution information of the target SQL script and the corresponding target object: Execution information refers to detailed data related to the execution of the target SQL script, including but not limited to statement text, execution plan, resource consumption, execution frequency, average and maximum execution time, etc., which provides basic data for in-depth performance analysis. For example, for a target SQL script, execution information may include:

[0061] 1) Statement text: SELECT * FROM orders WHERE order_date>'2023-01-01'AND status='SHIPPED'.

[0062] 2) Execution plan: shows that a full table scan is used instead of an index scan, and there are a large number of disk I / O operations.

[0063] 3) Resource consumption: records the CPU usage, disk read and write times, and memory usage during script execution.

[0064] 4) Execution frequency: The script was called and executed 1,200 times within one week.

[0065] 5) Average and maximum execution time: The average execution time is 2.5 seconds and the maximum execution time is 4.2 seconds.

[0066] Since each SQL script has a unique identifier, which is used to distinguish different SQL scripts, in some embodiments, the code submission record can be queried from the code repository through the identifier of the target SQL script, and the target object can be determined from the code submission record.

[0067] (2) Analyze the execution information, obtain analysis results and generate optimization suggestions: Optimization suggestions are specific guidance measures generated by the system based on the analysis results, which are used to improve the execution efficiency and resource usage of SQL scripts, including but not limited to code modification, index adjustment, and database configuration parameter optimization.

[0068] When the execution information is a statement text, the query structure, index usage, data type, function call, etc. of the statement text can be analyzed. For example, the statement text of the target SQL script is "SELECT * FROM orders WHERE order_date>'2023-01-01' AND status='SHIPPED'"; the analysis result can be: using "SELECT *" to retrieve all columns from the "orders" table instead of selecting only the required columns leads to unnecessary data transmission and processing, and increases network and CPU resource consumption; the optimization suggestion can be: changing "SELECT *" to a specific column name to reduce the amount of data transmission, thereby reducing network bandwidth consumption and CPU processing time.

[0069] When the execution information is an execution plan, the data access method, JOIN operation, sorting and grouping, scan range and filter conditions of the execution plan can be analyzed. For example, for the above SQL script, the execution plan shows that no index is used on the "order_date" and "status" fields; the analysis result can be: the target SQL script performs a full table scan instead of an index scan when executed; the optimization suggestion can be: create or optimize indexes, especially composite indexes for the "order_date" and "status" fields, to avoid full table scans, reduce execution time and the number of I / O operations, and improve query efficiency.

[0070] When the execution information is resource consumption, the CPU time consumption, disk I / O operations, memory usage, etc. can be analyzed. For example, the target SQL script consumes a lot of CPU resources during execution. The analysis result can be: the SQL script contains complex calculations or subqueries, resulting in a CPU usage rate of 80%; the optimization suggestions can be: simplify the calculation logic, avoid using complex recursive queries or subqueries, and consider using simpler JOIN operations or alternative calculation methods to reduce CPU resource consumption and improve query response speed.

[0071] (3) Constructing a rectification report of the target SQL script using the execution information, target object, and optimization suggestions: The structure of the rectification report can be pre-designed, that is, the corresponding content is extracted from the execution information, target object, and optimization suggestions according to the fields contained in the pre-designed rectification report and filled into the rectification report. Alternatively, the entire content of the execution information, target object, and optimization suggestions can be directly integrated (such as splicing) or a part of the content can be selected for integration to generate a rectification report. In some embodiments, the rectification report can include data fields such as "git user name", "submission time", "SQL script", "time consumed (seconds)", "points deducted", "rectification suggestions", and "project path where the code is located". Figure 3 As shown, in the rectification report, one row represents a target SQL script, and different columns represent different data fields, where "git user name" is the target object corresponding to the target SQL script, "submission time" is the time when the target object corresponding to the target SQL script submits the script, "SQL script" is the statement text corresponding to the target SQL script, "time consumed (seconds)" is the execution time of the target SQL script, "deduction" is the reference basis for performance appraisal, "rectification suggestions" are to analyze the execution information of the target SQL script, and the optimization suggestions generated after obtaining the analysis results, and "code project path" is the path of the project where the target SQL script is located.

[0072] In order to ensure the accuracy of the rectification report and avoid errors in the extraction and analysis process of the SQL script, the statement text of the extracted target SQL script can also be detected for errors. Specifically: execute the target statement text extracted from the target SQL script to obtain the test performance indicators of the target SQL script; compare the test performance indicators with the performance indicators corresponding to the target SQL script in the database performance analysis report; when the test performance indicators are inconsistent with the performance indicators corresponding to the target SQL script in the database performance analysis report, determine the error fragment in the target statement text; correct the error fragment in the target statement text to obtain the statement text of the target SQL script.

[0073] (1) Execution of target statement text and acquisition of test performance indicators: The target statement text extracted from the target SQL script is executed in the test environment to obtain its test performance indicators. Test performance indicators include but are not limited to execution time, resource consumption (such as CPU and I / O), and the amount of data returned by the query.

[0074] (2) Performance indicator comparison: In order to verify whether the extracted and executed target statement text is consistent with the behavior of the original SQL script, the test performance indicators obtained by the test environment can be compared with the performance indicators recorded in the database performance analysis report. The performance indicator comparison may be based on indicators such as average execution time, maximum execution time, resource consumption, etc., and mathematical operations (such as difference, percentage difference) are used to determine whether the two are consistent.

[0075] (3) Identify the erroneous fragment: If there is a difference between the test performance indicators and the indicators in the database performance analysis report, the erroneous fragment that causes the difference can be located through syntax parsing, semantic analysis, and execution plan comparison. For example, the SQL statements executed by the test can be compared with the original SQL script at the syntax level to check whether there are syntax differences or additional statements, or the execution plans can be compared to find differences in data access or JOIN operations.

[0076] (4) Correcting the error segment: After determining the error segment, the system or database administrator can take measures to correct the error, including syntax error repair, logic error adjustment, code omission supplement, etc. In some embodiments, this can be done automatically through a preset rule library or machine learning algorithm, or manually by a developer or database administrator.

[0077] (5) Statement text update: After correcting the error fragment, an updated statement text of the target SQL script can be obtained. In some embodiments, the updated statement text can be executed again to verify whether the performance indicators are consistent with the data in the database performance analysis report. The corrected statement text is used to construct a rectification report to ensure that the rectification report is based on accurate SQL script information.

[0078] Step S208: Use the rectification report to conduct performance appraisal on the target object.

[0079] In some embodiments, the target object can be performance-appraised through the following process: obtaining a target performance indicator of the target SQL script from the rectification report, wherein the target performance indicator includes a second execution time; determining a score of the target SQL script based on the target performance indicator, wherein the score is used to quantitatively evaluate the severity of the impact of the target SQL script on database performance; and using the score to performance-appraise the target object.

[0080] (1) Obtaining target performance indicators: Target performance indicators refer to the quantitative indicators used to evaluate the performance of the target SQL script in the rectification report, including execution time (i.e., second execution time), resource consumption (such as CPU usage, disk I / O operations), query efficiency (such as index usage), data access mode, etc.

[0081] In some embodiments, the performance data of the target SQL script in the rectification report can be read by a programming language (such as Python, Java, etc.) to extract performance information related to the target SQL script, such as execution time, resource consumption, etc. If the rectification report is stored in a structured data format (such as JSON, XML), a parsing library can be used to read the required fields; if the rectification report is a text report, the target data can be extracted by regular expressions or text processing algorithms.

[0082] (2) Determine the score of the target SQL script based on the target performance index: The score of the target SQL script can be determined by a preset scoring rule or algorithm. The scoring rule can be set according to the difference between the execution time and the preset threshold. For example, if the preset threshold is 2 seconds, 1 point will be deducted if the execution time is between 2 and 3 seconds, 2 points will be deducted if the execution time is between 3 and 4 seconds, and so on. This scoring mechanism can intuitively reflect the severity of the performance problems of the target SQL script and facilitate subsequent performance appraisal. The scoring process can be done by writing a scoring algorithm, inputting execution time data, and outputting a scoring result. For example, the average second execution time of the target SQL script W is 4.5 seconds. According to the scoring rule, the system calculates a scoring result of 3 points.

[0083] In addition to using the execution time to determine the score of the target SQL script, a comprehensive score can also be calculated in combination with multiple performance indicators to improve the accuracy of performance appraisal. Specifically: other performance indicators corresponding to the target SQL script are obtained from the performance indicator set, where the other performance indicators are indicators in the performance indicator set other than the target performance indicator; the weights corresponding to each performance indicator in the other performance indicators and the weight of the target performance indicator are determined; each performance indicator in the other performance indicators and the target performance indicator are weighted to obtain a comprehensive performance indicator; and the comprehensive performance indicator is used to determine the score of the target SQL script.

[0084] In the above process, the performance indicator set refers to a set of quantitative indicators that reflect the performance of the target SQL script, including but not limited to execution time, resource consumption (such as CPU usage, disk I / O times), query efficiency, data access mode, etc., which are used to comprehensively evaluate the performance of the target SQL script. For example, the performance indicator set may include the following indicators: Average execution time: 4.5 seconds. Maximum execution time: 8 seconds. CPU usage: 35%. Disk I / O times: 200 times.

[0085] In some embodiments, a database or file containing all performance indicators (such as a database table performance analysis report) can be read or accessed, and a unique identifier (such as SQL_ID) of a target SQL script can be used to query and obtain all performance indicators related to the script, including but not limited to execution time, resource consumption, query efficiency, etc. For example, resource consumption data of the target SQL script, such as CPU usage, disk I / O times, etc., can be obtained from a database table performance analysis report through object-relational mapping (ORM) technology or SQL query.

[0086] Weight refers to the relative importance value assigned to each performance indicator when calculating the comprehensive performance indicator. It is used to reflect the different degrees of impact of different performance indicators on the overall performance and is a key parameter for quantitative evaluation. For example, if the average execution time has the greatest impact on the user experience, it can be assigned a weight of 0.4; CPU usage and disk I / O times have a greater impact on resource consumption, and can be assigned weights of 0.3 and 0.2 respectively; query efficiency (such as index usage) may be assigned a smaller weight, such as 0.1.

[0087] In some embodiments, the weight of each performance indicator may be preset based on empirical rules, for example, by setting the weight through a configuration file or a database table.

[0088] The comprehensive performance index refers to a quantitative value obtained through weighted calculation that represents the comprehensive performance of the target SQL script. Since it takes into account the impact of multiple dimensions, it can more comprehensively evaluate the severity of the script's performance problems.

[0089] In some embodiments, after obtaining all performance indicators and corresponding weights, a programming language (such as Python, Java, etc.) can be used to implement a weighted operation algorithm to calculate the comprehensive performance indicator of each SQL script. For example, the operation logic can be to multiply each performance indicator by its weight, and then add all the products to obtain a comprehensive performance indicator representing the weighted average of all performance indicators.

[0090] The process of determining the score of the target SQL script using the comprehensive performance index may be: presetting a scoring rule to determine the corresponding relationship between the comprehensive performance index and the score, matching the comprehensive performance index with the scoring rule, and calculating the score for each target SQL script. In some embodiments, the preset scoring rule may be that if the comprehensive performance index is less than 40, it is excellent, and the score is increased by 2 points; if it is 40 to 60, it is good, and the score remains unchanged; if it is 60 to 80, it needs attention, and the score is reduced by 3 points; if it is more than 80, it is serious, and the score is reduced by 5 points.

[0091] (3) Use the score to evaluate the performance of the target object: Integrate the score with the performance evaluation system of the target object (such as the person in charge of R&D) and adjust the performance evaluation of the target object through the score. In some embodiments, an interface or function can be designed to receive the score result and the target object information, and adjust the score according to the preset performance evaluation rules. For example, if the target SQL script W that the target object Zhang San is responsible for scores 3 points, it means that there is a performance bottleneck in his SQL script, and the corresponding score can be deducted from Zhang San's performance evaluation.

[0092] Through the above steps S202 to S208, an automated analysis method is adopted to determine the target SQL script through the database performance analysis report, and a corresponding rectification report is generated using the target SQL script. Performance appraisal is performed by analyzing the rectification report, thereby achieving the purpose of quantitatively evaluating the quality of SQL scripts and associating it with performance appraisal, thereby achieving the technical effect of improving the efficiency of database performance management and objectively and impartially performing automated performance appraisal on the work of the target object, thereby solving the technical problem that the performance appraisal of R&D personnel in related technologies usually relies on manual analysis of R&D scripts, which is cumbersome and has poor accuracy.

[0093] Figure 4 is a schematic diagram of the overall process of performance appraisal according to an embodiment of the present application, such as Figure 4 As shown:

[0094] (1) Step S401, input platform access address: the user selects a performance optimization engine according to different database types. For example, for a PostgreSQL database, the platform access address is input through "pg_profile performance optimization engine" so that the performance optimization engine can connect to the database and collect performance data;

[0095] (2) Step S402, audit analysis of the platform: the performance optimization engine begins to perform in-depth audit analysis on the database platform, including performance indicators such as query execution time, resource consumption, and execution plan, to identify any possible performance bottlenecks or inefficient SQL scripts;

[0096] (3) Step S403, outputting an AWR analysis report: The performance optimization engine generates a detailed AWR analysis report through the platform based on the audit analysis results. The “AWR analysis report” is a database performance analysis report.

[0097] (4) Step S404, review and optimize key SQL scripts in the report: the performance appraisal system selects key SQL scripts whose execution time exceeds a preset threshold (e.g., 3 seconds) from the AWR analysis report, and prepares to conduct in-depth review and generate optimization suggestions for them;

[0098] (5) Step S405, outputting optimization plans: The performance appraisal system uses the pawSQL audit engine to conduct in-depth analysis on the above-screened key SQL scripts, identify specific performance issues, and generate optimization plans for each script. The optimization plans may include suggestions such as modifying query structures, creating or adjusting indexes, and optimizing JOIN operations;

[0099] (6) Step S406, query the Git submitter based on the key SQL scripts in the report: the performance appraisal system further matches the SQL scripts that take too long to execute with the submission records in the Git code repository, and uses the script's unique identifier (such as SQL_ID or hash value) to search in the Git code repository to determine the code submission person who is responsible for writing or most recently modifying the script;

[0100] (7) Step S407, outputting the person responsible for code submission: After matching the relevant submission records, outputting the information of the person responsible for code submission, including the developer's name, work number, contact information, etc., for subsequent communication and responsibility confirmation;

[0101] (8) Step S408, output the performance deduction and rectification plan of the responsible person: The performance appraisal system determines the performance deduction of each responsible person based on the optimization plan generated by the pawSQL audit engine and the information of the responsible person for code submission. The deduction is based on the comprehensive score of the second execution time of the target SQL script and other performance indicators. The performance appraisal system will integrate the optimization plan, responsible person information and performance deduction, and generate a detailed rectification report to feedback to the user. The rectification report can not only include optimization suggestions, but also the responsible person and his performance impact, providing the responsible person with a clear action guide to improve the SQL script performance, while linking his optimization behavior with his personal performance, promoting continuous database performance optimization and code quality improvement.

[0102] Figure 5 is a structural diagram of a performance evaluation device according to an embodiment of the present application, such as Figure 5 As shown, the device comprises:

[0103] Acquisition module, used to obtain database performance analysis report;

[0104] A determination module is used to determine a target SQL script from a database performance analysis report;

[0105] A report generation module, used to generate a rectification report of a target SQL script, wherein the rectification report includes a target object corresponding to the target SQL script;

[0106] The execution module is used to conduct performance appraisal on the target object using the rectification report.

[0107] In the above-mentioned performance appraisal device, the determination module is also used to obtain the first execution time of at least one SQL script from the database performance analysis report; compare the first execution time with a preset threshold; and determine the SQL script whose first execution time is greater than the preset threshold in at least one SQL script as the target SQL script.

[0108] In the above-mentioned performance appraisal device, the determination module is also used to obtain the database type corresponding to the database performance analysis report; determine the performance indicator mapping rules corresponding to the database type, wherein the performance indicator mapping rules are used to indicate the mapping relationship between the data fields in the database performance analysis report and the performance report; use the performance indicator mapping rules to map the database performance analysis report to obtain the execution time of the SQL script.

[0109] In the above-mentioned performance appraisal device, the report generation module is also used to obtain the execution information of the target SQL script and the target object corresponding to the target SQL script, wherein the execution information includes the statement text of the target SQL script; analyze the execution information to obtain the analysis results; generate optimization suggestions for the target SQL script corresponding to the analysis results; and use the execution information, target object and optimization suggestions to construct a rectification report for the target SQL script.

[0110] In the above-mentioned performance appraisal device, the report generation module is also used to execute the target statement text extracted from the target SQL script to obtain the test performance indicators of the target SQL script; compare the test performance indicators with the performance indicators corresponding to the target SQL script in the database performance analysis report; when the test performance indicators are inconsistent with the performance indicators corresponding to the target SQL script in the database performance analysis report, determine the error fragment in the target statement text; correct the error fragment in the target statement text to obtain the statement text of the target SQL script.

[0111] In the above-mentioned performance appraisal device, the execution module is also used to obtain the target performance indicators of the target SQL script from the rectification report, wherein the target performance indicators include the second execution time; determine the score of the target SQL script based on the target performance indicators, wherein the score is used to quantitatively evaluate the severity of the impact of the target SQL script on database performance; and use the score to perform performance appraisal on the target object.

[0112] In the above-mentioned performance appraisal device, the execution module is also used to obtain other performance indicators corresponding to the target SQL script from the performance indicator set, wherein the other performance indicators are indicators in the performance indicator set other than the target performance indicator; determine the weights corresponding to each performance indicator in the other performance indicators, as well as the weight of the target performance indicator; perform weighted operations on each performance indicator in the other performance indicators and the target performance indicator to obtain a comprehensive performance indicator; and use the comprehensive performance indicator to determine the score of the target SQL script.

[0113] It should be noted that Figure 5 The performance appraisal device shown is used to perform Figure 2 The performance appraisal method shown is therefore Figure 2 The relevant explanations in the performance appraisal method in the Figure 5 The performance appraisal device shown will not be described in detail here.

[0114] An embodiment of the present application also provides an electronic device, which includes a memory and a processor, wherein the memory is used to store program instructions; the processor is connected to the memory and is used to execute the steps of the performance appraisal method in each embodiment of the present application.

[0115] For example, the processor performs the following functions by executing program instructions stored in the memory:

[0116] Obtain a database performance analysis report; determine a target SQL script from the database performance analysis report; generate a rectification report for the target SQL script, wherein the rectification report includes a target object corresponding to the target SQL script; and use the rectification report to perform performance appraisal on the target object.

[0117] An embodiment of the present application further provides a non-volatile storage medium, which includes a stored computer program, wherein the device where the non-volatile storage medium is located executes the steps of the performance appraisal method in each embodiment of the present application by running the computer program.

[0118] An embodiment of the present application also provides a computer program product, including computer instructions, which, when executed by a processor, implement the steps of the performance appraisal method in each embodiment of the present application.

[0119] The embodiments of the present application also provide a computer program, which, when executed by a processor, implements the steps of the performance appraisal method in each embodiment of the present application.

[0120] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0121] In the above embodiments of the present application, the description of each embodiment has its own emphasis. For parts that are not described in detail in a certain embodiment, please refer to the relevant description of other embodiments.

[0122] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. Among them, the device embodiments described above are only schematic. For example, the division of the units can be a logical function division. There may be other division methods in actual implementation. For example, multiple units or components can be combined or integrated into another system, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of units or modules, which can be electrical or other forms.

[0123] The units described as separate components may or may not be physically separated, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed on multiple units. Some or all of the units may be selected according to actual needs to achieve the purpose of the present embodiment.

[0124] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0125] If the integrated unit is implemented in the form of 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 the present application is essentially 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, and the computer software product is stored in a storage medium, including a number of instructions to enable a computer device (which can be a personal computer, server or network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, read-only memory (ROM, Read-Only Memory), random access memory (RAM, RandomAccess Memory), mobile hard disk, disk or CD-ROM and other media that can store program codes.

[0126] The above is only a preferred implementation of the present application. It should be pointed out that for ordinary technicians in this technical field, several improvements and modifications can be made without departing from the principles of the present application. These improvements and modifications should also be regarded as the scope of protection of the present application.

Claims

1. A performance appraisal method, characterized in that: include: Get database performance analysis report; Determine a target SQL script from the database performance analysis report; Generate a rectification report of the target SQL script, wherein the rectification report includes a target object corresponding to the target SQL script; The rectification report is used to conduct performance appraisal on the target object.

2. The method according to claim 1, characterized in that Determine the target SQL script from the database performance analysis report, including: Obtaining a first execution duration of at least one SQL script from the database performance analysis report; comparing the first execution time with a preset threshold; A SQL script whose first execution time is greater than the preset threshold in the at least one SQL script is determined as the target SQL script.

3. The method according to claim 2, characterized in that Obtaining the execution time of at least one SQL script from the database performance analysis report includes: Obtaining the database type corresponding to the database performance analysis report; Determine a performance indicator mapping rule corresponding to the database type, wherein the performance indicator mapping rule is used to indicate a mapping relationship between a data field in the database performance analysis report and a performance report; The database performance analysis report is mapped using the performance indicator mapping rule to obtain the execution time of the SQL script.

4. The method according to claim 1, characterized in that: Generate a rectification report for the target SQL script, including: Acquire execution information of the target SQL script and a target object corresponding to the target SQL script, wherein the execution information includes a statement text of the target SQL script; Analyze the execution information to obtain analysis results; Generate optimization suggestions for the target SQL script corresponding to the analysis results; A rectification report of the target SQL script is constructed using the execution information, the target object, and the optimization suggestion.

5. The method according to claim 4, characterized in that The execution information is the statement text of the target SQL script. After acquiring the execution information of the target SQL script, the method further includes: Execute the target statement text extracted from the target SQL script to obtain the test performance index of the target SQL script; Compare the test performance indicator with the performance indicator corresponding to the target SQL script in the database performance analysis report; In the case where the test performance indicator is inconsistent with the performance indicator corresponding to the target SQL script in the database performance analysis report, determining an error segment in the target statement text; Correct the error fragments in the target statement text to obtain the statement text of the target SQL script.

6. The method according to claim 1, characterized in that Using the rectification report to conduct performance appraisal on the target object, including: Acquire a target performance indicator of the target SQL script from the rectification report, wherein the target performance indicator includes a second execution duration; Determining a score of the target SQL script according to the target performance indicator, wherein the score is used to quantitatively evaluate the severity of the impact of the target SQL script on database performance; The score is used to conduct a performance appraisal on the target object.

7. The method according to claim 6, characterized in that Determining a score of the target SQL script according to the target performance indicator includes: Acquire other performance indicators corresponding to the target SQL script from the performance indicator set, wherein the other performance indicators are indicators in the performance indicator set except the target performance indicator; Determine the weights corresponding to the respective performance indicators in the other performance indicators, and the weight of the target performance indicator; Performing weighted calculation on each performance indicator in the other performance indicators and the target performance indicator to obtain a comprehensive performance indicator; The score of the target SQL script is determined using the comprehensive performance indicator.

8. A performance appraisal device, characterized in that: include: Acquisition module, used to obtain database performance analysis report; A determination module, used to determine a target SQL script from the database performance analysis report; A report generation module, used to generate a rectification report of the target SQL script, wherein the rectification report includes a target object corresponding to the target SQL script; An execution module is used to use the rectification report to conduct performance appraisal on the target object.

9. An electronic device, characterized in that: include: A memory and a processor, wherein the memory is used to store program instructions; the processor is connected to the memory and is used to execute the performance appraisal method described in any one of claims 1 to 7.

10. A non-volatile storage medium, characterized in that: The non-volatile storage medium includes a stored computer program, wherein the device where the non-volatile storage medium is located executes the performance appraisal method described in any one of claims 1 to 7 by running the computer program.

11. A computer program product comprising computer instructions, characterized in that: When the computer instructions are executed by a processor, the performance evaluation method described in any one of claims 1 to 7 is implemented.

Images