Multi-database batch performance comparison method realized based on EXCEL

Abstract

The invention discloses a multi-database batch performance comparison method and system based on EXCEL, and the method comprises the steps: maintaining and generating metadata used in a storage process, including system platform information and database information; according to the maintained metadata information and the system and database information, a report of comparison of task execution performance of different databases on that day is generated; and storing statistical information in the process of generating the report by the tool. An EXCEL generation tool is used, a task execution condition statistical template exported by each database is maintained according to a specified template, and a comparison report is automatically generated, so that the daily workload is reduced, and the efficiency and the accuracy are improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the field of database, and in particular to a multi-database batch performance comparison method based on EXCEL. BACKGROUND

[0002] In the current data development process of various industries, in order to respond to the needs of database localization, each bank and institution is replacing the existing database with a domestic product. In the replacement process, it is necessary to compare the execution of the same task in different databases. However, in the existing environment, most of the comparisons are made by operation and maintenance personnel, which is time-consuming and repetitive, and wastes a lot of manpower and resources. Moreover, many companies use daily reports to compare, and manual comparison cannot meet such needs.

[0003] The EXCEL multi-database batch performance comparison tool can generate a multi-database task batch performance comparison report uniformly and quickly according to a specified template. Based on this technical background, a process-based comparison report can be generated, greatly reducing the labor cost of operation and maintenance, and significantly improving work efficiency.

[0004] Currently, when generating a comparison daily report, the operation and maintenance personnel need to export the execution time of the batch tasks of different databases into an excel file every day, and then manually copy and paste each row to compare the execution of all tasks under the current system, and mark the database of the task with good execution performance. Finally, the number of tasks with execution performance higher than that of other databases under each database is counted and a daily report is generated. This method cannot efficiently complete time-sensitive tasks, and manual comparison is prone to report quality accuracy problems and rework, consuming a lot of time and effort.

[0005] Currently, when generating a comparison daily report, the operation and maintenance personnel need to export the execution time of the batch tasks of different databases into an excel file every day, and then manually copy and paste each row to compare the execution of all tasks under the current system, and mark the database of the task with good execution performance. Finally, the number of tasks with execution performance higher than that of other databases under each database is counted and a daily report is generated. This method cannot efficiently complete time-sensitive tasks, and manual comparison is prone to report quality accuracy problems and rework, consuming a lot of time and effort.

[0006] 1) Manual comparison is inefficient and cannot complete tasks in a timely manner, which may cause task delays.

[0007] 2) Manual comparison cannot guarantee the quality of the delivered product, and may cause accuracy problems or even rework. SUMMARY

[0008] In view of the above problems, the present application is proposed to provide a multi-database batch performance comparison method based on EXCEL to overcome the above problems or at least partially solve the above problems.

[0009] According to one aspect of the present application, a multi-database batch performance comparison method based on EXCEL is provided, which comprises:

[0010] Metadata used by the maintenance generation stored procedure, including system platform information, database information;

[0011] According to the maintained metadata information, according to the system and database information, a report of the performance comparison of different database tasks executed on the same day is generated;

[0012] The statistical information in the report generation process is stored.

[0013] Optionally, the statistical information specifically includes: start time, end time and success or not.

[0014] Optionally, the metadata used by the maintenance generation stored procedure specifically includes:

[0015] The system name, the execution situation file name of each database export, and the report generation name involved in the comparison report to be generated are confirmed and maintained in the EXCEL configuration page to obtain an information configuration table.

[0016] Optionally, the table header information in the document includes: system name, database execution situation file name, final report name and generation time.

[0017] Optionally, the report of the performance comparison of different database tasks executed on the same day specifically includes:

[0018] The information configuration table content in the EXCEL configuration page is read, and each row of records in the summary file of the batch task execution situation of each database on the same day is traversed row by row;

[0019] The task name and execution time information are extracted and temporarily stored in the tool comparison page to sort the names in dictionary order, and then the execution time is compared row by row, and the final daily report is generated.

[0020] Optionally, the statistical information in the report generation process specifically includes:

[0021] During the whole generation process, the execution records of each step are stored in the log storage for historical problem tracing and version information retention.

[0022] The present application also provides a multi-database batch performance comparison system based on EXCEL, which applies the multi-database batch performance comparison method based on EXCEL described above, and the comparison system comprises:

[0023] An EXCEL tool metadata maintenance module is used to maintain metadata used by the generated stored procedure, including system platform information and database information.

[0024] A comparison daily report generation function module is used to generate a report on the performance comparison of different database tasks performed on the same day according to the maintained metadata information and system and database information.

[0025] A log storage module is used to store statistical information in the process of report generation by the tool.

[0026] The application provides a multi-database batch performance comparison method and system based on EXCEL, which comprises the following steps: maintaining metadata used by a generated stored procedure, including system platform information and database information; generating a report on the performance comparison of different database tasks performed on the same day according to the maintained metadata information and system and database information; and storing statistical information in the process of report generation by the tool. The EXCEL generation tool is used to maintain a task execution condition statistical template exported by each database according to a specified template, and to automatically generate a comparison report, thereby reducing the daily workload, improving the efficiency and accuracy.

[0027] The above description is only a summary of the technical scheme of the application. In order to more clearly understand the technical means of the application, the application can be implemented according to the content of the specification, and in order to make the above and other purposes, features and advantages of the application more obvious and easy to understand, the following will specifically describe the embodiments of the application. BRIEF DESCRIPTION OF DRAWINGS

[0028] In order to more clearly illustrate the technical scheme of the embodiments of the application, the following will briefly introduce the drawings needed in the embodiment description. Obviously, the drawings in the following description are only some embodiments of the application, and those skilled in the art can obtain other drawings according to these drawings without creative labor.

[0029] Figure 1 A flowchart of a multi-database batch performance comparison method based on EXCEL provided by the embodiments of the application;

[0030] Figure 2 A multi-database batch performance comparison system based on EXCEL provided by the embodiments of the application. DETAILED DESCRIPTION

[0031] Exemplary embodiments of the present disclosure will be described below in greater detail with reference to the accompanying drawings. While exemplary embodiments of the present disclosure are shown in the drawings, it is to be understood that the present disclosure can be embodied in various forms without being limited by the embodiments set forth herein. Rather, these embodiments are provided so that the present disclosure will be thoroughly and completely understood, and will fully convey the scope of the present disclosure to those skilled in the art.

[0032] The terms "include" and "have" and any variations thereof in the specification, embodiments of the present disclosure and claims, and the accompanying drawings, are intended to cover the non-exclusive inclusion, for example, the inclusion of a series of steps or units.

[0033] The technical solutions of the present application will be described in further detail below in combination with the drawings and embodiments.

[0034] As Figure 1 shown, a method for comparing the performance of multi-database batch running based on EXCEL, comprising:

[0035] Confirm the system name involved in the generation of the comparison report, the execution file name of each database export, the name of the generated report, and maintain them in the EXCEL configuration page. The table header information in the information configuration table in the document includes: system name, database execution file name, final report name, generation time. The specific steps are: according to the actual demand, fill in XXX system or XXX platform in the system name, and fill in the summary file name of each database daily batch task execution situation as the database execution file name.

[0036] For example, ORACLE_YYYYMMDD.csv, CIRRODATA_YYYYMMDD.xlsx, etc., fill in ORACLE and CIRRODATA database performance comparison daily report_ YYYYMMDD.xlsx as the final report name, and fill in the generation time according to the format of YYYYMMDD;

[0037] The comparison daily report function module reads the content of the information configuration table in the EXCEL configuration page, iterates through each row of records in the summary file of each database daily batch task execution situation, extracts the task name and execution time information, and temporarily stores them in the tool comparison page to sort the names in lexicographical order. Then compare the execution time row by row, for example, the execution time of task 1 of database A is less than that of task 1 of database B, then mark the execution performance state of task 1 as A database is better, otherwise B database is better. When all the task statistics are completed, all the data are summarized and counted, and the information such as the number of tasks with A database execution performance as optimal is XX, and the number of tasks with B database execution performance as optimal is XX, etc. is output, and the final daily report is generated.

[0038] During the whole generation process, the execution record of each step is stored in the log storage module for historical problem tracing and version information retention.

[0039] As shown in the figure, a multi-database batch performance comparison system based on EXCEL includes an EXCEL tool metadata maintenance module, a comparison report generation function module, and a log storage module. Figure 2

[0040] The metadata maintenance module is used to maintain the metadata used by the generated stored procedures, including but not limited to system platform information, database information, etc.

[0041] The comparison report generation function module is used to generate a report comparing the performance of different database tasks according to the maintained metadata information and system and database information.

[0042] The log storage module is used to store statistical information during the report generation process, such as start time, end time, and success or failure.

[0043] Beneficial effects: The present application helps users quickly generate multi-database performance comparison reports, saving development time and reducing repetitive steps in the manual comparison process. The comparison daily report has a consistent format and framework, making it easier to read and understand, which helps improve collaboration efficiency and reduce the accuracy and timeliness of comparison daily reports due to human error. At the same time, the function is easy to modify and extend, reducing the difficulty of maintenance.

[0044] The above detailed description further explains the purpose, technical solutions, and beneficial effects of the present application. It should be understood that the above is only a specific embodiment of the present application and does not limit the scope of protection. Any modifications, equivalent replacements, improvements, etc. within the spirit and principles of the present application should be included in the scope of protection of the present application.​

Claims

1. A multi-database batch performance comparison method based on EXCEL, characterized in that: The comparison method includes: Maintain and generate metadata used in stored procedures, including system platform information and database information; Generate a report comparing the performance of different database tasks on the day based on the maintained metadata, system, and database information. Stores statistical information generated by the tool during report generation.

2. The multi-database batch performance comparison method based on EXCEL according to claim 1, characterized in that: The statistical information specifically includes: start time, end time and whether it is successful.

3. The multi-database batch performance comparison method based on EXCEL according to claim 1, characterized in that: The metadata used in the maintenance and generation storage process specifically includes: Confirm the system names involved in generating the comparison report, the names of the execution status files exported from each database, and the name of the generated report, and maintain them in the EXCEL configuration page to obtain the information configuration table.

4. The multi-database batch performance comparison method based on EXCEL according to claim 3 is characterized in that: The header information of the information configuration table in the document includes: system name, database execution status file name, final report name and generation time.

5. The multi-database batch performance comparison method based on EXCEL according to claim 1, characterized in that: The report generating the comparison of the execution performance of different database tasks on the day specifically includes: Read the information configuration table content in the EXCEL configuration page, and traverse each row of records in the summary file of the batch task execution status of each database on that day; Extract the task name and execution time information, and temporarily save them to the tool comparison page to sort the names in lexicographical order. Then compare the execution time line by line and generate the final daily report.

6. The multi-database batch performance comparison method based on EXCEL according to claim 1, characterized in that: The statistical information generated by the storage tool during the report generation process specifically includes: During the entire generation process, the execution records of each step are stored in the log storage to trace historical problems and retain version information.

7. A multi-database batch performance comparison system based on EXCEL, applying the multi-database batch performance comparison method based on EXCEL according to any one of claims 1 to 6, characterized in that: The comparison system comprises: The EXCEL tool metadata maintenance module is used to maintain the metadata used in generating stored procedures, including system platform information and database information; Generate daily comparison report function module, which is used to generate a report comparing the execution performance of different database tasks on the same day according to the maintained metadata information, system and database information; The log storage module is used to store statistical information during the tool's report generation process.

Images